测试案例：
session1：
mysql> create table department
-> (
-> id int not null auto_increment,
> name varchar(20) not null ,
-> description varchar(100),
-> primary key PK_department (id) #
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into department(name,description) values('a','a');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from department;
+----+------+-------------+
| id | name | description |
+----+------+-------------+
| 1 | a | a |
+----+------+-------------+
1 row in set (0.00 sec)
mysql> insert into department(name,description) values('b','b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department(name,description) values('c','c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from department;
+----+------+-------------+
| id | name | description |
+----+------+-------------+
| 1 | ab | a |
| 2 | b | b |
| 3 | c | c |
+----+------+-------------+
3 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update department set name='abc' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql>
session2：
mysql> mysql> update department set description='abc' where name='ab';
问题排查：
session3：
mysql> show processlist;
+----+------+-----------+------------+---------+------+----------+---------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------------+---------+------+----------+---------------------------------------------------------+
| 95 | root | localhost | test_mysql | Sleep | 167 | | NULL |
| 96 | root | localhost | test_mysql | Query | 4 | updating | update department set description='abc' where name='ab' |
| 97 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------------+---------+------+----------+---------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT p2.`HOST` Blockedhost, p2.`USER` BlockedUser, r.trx_id BlockedTrxId, r.trx_mysql_thread_id BlockedThreadId, TIMESTAMPDIFF( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ) WaitTime, r.trx_query BlockedQuery, l.lock_table BlockedTable, m.`lock_mode` BlockedLockMode, m.`lock_type` BlockedLockType, m.`lock_index` BlockedLockIndex, m.`lock_space` BlockedLockSpace, m.lock_page BlockedLockPage, m.lock_rec BlockedLockRec, m.lock_data BlockedLockData, p.`HOST` blocking_host, p.`USER` blocking_user, b.trx_id BlockingTrxid, b.trx_mysql_thread_id BlockingThreadId, b.trx_query BlockingQuery, l.`lock_mode` BlockingLockMode, l.`lock_type` BlockingLockType, l.`lock_index` BlockingLockIndex, l.`lock_space` BlockingLockSpace, l.lock_page BlockingLockPage, l.lock_rec BlockingLockRec, l.lock_data BlockingLockData, IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id` INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id` INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id ORDER BY WaitTime DESC\G
*************************** 1. row ***************************
Blockedhost: localhost
BlockedUser: root
BlockedTrxId: 1309
BlockedThreadId: 96
WaitTime: 10
BlockedQuery: update department set description='abc' where name='ab'
BlockedTable: `test_mysql`.`department`
BlockedLockMode: X
BlockedLockType: RECORD
BlockedLockIndex: PRIMARY
BlockedLockSpace: 24
BlockedLockPage: 3
BlockedLockRec: 7
BlockedLockData: 1
blocking_host: localhost
blocking_user: root
BlockingTrxid: 1307
BlockingThreadId: 95
BlockingQuery: NULL
BlockingLockMode: X
BlockingLockType: RECORD
BlockingLockIndex: PRIMARY
BlockingLockSpace: 24
BlockingLockPage: 3
BlockingLockRec: 7
BlockingLockData: 1
idel_in_trx: 173 seconds
1 row in set, 3 warnings (0.00 sec)
mysql>
备注脚本：
SELECT
p2.`HOST` Blockedhost,
p2.`USER` BlockedUser,
r.trx_id BlockedTrxId,
r.trx_mysql_thread_id BlockedThreadId,
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) WaitTime,
r.trx_query BlockedQuery,
l.lock_table BlockedTable,
m.`lock_mode` BlockedLockMode,
m.`lock_type` BlockedLockType,
m.`lock_index` BlockedLockIndex,
m.`lock_space` BlockedLockSpace,
m.lock_page BlockedLockPage,
m.lock_rec BlockedLockRec,
m.lock_data BlockedLockData,
p.`HOST` blocking_host,
p.`USER` blocking_user,
b.trx_id BlockingTrxid,
b.trx_mysql_thread_id BlockingThreadId,
b.trx_query BlockingQuery,
l.`lock_mode` BlockingLockMode,
l.`lock_type` BlockingLockType,
l.`lock_index` BlockingLockIndex,
l.`lock_space` BlockingLockSpace,
l.lock_page BlockingLockPage,
l.lock_rec BlockingLockRec,
l.lock_data BlockingLockData,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
WaitTime DESC \G;
SELECT
p2.`HOST` 被阻塞方host,
p2.`USER` 被阻塞方用户,
r.trx_id 被阻塞方事务id,
r.trx_mysql_thread_id 被阻塞方线程号,
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) 等待时间,
r.trx_query 被阻塞的查询,
l.lock_table 阻塞方锁住的表,
m.`lock_mode` 被阻塞方的锁模式,
m.`lock_type` "被阻塞方的锁类型(表锁还是行锁)",
m.`lock_index` 被阻塞方锁住的索引,
m.`lock_space` 被阻塞方锁对象的space_id,
m.lock_page 被阻塞方事务锁定页的数量,
m.lock_rec 被阻塞方事务锁定行的数量,
m.lock_data 被阻塞方事务锁定记录的主键值,
p.`HOST` 阻塞方主机,
p.`USER` 阻塞方用户,
b.trx_id 阻塞方事务id,
b.trx_mysql_thread_id 阻塞方线程号,
b.trx_query 阻塞方查询,
l.`lock_mode` 阻塞方的锁模式,
l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",
l.`lock_index` 阻塞方锁住的索引,
l.`lock_space` 阻塞方锁对象的space_id,
l.lock_page 阻塞方事务锁定页的数量,
l.lock_rec 阻塞方事务锁定行的数量,
l.lock_data 阻塞方事务锁定记录的主键值,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事务空闲的时间
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
等待时间 DESC;
