Rootop 服务器运维与web架构

mysql死锁 Deadlock found when trying to get lock; try restarting transaction

研究了一下mysql的死锁,记录如下。
比如有2个事务,执行的sql分别如下:
这里用 #N 标识sql语句的执行顺序,下面开启两个mysql客户端连接。

事务1
START TRANSACTION; #1
UPDATE username SET `name` = 't1' WHERE id = 1; #3
UPDATE username SET `name` = 't1' WHERE id = 2; #5
COMMIT;
事务2
START TRANSACTION; #2
UPDATE username SET `name` = 't2' WHERE id = 1; #6
UPDATE username SET `name` = 't2' WHERE id = 2;#4
COMMIT;

死锁:当出现2个(以上)事务互相等待对方释放锁的时候就会出现死锁。
PS:不管两个事务执行什么sql语句,只要出现互相等待对方释放就发生了死锁问题。

1、当执行#1 #2时两条事务开始
2、当执行#3 时,事务1将id=1的这条数据加锁(当sql语句执行时才加锁,事务开始时不会加)
3、当执行#4 时,事务2将id=2的这条数据加锁
4、当执行#5 时,事务1等待事务2释放锁(锁是在事务提交以后才释放)
此时,通过information_schema库INNODB_TRX事务表中查看正在运行的事务,注意2个事务中trx_weight的最小值,后续死锁时mysql可以确定需要回滚哪个事务。


5、当执行#6 时,事务2与事务1发生死锁。

mysql会报一个错误:Deadlock found when trying to get lock; try restarting transaction
通过mysql命令行执行:mysql> show engine innodb status\G; 查看mysql记录的信息(可以看到最后一次死锁)
------------------------
LATEST DETECTED DEADLOCK   # mysql检测到的最后一次死锁
------------------------
2019-03-13 11:52:13 0x7fd873618700
*** (1) TRANSACTION:  # 事务1
TRANSACTION 188912126, ACTIVE 32 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1979385, OS thread handle 140567861790464, query id 328763106 192.168.1.1 root updating
UPDATE username SET `name` = 't1' WHERE id = 2 # 执行的sql
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21691 page no 3 n bits 80 index PRIMARY of table `test_cjx`.`username` trx id 188912126 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000b4291ff; asc    B  ;;
 2: len 7; hex 2b0000015101e7; asc +   Q  ;;
 3: len 2; hex 7432; asc t2;;
 4: len 4; hex 80000000; asc     ;;

*** (2) TRANSACTION:  # 事务2
TRANSACTION 188912127, ACTIVE 31 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1980464, OS thread handle 140567625434880, query id 328764925 192.168.1.1 root updating
UPDATE username SET `name` = 't2' WHERE id = 1 # 执行的sql
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21691 page no 3 n bits 80 index PRIMARY of table `test_cjx`.`username` trx id 188912127 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000b4291ff; asc    B  ;;
 2: len 7; hex 2b0000015101e7; asc +   Q  ;;
 3: len 2; hex 7432; asc t2;;
 4: len 4; hex 80000000; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21691 page no 3 n bits 80 index PRIMARY of table `test_cjx`.`username` trx id 188912127 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000b4291fe; asc    B  ;;
 2: len 7; hex 2a00000150025c; asc *   P \;;
 3: len 2; hex 7431; asc t1;;
 4: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)  # mysql回滚了哪个事务
------------
TRANSACTIONS
------------

可以看到mysql回滚了事务2,此事务id为 188912127,此时事务1可以提交了,最终name的值应为t1。因为事务2的trx_weight权重最小(上面图显示了权重为3),所以回滚了(释放锁)。

原创文章,转载请注明。本文链接地址: http://www.rootop.org/pages/4279.html

赞赏

微信赞赏支付宝赞赏

作者:Venus

专注于 服务器运维与web架构 E-mail:venus#rootop.org

评论已关闭。