You are here
mysql InnoDB锁等待的查看及分析
说明：前面已经了解了InnoDB关于在出现锁等待的时候，会根据参数innodb_lock_wait_timeout的配置，判断是否需要进行timeout的操作，本文档介绍在出现锁等待时候的查看及分析处理；
在InnoDB Plugin之前，一般通过show full processlist和show engine innodb status命令查看当前的数据库请求，然后再判断当前事务中锁的情况。随着mysql的发展，已经提供更加便捷的方法来监控数据库中的锁等待现象了。
在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表，可以更简单地监控当前的事务并分析可能存在的问题。
INNODB_TRX表及结构
| Column name | Description |
| TRX_ID | Unique transaction ID number, internal to InnoDB. (Starting in MySQL 5.6, these IDs are not created for transactions that are read-only and non-locking. See Optimizing InnoDB Read-Only Transactions for details.) |
| TRX_WEIGHT | The weight of a transaction, reflecting (but not necessarily the exact count of) the number of rows altered and the number of rows locked by the transaction. To resolve a deadlock, InnoDB selects the transaction with the smallest weight as the “victim” to rollback. Transactions that have changed non-transactional tables are considered heavier than others, regardless of the number of altered and locked rows. |
| TRX_STATE | Transaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING. |
| TRX_STARTED | Transaction start time. |
| TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if TRX_STATE is LOCK WAIT, otherwise NULL). Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID. |
| TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if TRX_STATE is LOCK WAIT, otherwise NULL). |
| TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with PROCESSLIST on ID. See Section 14.17.2.3.1, “Potential Inconsistency with PROCESSLIST Data”. |
| TRX_QUERY | The SQL query that is being executed by the transaction. |
| TRX_OPERATION_STATE | The transaction's current operation, or NULL. |
| TRX_TABLES_IN_USE | The number of InnoDB tables used while processing the current SQL statement of this transaction. |
| TRX_TABLES_LOCKED | Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) |
| TRX_LOCK_STRUCTS | The number of locks reserved by the transaction. |
| TRX_LOCK_MEMORY_BYTES | Total size taken up by the lock structures of this transaction in memory. |
| TRX_ROWS_LOCKED | Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction. |
| TRX_ROWS_MODIFIED | The number of modified and inserted rows in this transaction. |
| TRX_CONCURRENCY_TICKETS | A value indicating how much work the current transaction can do before being swapped out, as specified by the innodb_concurrency_tickets option. |
| TRX_ISOLATION_LEVEL | The isolation level of the current transaction. |
| TRX_UNIQUE_CHECKS | Whether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
| TRX_FOREIGN_KEY_CHECKS | Whether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
| TRX_LAST_FOREIGN_KEY_ERROR | Detailed error message for last FK error, or NULL. |
| TRX_ADAPTIVE_HASH_LATCHED | Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.) |
| TRX_ADAPTIVE_HASH_TIMEOUT | Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. |
| TRX_IS_READ_ONLY | A value of 1 indicates the transaction is read-only. (5.6.4 and up.) |
| TRX_AUTOCOMMIT_NON_LOCKING | A value of 1 indicates the transaction is a SELECT statement that does not use the FOR UPDATE or LOCK IN SHARED MODE clauses, and is executing with the autocommit setting turned on so that the transaction will only contain this one statement. (5.6.4 and up.) When this column and TRX_IS_READ_ONLY are both 1, InnoDB optimizes the transaction to reduce the overhead associated with transactions that change table data. |
比较常用的列：
trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态
trx_status:事务的开始时间
trx_requested_lock_id：等待事务的锁ID
trx_wait_started：事务等待的开始时间
trx_weight：事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的值被回滚
trx_mysql_thread_id：MySQL中的进程ID，与show processlist中的ID值相对应
trx_query：事务运行的SQL语句
INNODB_LOCKS
| Column name | Description |
| LOCK_ID | Unique lock ID number, internal to InnoDB. Treat it as an opaque string. Although LOCK_ID currently contains TRX_ID, the format of the data in LOCK_ID is not guaranteed to remain the same in future releases. Do not write programs that parse the LOCK_ID value. |
| LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction can be found by joining with INNODB_TRX on TRX_ID. |
| LOCK_MODE | Mode of the lock. One of S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC for shared, exclusive, intention shared, intention exclusive row locks, shared and exclusive gap locks, intention shared and intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections Section 14.5.3, “InnoDB Lock Modes” and Section 14.5.2, “The InnoDB Transaction Model and Locking” for information on InnoDB locking. |
| LOCK_TYPE | Type of the lock. One of RECORD or TABLE for record (row) level or table level locks, respectively. |
| LOCK_TABLE | Name of the table that has been locked or contains locked records. |
| LOCK_INDEX | Name of the index if LOCK_TYPE='RECORD', otherwise NULL. |
| LOCK_SPACE | Tablespace ID of the locked record if LOCK_TYPE='RECORD', otherwise NULL. |
| LOCK_PAGE | Page number of the locked record if LOCK_TYPE='RECORD', otherwise NULL. |
| LOCK_REC | Heap number of the locked record within the page if LOCK_TYPE='RECORD', otherwise NULL. |
| LOCK_DATA | Primary key value(s) of the locked record if LOCK_TYPE='RECORD', otherwise NULL. This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL commands). If there is no primary key then the InnoDB internal unique row ID number is used. If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”. When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to NULL. |
INNODB_LOCK_WAITS
| Column name | Description |
| REQUESTING_TRX_ID | ID of the requesting transaction. |
| REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID. |
| BLOCKING_TRX_ID | ID of the blocking transaction. |
| BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID. |
以上这些表，其实只要知道其中比较常用的字段，就差不多能够满足日常的工作需求了，下面通过测试进行演示；
一、准备工作
1、在test下面随便创建一张表john，并取消自动commit操作，脚本如下：
| mysql> use information_schema Database changed mysql> select count(*) from tables; mysql> create table test.john as select * from tables;
mysql> insert into john select * from john; （经过几次插入后john表的数据671744行）
mysql> set @@autocommit=0; （取消数据库的自动commit） |
二、进行表john加锁操作，脚本如下：
mysql> select count(*) from john for update; |
在另外一个窗口中监控innodb锁的状态；
mysql> SELECT * FROM INNODB_TRX\G; |
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; |
请注意：因为我们只有模拟两个session，所以这边只有两个会话。（因此一个处于锁等待，另外一个必然就是持有锁的对象。实际的生产环境中可能这边会出现很多列，所以需要用下面的语句才能判断：锁等待和持有锁对象的匹配关系）
3.3 锁等待和持有锁的相互关系
mysql> SELECT * FROM INNODB_LOCK_WAITS\G; ERROR: |
通过视图INNODB_LOCK_WAITS可以清晰的看到B14持有锁，而B15处于锁等待；
3.4 锁等待的原因
mysql> SELECT * FROM INNODB_LOCKS\G; |
可以看到持有锁的模式、对象
3.5 在进程里面查看状态
Id值为8的进程，Info显示为NULL值，可以推断当前的session由于未进行commit导致锁未释放的；
总结：通过以上几个视图，就可以很快速的判断出锁等待的对象及原因了，从这上面也可以看出mysql管理更加便捷和容易了；
本文作者：JOHN，某上市公司DBA，业余时间专注于数据库的技术管理，从管理的角度去运用技术。