Shulou(Shulou.com)05/31 Report--

This article mainly talks about "viewing and analysis of mysql InnoDB lock waiting". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "mysql InnoDB lock waiting for viewing and analysis" it!

Before InnoDB Plugin, it is common to view the current database request through the show full processlist and show engine innodb status commands, and then determine the lock in the current transaction. With the development of mysql, more convenient methods have been provided to monitor lock waiting in databases.

There are three tables under information_schema: INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS, which make it easier to monitor current transactions and analyze possible problems.

INNODB_ TRX table and its structure

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 LOCKIN 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.

More commonly used columns:

The only thing within the trx_id:InnoDB storage engine ID

Trx_status: status of the current transaction

Trx_status: the start time of the transaction

Trx_requested_lock_id: the lock ID waiting for the transaction

Trx_wait_started: the start time of transaction wait

Trx_weight: the weight of a transaction, reflecting the number of rows modified and locked by a transaction. When a deadlock is found to need to be rolled back, the value with lower weight is rolled back.

Process ID in trx_mysql_thread_id:MySQL, corresponding to ID value in show processlist

Trx_query: the SQL statement that the transaction runs

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.

In fact, as long as you know the more commonly used fields in the above tables, they can almost meet the needs of daily work. The following is demonstrated by testing.

I. preparatory work

1. Create a table john under test, and cancel the automatic commit operation. The script is as follows:

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

(671744 rows of data in the john table after several inserts)

Mysql > set @ @ autocommit=0

Query OK, 0 rows affected (0.00 sec)

(cancel the automatic commit of the database)

2. Perform table john locking operation. The script is as follows:

Mysql > select count (*) from john for update

+-+

| | count (*) |

+-+

| | 2686976 |

+-+

1 row in set (8.19 sec)

Monitor the status of the innodb lock in another window

Mysql > SELECT * FROM INNODB_TRX\ G

* * 1. Row *

Trx_id: B14 / Please remember the trx_id/

Trx_state: RUNNING / current status /

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 / id value in process /

Trx_query: select count (*) from john for update; / currently executed statement /

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 only holds the lock, but does not generate lock waiting

Third, analog lock waiting

3.1 in another window, execute the statement:

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

3.2 check the current lock waiting

Lock status of INNODB_TRX:

Mysql > SELECT * FROM INNODB_TRX\ G

* * 1. Row *

Trx_id: B15

Trx_state: LOCK WAIT / / status is lock waiting / /

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 / / you can see the corresponding status in process / /

Trx_query: select count (*) from john where table_name='CHARACTER_SETS' for update / / Lock waiting statement / /

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)

Please note that since we only have two session simulations, there are only two sessions here. So one is waiting for a lock, and the other must be the object that holds the lock. There may be many columns here in the actual production environment, so you need to use the following statement to determine the matching relationship between lock waiting and holding lock objects)

3.3 the relationship between waiting and holding locks

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

Through the view INNODB_LOCK_WAITS, you can clearly see that B14 holds the lock while B15 is waiting for the lock.

3.4 reasons for lock waiting

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.01sec)

You can see the mode and object that holds the lock

3.5 View status in the process

For a process with an ID value of 8, the Info is displayed as a null value. It can be inferred that the lock of the current session is not released due to no commit.

Summary: through the above views, we can quickly determine the object and reason of the lock waiting, from which we can see that mysql management is more convenient and easy.

At this point, I believe you have a deeper understanding of "viewing and analysis of mysql InnoDB lock waiting". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.