InnoDb死锁分析

mysql8.0增加了performance_schema.data_locks，可以查看Innodb锁状态：

SELECT ENGINE_TRANSACTION_ID trx_id,INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE,LOCK_STATUS 
FROM performance_schema.data_locks;

一、死锁案例一

假设有两个连接conn1，conn2，且假设d011记录已存在：

1）conn1执行INSERT操作insert employees.departments values('d011', 'qiwei');

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2360		TABLE		IX	GRANTED
2	2360	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED

2）conn2执行INSERT操作：insert employees.departments values('d011', 'qiwei');

#	trx_ix	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2361		TABLE		IX	GRANTED
2	2361	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED
3	2360		TABLE		IX	GRANTED
4	2360	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED

3）conn1执行UPDATE操作：update employees.departments set dept_name='kivi' where dept_no='d011';

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2361		TABLE		IX	GRANTED
2	2361	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED
3	2360		TABLE		IX	GRANTED
4	2360	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED
5	2360	PRIMARY	RECORD	‘d011’	X,REC_NOT_GAP	WAITING

4）conn2执行UPDATE操作：update employees.departments set dept_name='kivi' where dept_no='d011';，发生死锁。

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2361		TABLE		IX	GRANTED
2	2361	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED
3	2361	PRIMARY	RECORD	‘d011’	X,REC_NOT_GAP	WAITING
4	2360		TABLE		IX	GRANTED
5	2360	PRIMARY	RECORD	‘d011’	S,REC_NOT_GAP	GRANTED
6	2360	PRIMARY	RECORD	‘d011’	X,REC_NOT_GAP	WAITING

二、死锁案例二

假设有两个连接conn1，conn2，且假设d015记录不存在：

1）conn1执行SELECT … FOR UPDATE操作：select * from employees.departments for update where dept_no='d015';

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2366		TABLE		IX	GRANTED
2	2366	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED

2）conn2执行SELECT … FOR UPDATE操作：conn2> select * from employees.departments for update where dept_no='d015';

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2367		TABLE		IX	GRANTED
2	2367	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED
3	2366		TABLE		IX	GRANTED
4	2366	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED

3）conn1执行INSERT操作：insert employees.departments values('d015', 'qiwei');

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2367		TABLE		IX	GRANTED
2	2367	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED
3	2366		TABLE		IX	GRANTED
4	2366	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED
5	2366	PRIMARY	RECORD	supremum pseudo-record	X, INSERT_INTENTION	WAITING

4）conn2执行INSERT操作：insert employees.departments values('d015', 'qiwei');，发生死锁。

#	trx_id	INDEX_NAME	LOCY_TYPE	LOCK_DATA	LOCK_MODE	LOCK_STATUS
1	2367		TABLE		IX	GRANTED
2	2367	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED
3	2367	PRIMARY	RECORD	supremum pseudo-record	X, INSERT_INTENTION	WAITING
4	2366		TABLE		IX	GRANTED
5	2366	PRIMARY	RECORD	supremum pseudo-record	X	GRANTED
6	2366	PRIMARY	RECORD	supremum pseudo-record	X, INSERT_INTENTION	WAITING

三、详解

// TODO

# 参考

1. https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-lock-tables.html
2. https://mysqlserverteam.com/innodb-data-locking-part-1-introduction/
3. https://mysqlserverteam.com/innodb-data-locking-part-2-locks/
4. https://mysqlserverteam.com/innodb-data-locking-part-2-5-locks-deeper-dive/
5. https://mysqlserverteam.com/innodb-data-locking-part-3-deadlocks/