欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MYSQL 加锁 S 锁 X 锁 死锁 事务

程序员文章站 2022-06-06 17:21:03
...
由 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 锁!

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