您好,登錄后才能下訂單哦!
這篇文章給大家分享的是有關(guān)MySQL 5.7如何查詢(xún)InnoDB鎖表的內(nèi)容。小編覺(jué)得挺實(shí)用的,因此分享給大家做個(gè)參考,一起跟隨小編過(guò)來(lái)看看吧。
InnoDB INFORMATION_SCHEMA 里有三張表可以用來(lái)監(jiān)控和診斷鎖的問(wèn)題。
INNODB_TRX
包含正在InnoDB里執(zhí)行的每個(gè)事務(wù)的相關(guān)信息,包括事務(wù)是否在等待鎖,事務(wù)的開(kāi)始時(shí)間和事務(wù)正在執(zhí)行的SQL語(yǔ)句。
INNODB_LOCKS
記錄InnoDB里每個(gè)正在等待另一個(gè)事務(wù)釋放鎖(INNODB_TRX.TRX_STATE='LOCK WAIT')的事務(wù)的相關(guān)信息,這些事務(wù)被“blocking lock request”事件阻塞,這些鎖的請(qǐng)求為被另一個(gè)事務(wù)占用的行鎖或表鎖。
等待或阻塞的事務(wù)不能進(jìn)行,直到占有鎖的事務(wù)提交或回滾。這張表記錄事務(wù)請(qǐng)求的鎖,占有鎖的事務(wù)信息,占有鎖的事務(wù)的狀態(tài)('RUNNING', 'LOCK WAIT', 'ROLLING BACK' or 'COMMITTING'),占有鎖的模式(read vs. write, shared vs. exclusive)。
INNODB_LOCK_WAITS
記錄哪些事務(wù)在等待鎖以及等待的鎖的類(lèi)型,REQUESTED_LOCK_ID代表事務(wù)請(qǐng)求的鎖的ID,BLOCKING_LOCK_ID代表占有鎖的ID。
事務(wù)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);
事務(wù)2
mysql> SELECT IMEI FROM t50 FOR UPDATE;
事務(wù)3
mysql> SELECT IMSI FROM t50 FOR UPDATE;
再開(kāi)一個(gè)會(huì)話(huà),查看線(xiàn)程信息
mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+---------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+--------------+---------------------------------+-----------+---------------+
| 70 | root | localhost | test | Query | 8 | Sending data | SELECT IMEI FROM t50 FOR UPDATE | 0 | 0 |
| 71 | root | localhost | test | Query | 310 | User sleep | SELECT SLEEP(1000) | 0 | 0 |
| 72 | root | localhost | test | Query | 6 | Sending data | SELECT IMSI FROM t50 FOR UPDATE | 0 | 0 |
| 73 | root | localhost | test | Query | 0 | init | show processlist | 0 | 0 |
+----+------+-----------+------+---------+------+--------------+---------------------------------+-----------+---------------+
4 rows in set (0.03 sec)
查看鎖的信息
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)
可以看到,最初執(zhí)行SQL的線(xiàn)程是 71,線(xiàn)程 70 等待線(xiàn)程 71 ,線(xiàn)程 72 在等待線(xiàn)程 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狀態(tài)變量來(lái)分析系統(tǒng)上的行鎖的爭(zhēng)奪情況
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)
感謝各位的閱讀!關(guān)于“MySQL 5.7如何查詢(xún)InnoDB鎖表”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,讓大家可以學(xué)到更多知識(shí),如果覺(jué)得文章不錯(cuò),可以把它分享出去讓更多的人看到吧!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。