MySQL 5.7如何查询InnoDB锁表

103次阅读

共计 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 锁表”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

正文完