MySQL 5.7如何查詢InnoDB鎖表

146次閱讀

共計 5787 個字符，預計需要花費 15 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 MySQL 5.7 如何查詢 InnoDB 鎖表的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

InnoDB INFORMATION_SCHEMA 里有三張表可以用來監控和診斷鎖的問題。

INNODB_TRX
包含正在 InnoDB 里執行的每個事務的相關信息，包括事務是否在等待鎖，事務的開始時間和事務正在執行的 SQL 語句。

INNODB_LOCKS
記錄 InnoDB 里每個正在等待另一個事務釋放鎖 (INNODB_TRX.TRX_STATE= LOCK WAIT) 的事務的相關信息，這些事務被“blocking lock request”事件阻塞，這些鎖的請求為被另一個事務占用的行鎖或表鎖。
等待或阻塞的事務不能進行，直到占有鎖的事務提交或回滾。這張表記錄事務請求的鎖，占有鎖的事務信息，占有鎖的事務的狀態(RUNNING , LOCK WAIT , ROLLING BACK or COMMITTING)，占有鎖的模式(read vs. write, shared vs. exclusive)。

INNODB_LOCK_WAITS
記錄哪些事務在等待鎖以及等待的鎖的類型，REQUESTED_LOCK_ID 代表事務請求的鎖的 ID，BLOCKING_LOCK_ID 代表占有鎖的 ID。

事務 1
mysql BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql SELECT MSISDN FROM t50 FOR UPDATE;
+—————-+
| MSISDN |
+—————-+
| +3301000000011 |
| +3301000000013 |
| +3301000000015 |
| +3301000000015 |
| +3301000000017 |
| +3301000000019 |
+—————-+
6 rows in set (0.00 sec)

mysql SELECT SLEEP(1000);

事務 2
mysql SELECT IMEI FROM t50 FOR UPDATE;

事務 3
mysql SELECT IMSI FROM t50 FOR UPDATE;

查看鎖的信息
mysql SELECT r.trx_id waiting_trx_id,
– r.trx_mysql_thread_id waiting_thread,
– r.trx_query waiting_query,
– b.trx_id blocking_trx_id,
– b.trx_mysql_thread_id blocking_thread,
– b.trx_query blocking_query
– 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;
+—————-+—————-+———————————+—————–+—————–+———————————+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+—————-+—————-+———————————+—————–+—————–+———————————+
| 6288648 | 72 | SELECT IMSI FROM t50 FOR UPDATE | 6288647 | 70 | SELECT IMEI FROM t50 FOR UPDATE |
| 6288648 | 72 | SELECT IMSI FROM t50 FOR UPDATE | 6288638 | 71 | SELECT SLEEP(1000) |
| 6288647 | 70 | SELECT IMEI FROM t50 FOR UPDATE | 6288638 | 71 | SELECT SLEEP(1000) |
+—————-+—————-+———————————+—————–+—————–+———————————+
3 rows in set (0.00 sec)

可以看到，最初執行 SQL 的線程是 71，線程 70 等待線程 71，線程 72 在等待線程 70、71

mysql select * from information_schema.INNODB_LOCKS;
+—————-+————-+———–+———–+————–+—————–+————+———–+———-+—————-+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+—————-+————-+———–+———–+————–+—————–+————+———–+———-+—————-+
| 6288651:78:3:2 | 6288651 | X | RECORD | `test`.`t50` | GEN_CLUST_INDEX | 78 | 3 | 2 | 0x000000000607 |
| 6288650:78:3:2 | 6288650 | X | RECORD | `test`.`t50` | GEN_CLUST_INDEX | 78 | 3 | 2 | 0x000000000607 |
| 6288638:78:3:2 | 6288638 | X | RECORD | `test`.`t50` | GEN_CLUST_INDEX | 78 | 3 | 2 | 0x000000000607 |
+—————-+————-+———–+———–+————–+—————–+————+———–+———-+—————-+
3 rows in set (0.00 sec)

mysql select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_mysql_thread_id,trx_query from information_schema.INNODB_TRX;
+———+———–+———————+———————–+———————+———————+———————————+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_mysql_thread_id | trx_query |
+———+———–+———————+———————–+———————+———————+———————————+
| 6288669 | LOCK WAIT | 2016-09-05 14:14:28 | 6288669:78:3:2 | 2016-09-05 14:14:28 | 72 | SELECT IMSI FROM t50 FOR UPDATE |
| 6288668 | LOCK WAIT | 2016-09-05 14:14:26 | 6288668:78:3:2 | 2016-09-05 14:14:26 | 70 | SELECT IMEI FROM t50 FOR UPDATE |
| 6288638 | RUNNING | 2016-09-05 11:41:59 | NULL | NULL | 71 | SELECT SLEEP(1000) |
+———+———–+———————+———————–+———————+———————+———————————+
3 rows in set (0.00 sec)

mysql select * from information_schema.INNODB_LOCK_WAITS;
+——————-+——————-+—————–+——————+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+——————-+——————-+—————–+——————+
| 6288671 | 6288671:78:3:2 | 6288670 | 6288670:78:3:2 |
| 6288671 | 6288671:78:3:2 | 6288638 | 6288638:78:3:2 |
| 6288670 | 6288670:78:3:2 | 6288638 | 6288638:78:3:2 |
+——————-+——————-+—————–+——————+
3 rows in set (0.00 sec)

檢查 Innodb_row_lock 狀態變量來分析系統上的行鎖的爭奪情況
mysql show global status like %innodb%row%lock%
+——————————-+——-+
| Variable_name | Value |
+——————————-+——-+
| Innodb_row_lock_current_waits | 0 |
| Innodb_current_row_locks | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+——————————-+——-+
6 rows in set (0.00 sec)

感謝各位的閱讀！關于“MySQL 5.7 如何查詢 InnoDB 鎖表”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

正文完