[test]> flush tables with read lock;Query OK, 0 rows affected (0.06 sec)[test]> show full processlist\G*************************** 1. row *************************** Id: 10 User: root Host: localhost db: test Command: Query Time: 0 State: init Info: show full processlistProgress: 0.000*************************** 2. row *************************** Id: 11 User: root Host: localhost db: test Command: Query Time: 743 State: Waiting for global read lock Info: delete from t0Progress: 0.0002 rows in set (0.00 sec)[test]> select * from information_schema.processlist\G*************************** 1. row *************************** ID: 11 USER: root HOST: localhost DB: test COMMAND: Query TIME: 954 STATE: Waiting for global read lock INFO: delete from t0 TIME_MS: 954627.587 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 67464EXAMINED_ROWS: 0 QUERY_ID: 1457 INFO_BINARY: delete from t0 TID: 8838 *************************** 2. row *************************** ID: 10 USER: root HOST: localhost DB: test COMMAND: Query TIME: 0 STATE: Filling schema table INFO: select * from information_schema.processlist TIME_MS: 0.805 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 84576EXAMINED_ROWS: 0 QUERY_ID: 1461 INFO_BINARY: select * from information_schema.processlist TID: 84242 rows in set (0.02 sec)复制代码
...------------TRANSACTIONS------------Trx id counter 20439Purge done for trx's n:o < 20422 undo n:o < 0 state: running but idleHistory list length 176LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 11, OS thread handle 0x7f7f5cdb8b00, query id 1457 localhost root Waiting for global read lockdelete from t0---TRANSACTION 0, not startedMySQL thread id 10, OS thread handle 0x7f7f5ce02b00, query id 1462 localhost root initshow engine innodb status--------...复制代码
会话1：flush tables with read lock;Query OK, 0 rows affected (0.00 sec)会话2：mysql> delete from t; --被hang住会话3：mysql> show processlist;+----+------+-----------+------+---------+------+------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+------------------------------+------------------+| 7 | root | localhost | test | Query | 227 | Waiting for global read lock | delete from t || 8 | root | localhost | NULL | Sleep | 215 | | NULL || 9 | root | localhost | NULL | Query | 0 | init | show processlist |+----+------+-----------+------+---------+------+------------------------------+------------------+复制代码
root 7743 2366 0 05:07 ? 00:00:01 /u02/mysql/bin/mysqld 复制代码
(gdb) pset global_thread_list THD*elem[0]: $1 = (THD *) 0x4a55de0elem[1]: $2 = (THD *) 0x4a5cf10elem[2]: $3 = (THD *) 0x4b24aa0Set size = 3 复制代码
(gdb) p ((THD *) 0x4a55de0)->global_read_lock$4 = { static m_active_requests = 1, m_state = Global_read_lock::GRL_NONE, m_mdl_global_shared_lock = 0x0, m_mdl_blocks_commits_lock = 0x0} //这个会话的Global_read_lock为空，不是我们要找的(gdb) p ((THD *) 0x4a5cf10)->global_read_lock$5 = { static m_active_requests = 1, m_state = Global_read_lock::GRL_NONE, m_mdl_global_shared_lock = 0x0, m_mdl_blocks_commits_lock = 0x0} //这个会话的Global_read_lock也为空，不是我们要找的(gdb) p ((THD *) 0x4b24aa0)->global_read_lock$6 = { static m_active_requests = 1, m_state = Global_read_lock::GRL_ACQUIRED_AND_BLOCKS_COMMIT, m_mdl_global_shared_lock = 0x7f6034002bb0, m_mdl_blocks_commits_lock = 0x7f6034002c20} //这个会话的Global_read_lock不为空，GRL_ACQUIRED_AND_BLOCKS_COMMIT表示全局读锁与commit锁，这个就是我们要好的。我接下来打印出它的thread_idp ((THD *) 0x4b24aa0)->thread_id$7 = 8 //8号会话执行了FTWL 复制代码 在新开的mysql会话中，执行下面的语句mysql> kill 8以前被堵在的会话中，会看到下面的结果mysql> delete from t;Query OK, 0 rows affected (40 min 20.73 sec)复制代码