在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管理更加便捷和容易了;