- 全域性讀鎖(lock_global_read_lock) 會導致所有的更新操作被堵塞
- 全域性COMMIT鎖(make_global_read_lock_block_commit) 會導致所有的活躍事務無法提交
FLUSH TABLES WITH READ LOCK執行後整個系統會一直處於只讀狀態，直到顯示執行UNLOCK TABLES。這點請切記。
[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 processlist
Progress: 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 t0
Progress: 0.000
2 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: 67464
EXAMINED_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: 84576
EXAMINED_ROWS: 0
QUERY_ID: 1461
INFO_BINARY: select * from information_schema.processlist
TID: 8424
2 rows in set (0.02 sec)複製程式碼從上的輸出中，我們只發現了會話11 在等候一個全域性讀鎖。但這個鎖被誰持有，從這個輸出裡面我們找不到任何線索。我現在再來看看INNODB STATUS輸出：
...
------------
TRANSACTIONS
------------
Trx id counter 20439
Purge done for trx's n:o < 20422 undo n:o < 0 state: running but idle
History list length 176
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 11, OS thread handle 0x7f7f5cdb8b00, query id 1457 localhost root Waiting for global read lock
delete from t0
---TRANSACTION 0, not started
MySQL thread id 10, OS thread handle 0x7f7f5ce02b00, query id 1462 localhost root init
show engine innodb status
--------
...複製程式碼- 如果你用的Mysql 5.7，那麼你可以使用performance_schema.metadata_locks
- 如果你用的Mysql 5.6，那麼你可以使用performance_schema.events_statements_history
- 如果你用的Mysql版本比較老，那麼可以使用genearal log或者一些sql審計的日誌來定位
會話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 |
+----+------+-----------+------+---------+------+------------------------------+------------------+複製程式碼由於會話1執行了FTWL,導致了會話2中的DML無法執行。接下來，我們演示如何通過gdb去定位執行了FTWL的會話。見下面的步驟
1.找出myql的程式id， ps -ef | grep mysql
root 7743 2366 0 05:07 ? 00:00:01 /u02/mysql/bin/mysqld 複製程式碼(gdb) pset global_thread_list THD*
elem[0]: $1 = (THD *) 0x4a55de0
elem[1]: $2 = (THD *) 0x4a5cf10
elem[2]: $3 = (THD *) 0x4b24aa0
Set size = 3 複製程式碼上面的命令輸出了三個會話的記憶體地址。接下來我們根據這些記憶體地址去查詢每個會話各自對應的global_read_lock
4.依次在gdb中列印上面三個會話中的global_read_lock和thread_id的值
(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_id
p ((THD *) 0x4b24aa0)->thread_id
$7 = 8 //8號會話執行了FTWL 複製程式碼5.我們可以通過執行kill 8結束這個會話來釋放全域性的鎖。讓被堵住的會話，繼續執行下去。
在新開的mysql會話中，執行下面的語句
mysql> kill 8
以前被堵在的會話中，會看到下面的結果
mysql> delete from t;
Query OK, 0 rows affected (40 min 20.73 sec)複製程式碼