mysql InnoDB锁等待

在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS

INNODB_TRX表及结构

比较常用的列：

trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态， RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
trx_status:事务的开始时间
trx_requested_lock_id：事务等待的锁的ID（如果事务状态不是LOCK WAIT，这个字段是NULL），详细的锁的信息可以连查INNODB_LOCKS表
trx_wait_started：事务等待的开始时间
trx_weight：事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的值被回滚
trx_mysql_thread_id：MySQL中的进程ID，与show processlist中的ID值相对应
trx_query：事务运行的SQL语句

trx_operation_state：事务当操作的类型 如updating or deleting，starting index read等

trx_tables_in_use：查询用到的表的数量

trx_tables_locked：查询加行锁的表的数量

trx_rows_locked：事务锁住的行数（不是准确数字）

trx_rows_modified：事务插入或者修改的行数

INNODB_LOCKS表

比较常用的列：

lock_id     锁ID
lock_trx_id     事务ID, 可以连INNODB_TRX表查事务详情
lock_mode     锁的模式： S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC
lock_type     锁的类型，行级锁 或者表级锁
lock_table     加锁的表
lock_index     如果是lock_type='RECORD' 行级锁 ,为锁住的索引，如果是表锁为null
lock_space     如果是lock_type='RECORD' 行级锁 ,为锁住对象的Tablespace ID，如果是表锁为null
lock_page     如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为null
lock_rec     如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为null
lock_data     事务锁住的主键值，若是表锁，则该值为null

INNODB_LOCK_WAITS 表

requesting_trx_id     申请锁资源的事务ID
requesting_lock_id     申请的锁的ID
blocking_trx_id     租塞的事务ID
blocking_lock_id     租塞的锁的ID

一、准备工作

1、在test下面随便创建一张表john，并取消自动commit操作，脚本如下：

mysql> use information_schema Database changed mysql> select count(*) from tables; +----------+ | count(*) | +----------+ |       81 | +----------+ 1 row in set (0.06 sec) mysql> create table test.john as select * from tables; Query OK, 82 rows affected (0.29 sec) Records: 82  Duplicates: 0  Warnings: 0   mysql> insert into john select * from john; Query OK, 671744 rows affected (2 min 19.03 sec) Records: 671744  Duplicates: 0  Warnings: 0 （经过几次插入后john表的数据671744行）   mysql> set @@autocommit=0; Query OK, 0 rows affected (0.00 sec) （取消数据库的自动commit）

 

二、进行表john加锁操作，脚本如下：

mysql> select count(*) from john for update; +----------+ | count(*) | +----------+ |  2686976 | +----------+ 1 row in set (8.19 sec)

 

在另外一个窗口中监控innodb锁的状态；

mysql> SELECT  * FROM INNODB_TRX\G; *************************** 1. row ***************************                     trx_id: B14                                                /请记住该trx_id/                  trx_state: RUNNING                                       /当前状态/                trx_started: 2014-11-29 14:07:51      trx_requested_lock_id: NULL           trx_wait_started: NULL                 trx_weight: 15905        trx_mysql_thread_id: 10                                          /在process 里面的id值/                  trx_query: select count(*) from john for update;    /当前执行的语句/        trx_operation_state: fetching rows          trx_tables_in_use: 1          trx_tables_locked: 1           trx_lock_structs: 15905      trx_lock_memory_bytes: 1554872            trx_rows_locked: 1360743          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 1 row in set (0.02 sec)

trx_id: B14 只是持有锁，但并没有产生锁等待；

三、模拟锁等待

3.1 在另外一个窗口中，执行语句：

mysql> select count(*) from john where table_name='CHARACTER_SETS' for update;

 

3.2 查看当前锁等待的情况

INNODB_TRX的锁情况：

mysql> SELECT  * FROM INNODB_TRX\G; *************************** 1. row ***************************                     trx_id: B15                                        trx_state: LOCK WAIT                       //状态为锁等待//                trx_started: 2014-11-29 14:12:28      trx_requested_lock_id: B15:0:32777:2           trx_wait_started: 2014-11-29 14:12:28                 trx_weight: 2        trx_mysql_thread_id: 10                           //在process里面可以看到相应的状态//                   trx_query: select count(*) from john where table_name='CHARACTER_SETS' for update                //锁等待的语句//        trx_operation_state: starting index read          trx_tables_in_use: 1          trx_tables_locked: 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: B14                                                     trx_state: RUNNING                trx_started: 2014-11-29 14:07:51      trx_requested_lock_id: NULL           trx_wait_started: NULL                 trx_weight: 31777        trx_mysql_thread_id: 8                  trx_query: NULL        trx_operation_state: NULL          trx_tables_in_use: 0          trx_tables_locked: 0           trx_lock_structs: 31777      trx_lock_memory_bytes: 3094968            trx_rows_locked: 2718752          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 rows in set (0.02 sec)

请注意：因为我们只有模拟两个session，所以这边只有两个会话。（因此一个处于锁等待，另外一个必然就是持有锁的对象。实际的生产环境中可能这边会出现很多列，所以需要用下面的语句才能判断：锁等待和持有锁对象的匹配关系）

 

3.3 锁等待和持有锁的相互关系

mysql> SELECT * FROM INNODB_LOCK_WAITS\G; *************************** 1. row *************************** requesting_trx_id: B15 requested_lock_id: B15:0:32777:2   blocking_trx_id: B14  blocking_lock_id: B14:0:32777:2 1 row in set (0.03 sec) ERROR:  No query specified

通过视图INNODB_LOCK_WAITS可以清晰的看到B14持有锁，而B15处于锁等待；

 

3.4 锁等待的原因

mysql> SELECT * FROM INNODB_LOCKS\G; *************************** 1. row ***************************     lock_id: B15:0:32777:2 lock_trx_id: B15   lock_mode: X   lock_type: RECORD  lock_table: `test`.`john`  lock_index: `GEN_CLUST_INDEX`  lock_space: 0   lock_page: 32777    lock_rec: 2   lock_data: 0x000000640000 *************************** 2. row ***************************     lock_id: B14:0:32777:2 lock_trx_id: B14   lock_mode: X   lock_type: RECORD  lock_table: `test`.`john`  lock_index: `GEN_CLUST_INDEX`  lock_space: 0   lock_page: 32777    lock_rec: 2   lock_data: 0x000000640000 2 rows in set (0.01 sec)

可以看到持有锁的模式、对象

 

3.5 在进程里面查看状态

Id值为8的进程，Info显示为NULL值，可以推断当前的session由于未进行commit导致锁未释放的；

 

总结：通过以上几个视图，就可以很快速的判断出锁等待的对象及原因了，从这上面也可以看出mysql管理更加便捷和容易了；