mysql InnoDB鎖等待的查看以及分析

194次閱讀

共計 10182 個字符，預計需要花費 26 分鐘才能閱讀完成。

本篇內(nèi)容主要講解“mysql InnoDB 鎖等待的查看以及分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“mysql InnoDB 鎖等待的查看以及分析”吧!

在 InnoDB Plugin 之前，一般通過 show full processlist 和 show engine innodb status 命令查看當前的數(shù)據(jù)庫請求，然后再判斷當前事務中鎖的情況。隨著 mysql 的發(fā)展，已經(jīng)提供更加便捷的方法來監(jiān)控數(shù)據(jù)庫中的鎖等待現(xiàn)象了。

在 information_schema 下面有三張表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通過這三張表，可以更簡單地監(jiān)控當前的事務并分析可能存在的問題。

INNODB_TRX 表及結(jié)構(gòu)

Column nameDescriptionTRX_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 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 14.17.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 the innodb_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. (Only a single transaction at a time can modify the adaptive hash index.)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.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.

比較常用的列：

trx_id:InnoDB 存儲引擎內(nèi)部唯一的事物 ID
trx_status: 當前事務的狀態(tài)
trx_status: 事務的開始時間
trx_requested_lock_id：等待事務的鎖 ID
trx_wait_started：事務等待的開始時間
trx_weight：事務的權(quán)重，反應一個事務修改和鎖定的行數(shù)，當發(fā)現(xiàn)死鎖需要回滾時，權(quán)重越小的值被回滾
trx_mysql_thread_id：MySQL 中的進程 ID，與 show processlist 中的 ID 值相對應
trx_query：事務運行的 SQL 語句

INNODB_LOCKS

Column nameDescriptionLOCK_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 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_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, 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_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 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 nameDescriptionREQUESTING_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 with INNODB_LOCKS on LOCK_ID.

以上這些表，其實只要知道其中比較常用的字段，就差不多能夠滿足日常的工作需求了，下面通過測試進行演示；

一、準備工作

1、在 test 下面隨便創(chuàng)建一張表 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

（經(jīng)過幾次插入后 john 表的數(shù)據(jù) 671744 行）

mysql set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

（取消數(shù)據(jù)庫的自動 commit）

二、進行表 john 加鎖操作，腳本如下：

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

在另外一個窗口中監(jiān)控 innodb 鎖的狀態(tài)；

mysql SELECT * FROM INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: B14 / 請記住該 trx_id/
trx_state: RUNNING / 當前狀態(tài) /
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; / 當前執(zhí)行的語句 /
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 只是持有鎖，但并沒有產(chǎn)生鎖等待；

三、模擬鎖等待

3.1 在另外一個窗口中，執(zhí)行語句：

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 // 狀態(tài)為鎖等待 //
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 里面可以看到相應的狀態(tài) //
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，所以這邊只有兩個會話。（因此一個處于鎖等待，另外一個必然就是持有鎖的對象。實際的生產(chǎn)環(huán)境中可能這邊會出現(xiàn)很多列，所以需要用下面的語句才能判斷：鎖等待和持有鎖對象的匹配關(guān)系）

3.3 鎖等待和持有鎖的相互關(guān)系

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)