MySQL 5.7 查询InnoDB锁表

梦里梦外; 2024-04-18 02:06 81阅读 0赞

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> 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)  
  
可以看到，最初执行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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2124488/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2124488/