死锁笔记

mysql 版本5.7 ，引擎innodb，隔离级别 ：可重复读，测试死锁

事物1：

start transaction

update sylius_shop_user set username='xxx' where id=21;

事物2：

start transaction;

update sylius_shop_user set username='xxx' where id=21;

结果该条数据被上锁。

mysql> update sylius_shop_user set username='xxx' where id=21;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上锁时查看锁信息

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G

*************************** 1. row ***************************

    lock_id: 48976:320:3:22

lock_trx_id: 48976

  lock_mode: X

  lock_type: RECORD

lock_table: `ppgo-online_dev`.`sylius_shop_user`

lock_index: PRIMARY

lock_space: 320

  lock_page: 3

   lock_rec: 22

  lock_data: 21

*************************** 2. row ***************************

    lock_id: 48973:320:3:22

lock_trx_id: 48973

  lock_mode: X

  lock_type: RECORD

lock_table: `ppgo-online_dev`.`sylius_shop_user`

lock_index: PRIMARY

lock_space: 320

  lock_page: 3

   lock_rec: 22

  lock_data: 21

2 rows in set (0.00 sec)

查看等待的锁

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G

*************************** 1. row ***************************

requesting_trx_id: 48976

requested_lock_id: 48976:320:3:22

  blocking_trx_id: 48973

blocking_lock_id: 48973:320:3:22

1 row in set (0.00 sec)

事后检查有哪些死锁，优化程序

show engine innodb status;

结果中看

LATEST DETECTED DEADLOCK

HOLDS THE LOCK(S)

WE ROLL BACK TRANSACTION

RECORD LOCKS space id 320 page no 3 n bits 88 index `PRIMARY` of table `ppgo-online_dev`.`sylius_shop_user` trx id 48973 lock_mode X locks rec but not gap waiting

Record lock

表明 ，有死锁导致事物被回滚

当发现有死锁，查询进程（ show processlist ）可以通过运行时间来判断是否需要杀死，结束掉死锁进程

mysql> show processlist;

+----+------+-----------+-----------------+---------+------+----------+--------------------------------------------------------+

| Id | User | Host      | db              | Command | Time | State    | Info                                                   |

+----+------+-----------+-----------------+---------+------+----------+--------------------------------------------------------+

|  2 | root | localhost | ppgo-online_dev | Query   |    0 | init     | show processlist                                       |

|  3 | root | localhost | ppgo-online_dev | Query   |   20 | updating | update sylius_shop_user set username='xxx' where id=21 |

+----+------+-----------+-----------------+---------+------+----------+--------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> kill 3;

Query OK, 0 rows affected (0.01 sec)