MYSQL 加锁 S 锁 X 锁 死锁 事务

由 InnoDB 预设是 Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行 Row lock (只锁住被选取的资料例) ，否则 MySQL 将会执行 Table Lock (将整个资料表单给锁住)。

举个例子:

假设有个表单 products ，裡面有 id 跟 name 二个栏位，id 是主键。

例1: (明确指定主键，并且有此笔资料，row lock)

    SELECT * FROM products WHERE id='3' FOR UPDATE;

例2: (明确指定主键，若查无此笔资料，无 lock)

    SELECT * FROM products WHERE id='-1' FOR UPDATE;

例2: (无主键，table lock)

    SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例3: (主键不明确，table lock)

    SELECT * FROM products WHERE id<>'3' FOR UPDATE;  

例4: (主键不明确，table lock)

    SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1:
FOR UPDATE（X lock 不能读写) 或 lock in share mode（S lock 可读） 仅适用于 InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2:
要测试锁定的状况，可以利用 MySQL 的 Command Mode ，开二个视窗来做测试。

注3：
该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。
当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。

注
普通的select(没有加lock in share mode或for update)在MyISAM和InnoDB两种引擎下，都不会锁表或锁行的
对于MyISAM引擎，select语句加lock in share mode或for update是没有意义的，MyISAM必须用lock table来锁表操作
对于MyISAM引擎，update,insert语句会自动锁表

 

 

死锁  举个例子

首先客户端A创建一个包含一个行的表，然后开始一个事务。在这个事务内，A通过在共享模式选择行获得对行的S 锁定：

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;

Query OK, 0 rows affected (1.07 sec)

 

mysql> INSERT INTO t (i) VALUES(1);

Query OK, 1 row affected (0.09 sec)

 

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;   S 锁定

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.10 sec)

接着，客户端B开始一个事务并尝试从该表删除行：

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

 

mysql> DELETE FROM t WHERE i = 1;  阻塞中

删除操作要求一个X 锁定。因为这个锁定不兼容客户端A持有的S锁定，所以X 锁定不被允许，所以请求进入对行及客户端阻挡的锁定请求队列。

最后，客户端A也试图从表中删除该行：

mysql> DELETE FROM t WHERE i = 1;

ERROR 1213 (40001): Deadlock found when trying to get lock;

try restarting transaction

因为客户端A需要一个X 锁定来删除该行，所以在这里发生死锁。尽管如此，锁定请求不被允许，因为客户端B已经有一个对X锁定的请求并且它正等待客户端A释放S锁定。因为客户端B之前对X 锁定的请求，被客户端A持有的S锁定也不能升级到X锁定。因此，InnoDB对客户端A产生一个错误，并且释放它的锁定。在那一点上，客户端B的锁定请求可以被许可，并且客户端B从表中删除行。 

 

所以  在客户端 B  最后 执行commit  是可以完成对 该行加 X 锁的 请求， 可以顺利删除，因为 客户端B 排队在先，所以客户端A 是不可以完成

对该行加X 锁！

 另外 一种 
当一个线程等待另外一个线程持有的锁，而后者正在等待第一个线程持有的锁时，就会发生死锁。