首页 > 代码库 > information_schema系列八(事物，锁)

information_schema系列八(事物，锁)

2024-08-14 00:21:48   210人阅读

今天我们主要看一下MySQL information_schema里面的关于innodb的锁和事物的两三个系统表：
看一下锁对应的sql：
select * from innodb_lock_waits;
select * from innodb_locks limit 2\G
select * from information_schema.innodb_trx\G
select * from information_schema.innodb_trx where trx_id = 45734628\G
SELECT
lw.requesting_trx_id AS request_ID,
 trx.trx_mysql_thread_id as request_mysql_ID,
trx.trx_query AS request_command,
lw.blocking_trx_id AS blocking_ID,
 trx1.trx_mysql_thread_id as blocking_mysql_ID,
trx1.trx_query AS blocking_command,
lo.lock_index AS lock_index
FROM
information_schema.innodb_lock_waits lw
INNER JOIN information_schema.innodb_locks lo ON lw.requesting_trx_id = lo.lock_trx_id
INNER JOIN information_schema.innodb_locks lo1 ON lw.blocking_trx_id = lo1.lock_trx_id
INNER JOIN information_schema.innodb_trx trx ON lo.lock_trx_id = trx.trx_id
INNER JOIN information_schema.innodb_trx trx1 ON lo1.lock_trx_id = trx1.trx_id;
1： INNODB_LOCKS
2： INNODB_TRX
3： INNODB_LOCK_WAITS
三张表就一起实验好了，都是关于锁和事物的阻塞的。我们现在开两个终端。
第一个终端开启一个事物，进行更新：
root@localhost [(none)]>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>update qiandai.t1 set col_int_key=333 where pk=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
第二个终端直接也更新同一行数据：
update qiandai.t1 set col_int_key=222 where pk=10;
然后去查看三个表联合查询：
技术分享
技术分享
可以看得到，第二个更新是被阻塞的，因为第一个更新获取到了排它锁，所以第二个更新一致处于等待状态，直到锁等待时间超时：
SHOW VARIABLES LIKE ‘%LOCK_WAIT%‘;
上面可以查看到锁等待的超时时间，INNODB默认五十秒。
看一下三个表官方给的解释：
INNODB_LOCK_WAITS
Column nameDescription
REQUESTING_TRX_IDID of the requesting transaction.
REQUESTED_LOCK_IDID 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_IDID of the blocking transaction.
BLOCKING_LOCK_IDID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining withINNODB_LOCKS on LOCK_ID.
INNODB_LOCKS
Column nameDescription
LOCK_IDUnique lock ID number, internal to InnoDB. Treat it as an opaque string. Although LOCK_ID currently contains TRX_ID, the format of the data inLOCK_ID is not guaranteed to remain the same in future releases. Do not write programs that parse the LOCK_ID value.
LOCK_TRX_IDID of the transaction holding this lock. Details about the transaction can be found by joining with INNODB_TRX on TRX_ID.
LOCK_MODEMode of the lock. One of S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, or UNKNOWN. Lock modes other than AUTO_INC and UNKNOWNwill indicate GAP locks, if present. Refer to Section 15.5.1, “InnoDB Locking” for information about S, X, IS, and IX lock modes. Refer toSection 15.5.1, “InnoDB Locking” for information about GAP locks.
LOCK_TYPEType of the lock. One of RECORD or TABLE for record (row) level or table level locks, respectively.
LOCK_TABLEName of the table that has been locked or contains locked records.
LOCK_INDEXName of the index if LOCK_TYPE=‘RECORD‘, otherwise NULL.
LOCK_SPACETablespace ID of the locked record if LOCK_TYPE=‘RECORD‘, otherwise NULL.
LOCK_PAGEPage number of the locked record if LOCK_TYPE=‘RECORD‘, otherwise NULL.
LOCK_RECHeap number of the locked record within the page if LOCK_TYPE=‘RECORD‘, otherwise NULL.
LOCK_DATAPrimary 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 statements). If there is no primary key then the InnoDBinternal 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_TRX
Column nameDescription
TRX_IDUnique 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 Section 9.5.3, “Optimizing InnoDB Read-Only Transactions” for details.)
TRX_WEIGHTThe 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_STATETransaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
TRX_STARTEDTransaction start time.
TRX_REQUESTED_LOCK_IDID 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_STARTEDTime when the transaction started waiting on the lock (if TRX_STATE is LOCK WAIT, otherwise NULL).
TRX_MYSQL_THREAD_IDMySQL thread ID. Can be used for joining with PROCESSLIST on ID. See Section 15.15.2.3.1, “Potential Inconsistency with PROCESSLIST Data”.
TRX_QUERYThe SQL query that is being executed by the transaction.
TRX_OPERATION_STATEThe transaction‘s current operation, or NULL.
TRX_TABLES_IN_USEThe number of InnoDB tables used while processing the current SQL statement of this transaction.
TRX_TABLES_LOCKEDNumber 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_STRUCTSThe number of locks reserved by the transaction.
TRX_LOCK_MEMORY_BYTESTotal size taken up by the lock structures of this transaction in memory.
TRX_ROWS_LOCKEDApproximate 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_MODIFIEDThe number of modified and inserted rows in this transaction.
TRX_CONCURRENCY_TICKETSA value indicating how much work the current transaction can do before being swapped out, as specified by theinnodb_concurrency_tickets option.
TRX_ISOLATION_LEVELThe isolation level of the current transaction.
TRX_UNIQUE_CHECKSWhether 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_CHECKSWhether 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_ERRORDetailed error message for last FK error, or NULL.
TRX_ADAPTIVE_HASH_LATCHEDWhether or not the adaptive hash index is locked by the current transaction. When the adaptive hash index search system is partitioned, a single transaction does not lock the entire adaptive hash index. Adaptive hash index partitioning is controlled by innodb_adaptive_hash_index_parts, which is set to 8 by default.
TRX_ADAPTIVE_HASH_TIMEOUTWhether 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. When the adaptive hash index search system is partitioned (controlled by innodb_adaptive_hash_index_parts), the value remains 0.
TRX_IS_READ_ONLYA value of 1 indicates the transaction is read-only. (5.6.4 and up.)
TRX_AUTOCOMMIT_NON_LOCKINGA 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.
 
 
 
 
 
 

information_schema系列八(事物，锁)


联系
我们