查看事务锁：innodb_trx + innodb_locks + innodb_lock_waits

「爱情、让人受尽委屈。」 2021-11-26 13:48 337阅读 0赞

记得以前，当出现：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，要解决是一件麻烦的事情；特别是当一个SQL执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束，DBA光从数据库无法着手找出源头是哪个SQL锁住了；有时候看看 **show engine innodb status， 并结合 show full processlist **能暂时解决问题，但一直不能精确定位。  
  
   
  
在5.5中，information\_schema 库中增加了三个关于锁的表（MEMORY引擎）：  
innodb\_trx         \#\# 当前运行的所有事务  
innodb\_locks       \#\# 当前出现的锁  
innodb\_lock\_waits  \#\# 锁等待的对应关系  
  
  
看到这个就非常激动，这可是解决了一个大麻烦，先来看一下表结构：  
复制代码  
root@127.0.0.1 : information\_schema 13:28:38> desc innodb\_locks;  
\+————-+———————+——+—–+———+——-+  
| Field       | Type                | Null | Key | Default | Extra |  
\+————-+———————+——+—–+———+——-+  
| lock\_id     | varchar(81)         | NO   |     |         |       |\#锁ID  
| lock\_trx\_id | varchar(18)         | NO   |     |         |       |\#拥有锁的事务ID  
| lock\_mode   | varchar(32)         | NO   |     |         |       |\#锁模式  
| lock\_type   | varchar(32)         | NO   |     |         |       |\#锁类型  
| lock\_table  | varchar(1024)       | NO   |     |         |       |\#被锁的表  
| lock\_index  | varchar(1024)       | YES  |     | NULL    |       |\#被锁的索引  
| lock\_space  | bigint(21) unsigned | YES  |     | NULL    |       |\#被锁的表空间号  
| lock\_page   | bigint(21) unsigned | YES  |     | NULL    |       |\#被锁的页号  
| lock\_rec    | bigint(21) unsigned | YES  |     | NULL    |       |\#被锁的记录号  
| lock\_data   | varchar(8192)       | YES  |     | NULL    |       |\#被锁的数据  
\+————-+———————+——+—–+———+——-+  
10 rows in set (0.00 sec)  
     
root@127.0.0.1 : information\_schema 13:28:56> desc innodb\_lock\_waits;  
\+——————-+————-+——+—–+———+——-+  
| Field             | Type        | Null | Key | Default | Extra |  
\+——————-+————-+——+—–+———+——-+  
| requesting\_trx\_id | varchar(18) | NO   |     |         |       |\#请求锁的事务ID  
| requested\_lock\_id | varchar(81) | NO   |     |         |       |\#请求锁的锁ID  
| blocking\_trx\_id   | varchar(18) | NO   |     |         |       |\#当前拥有锁的事务ID  
| blocking\_lock\_id  | varchar(81) | NO   |     |         |       |\#当前拥有锁的锁ID  
\+——————-+————-+——+—–+———+——-+  
4 rows in set (0.00 sec)  
     
root@127.0.0.1 : information\_schema 13:29:05> desc innodb\_trx ;  
\+—————————-+———————+——+—–+———————+——-+  
| Field                      | Type                | Null | Key | Default             | Extra |  
\+—————————-+———————+——+—–+———————+——-+  
| trx\_id                     | varchar(18)         | NO   |     |                     |       |\#事务ID  
| trx\_state                  | varchar(13)         | NO   |     |                     |       |\#事务状态：  
| trx\_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |\#事务开始时间；  
| trx\_requested\_lock\_id      | varchar(81)         | YES  |     | NULL                |       |\#innodb\_locks.lock\_id  
| trx\_wait\_started           | datetime            | YES  |     | NULL                |       |\#事务开始等待的时间  
| trx\_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |\#  
| trx\_mysql\_thread\_id        | bigint(21) unsigned | NO   |     | 0                   |       |\#事务线程ID  
| trx\_query                  | varchar(1024)       | YES  |     | NULL                |       |\#具体SQL语句  
| trx\_operation\_state        | varchar(64)         | YES  |     | NULL                |       |\#事务当前操作状态  
| trx\_tables\_in\_use          | bigint(21) unsigned | NO   |     | 0                   |       |\#事务中有多少个表被使用  
| trx\_tables\_locked          | bigint(21) unsigned | NO   |     | 0                   |       |\#事务拥有多少个锁  
| trx\_lock\_structs           | bigint(21) unsigned | NO   |     | 0                   |       |\#  
| trx\_lock\_memory\_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |\#事务锁住的内存大小（B）  
| trx\_rows\_locked            | bigint(21) unsigned | NO   |     | 0                   |       |\#事务锁住的行数  
| trx\_rows\_modified          | bigint(21) unsigned | NO   |     | 0                   |       |\#事务更改的行数  
| trx\_concurrency\_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |\#事务并发票数  
| trx\_isolation\_level        | varchar(16)         | NO   |     |                     |       |\#事务隔离级别  
| trx\_unique\_checks          | int(1)              | NO   |     | 0                   |       |\#是否唯一性检查  
| trx\_foreign\_key\_checks     | int(1)              | NO   |     | 0                   |       |\#是否外键检查  
| trx\_last\_foreign\_key\_error | varchar(256)        | YES  |     | NULL                |       |\#最后的外键错误  
| trx\_adaptive\_hash\_latched  | int(1)              | NO   |     | 0                   |       |\#  
| trx\_adaptive\_hash\_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |\#  
\+—————————-+———————+——+—–+———————+——-+  
22 rows in set (0.01 sec)  
复制代码  
  
   
  
   
  
下面我们来动手看看数据吧：  
  
   
  
\#\#建立测试数据：  
复制代码  
use test;  
create table tx1  
(id int primary key ,  
c1 varchar(20),  
c2 varchar(30))  
engine=innodb default charset = utf8 ;  
  
insert into tx1 values  
(1,’aaaa’,'aaaaa2′),  
(2,’bbbb’,'bbbbb2′),  
(3,’cccc’,'ccccc2′);  
  
commit;  
复制代码  
  
   
  
\#\#\#产生事务  
\#\#\# Session1  
start transaction;  
update tx1 set c1=’heyf’,c2=’heyf’ where id = 3;  
  
   
  
\#\#\# 产生事务，在 innodb\_trx 就有数据  
复制代码  
root@127.0.0.1 : information\_schema 13:38:21> select \* from innodb\_trx \\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                    trx\_id: 3669D82  
                 trx\_state: RUNNING  
               trx\_started: 2010-12-24 13:38:06  
     trx\_requested\_lock\_id: NULL  
          trx\_wait\_started: NULL  
                trx\_weight: 3  
       trx\_mysql\_thread\_id: 2344  
                 trx\_query: NULL  
       trx\_operation\_state: NULL  
         trx\_tables\_in\_use: 0  
         trx\_tables\_locked: 0  
          trx\_lock\_structs: 2  
     trx\_lock\_memory\_bytes: 376  
           trx\_rows\_locked: 1  
         trx\_rows\_modified: 1  
   trx\_concurrency\_tickets: 0  
       trx\_isolation\_level: REPEATABLE READ  
         trx\_unique\_checks: 1  
    trx\_foreign\_key\_checks: 1  
trx\_last\_foreign\_key\_error: NULL  
 trx\_adaptive\_hash\_latched: 0  
 trx\_adaptive\_hash\_timeout: 10000  
1 row in set (0.00 sec)  
复制代码  
  
   
  
\#\#\# 由于没有产生锁等待，下面两个表没有数据  
root@127.0.0.1 : information\_schema 13:38:31> select \* from innodb\_lock\_waits \\G  
Empty set (0.00 sec)  
  
root@127.0.0.1 : information\_schema 13:38:57> select \* from innodb\_locks \\G  
Empty set (0.00 sec)  
  
   
  
\#\#\#\# 产生锁等待  
\#\#\#\# session 2  
复制代码  
start transaction;  
update tx1 set c1=’heyfffff’,c2=’heyffffff’ where id =3 ;  
  
root@127.0.0.1 : information\_schema 13:39:01> select \* from innodb\_trx \\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                    trx\_id: 3669D83   \#\#第2个事务  
                 trx\_state: LOCK WAIT   \#\# 处于等待状态  
               trx\_started: 2010-12-24 13:40:07  
     trx\_requested\_lock\_id: 3669D83:49:3:4  \#\#请求的锁ID  
          trx\_wait\_started: 2010-12-24 13:40:07  
                trx\_weight: 2  
       trx\_mysql\_thread\_id: 2346       \#\#线程 ID  
                 trx\_query: update tx1 set c1=’heyfffff’,c2=’heyffffff’ where id =3  
       trx\_operation\_state: starting index read  
         trx\_tables\_in\_use: 1      \#\#需要用到1个表  
         trx\_tables\_locked: 1      \#\#有1个表被锁  
          trx\_lock\_structs: 2  
     trx\_lock\_memory\_bytes: 376  
           trx\_rows\_locked: 1  
         trx\_rows\_modified: 0  
   trx\_concurrency\_tickets: 0  
       trx\_isolation\_level: REPEATABLE READ  
         trx\_unique\_checks: 1  
    trx\_foreign\_key\_checks: 1  
trx\_last\_foreign\_key\_error: NULL  
 trx\_adaptive\_hash\_latched: 0  
 trx\_adaptive\_hash\_timeout: 10000  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                    trx\_id: 3669D82 \#\#第1个事务  
                 trx\_state: RUNNING  
               trx\_started: 2010-12-24 13:38:06  
     trx\_requested\_lock\_id: NULL  
          trx\_wait\_started: NULL  
                trx\_weight: 3  
       trx\_mysql\_thread\_id: 2344  
                 trx\_query: NULL  
       trx\_operation\_state: NULL  
         trx\_tables\_in\_use: 0  
         trx\_tables\_locked: 0  
          trx\_lock\_structs: 2  
     trx\_lock\_memory\_bytes: 376  
           trx\_rows\_locked: 1  
         trx\_rows\_modified: 1  
   trx\_concurrency\_tickets: 0  
       trx\_isolation\_level: REPEATABLE READ  
         trx\_unique\_checks: 1  
    trx\_foreign\_key\_checks: 1  
trx\_last\_foreign\_key\_error: NULL  
 trx\_adaptive\_hash\_latched: 0  
 trx\_adaptive\_hash\_timeout: 10000  
2 rows in set (0.00 sec)  
  
root@127.0.0.1 : information\_schema 13:40:12> select \* from innodb\_locks \\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
    lock\_id: 3669D83:49:3:4      \#\# 第2个事务需要的锁  
lock\_trx\_id: 3669D83  
  lock\_mode: X  
  lock\_type: RECORD  
 lock\_table: \`test\`.\`tx1\`  
 lock\_index: \`PRIMARY\`  
 lock\_space: 49  
  lock\_page: 3  
   lock\_rec: 4  
  lock\_data: 3  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
    lock\_id: 3669D82:49:3:4     \#\# 第1个事务需要的锁  
lock\_trx\_id: 3669D82  
  lock\_mode: X  
  lock\_type: RECORD  
 lock\_table: \`test\`.\`tx1\`  
 lock\_index: \`PRIMARY\`  
 lock\_space: 49  
  lock\_page: 3  
   lock\_rec: 4  
  lock\_data: 3  
2 rows in set (0.00 sec)  
  
root@127.0.0.1 : information\_schema 13:40:15> select \* from innodb\_lock\_waits \\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
requesting\_trx\_id: 3669D83         \#\# 请求锁的事务  
requested\_lock\_id: 3669D83:49:3:4  \#\# 请求锁的锁ID  
  blocking\_trx\_id: 3669D82         \#\# 拥有锁的事务  
 blocking\_lock\_id: 3669D82:49:3:4  \#\# 拥有锁的锁ID  
1 row in set (0.00 sec)  
复制代码  
  
   
  
   
  
哈哈，有了以上这些信息，以下问题就迎刃而解啦。当前有哪些事务在等待锁？ 这些锁需要锁哪些表，锁哪些索引，锁哪些记录和值？处于等待状态的相关SQL是什么？在等待哪些事务完成 ？拥有当前锁的SQL是什么？