InnoDB lock type and state query

Locking is a key feature that distinguishes database systems from file systems. Databases use locks to support concurrent access to shared resources, providing data integrity and consistency. In addition, isolation of database transactions is also achieved through locks. InnoDB has consistently outperformed other database engines in this respect. InnoDB locks table data at the row level, while MyISAM locks only at the table level.

Locks in InnoDB storage engine

InnoDB storage engine implements the following two standard row-level locks:

• A shared Lock (S Lock) allows a transaction to read a row
• An X Lock that allows a transaction to delete or update a row of data

If one transaction T1 has acquired the shared lock on row R, then another transaction T2 can immediately acquire the shared lock on row R because the read does not change the data and can be read concurrently. However, if other transaction T3 wants to acquire the exclusive lock on row R, it must wait for transaction T1 and T2 to release the shared lock on row R before it can continue. Because the exclusive lock is generally acquired to change data, it cannot be read or other write operations at the same time.

InnoDB storage engine supports multi-granularity locking, which allows transaction row-level locks and table-level locks to exist simultaneously. To support locking at different granularity, InnoDB storage engine supports a type of locking called intentional locking. Intent locking is to divide the locked object into multiple layers. Intent locking means that the transaction wants to be locked at a finer granularity.

InnoDB storage engine intent locks are table level locks. The main purpose is to reveal the type of lock that will be requested for the next row in a transaction. It supports two types of intent locks:

• A transaction wants to acquire a shared Lock for rows in a table
• A transaction wants to acquire an exclusive Lock for rows in a table

Note that intent locks are table level locks and do not conflict with row-level X and S locks. It only collides with table level X and S. The following table shows the compatibility between table level intent locks and table level locks.

When adding a table-level X LOCK to a TABLE (perform ALTER TABLE, DROP TABLE, LOCK TABLES, etc.), if there is no intended LOCK, you need to traverse the entire TABLE to determine whether there is a row LOCK to avoid conflicts. If you have an intent lock, you only need to determine whether the intent lock is compatible with the table level lock to be added. Because the presence of an intent lock indicates the presence or imminent presence of a row-level lock, the result can be obtained without traversing the entire table.

If you treat the locked object as a tree, then locking the lowest level of the object, that is, the finest-grained object, requires locking the coarse-grained object first. As shown in the figure above, if you want to lock X on record m in table 1, then you need to lock IX on table 1 and X on record M. If any of these parts cause a wait, the operation needs to wait for the coarse-grained lock to complete.

InnoDB lock status query

Users can use the INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS tables under the INFOMATION_SCHEMA library to monitor current transactions and analyze possible lock problems. INNODB_TRX is defined in the following table and consists of eight fields.

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
************************************* 1.row *********************************************
trx_id:  7311F4
trx_state: LOCK WAIT
trx_started: 2010-01-04 10:49:33
trx_requested_lock_id: 7311F4:96:3:2
trx_wait_started: 2010-01-04 10:49:33
trx_weight: 2
trx_mysql_thread_id: 471719
trx_query: select * from parent lock in share mode
Copy the code

The INNODB_TRX table only shows the current InnoDB transaction, and does not directly determine some cases of locking. If you need to view locks, you also need to access the table INNODB_LOCKS, whose field composition is shown in the following table.

mysql> SELECT * FROM information_schema.INNODB_LOCKS\G;
*************************************** 1.row *************************************
lock_id: 7311F4:96:3:2
lock_trx_id: 7311F4
lock_mode: S
lock_type: RECORD
lock_table: 'mytest'.'parent'
lock_index: 'PRIMARY'
lock_space: 96
lock_page: 3
lock_rec: 2
lock_data: 1
Copy the code

After the table INNODB_LOCKS looks at how locked each table is, the user can determine how many waits are caused by it. It is not so easy to judge when the transaction volume is very high and locks and waits occur frequently. However, INNODB_LOCK_WAITS visually reflects the current transaction waits. The table INNODB_LOCK_WAITS consists of four fields, as shown in the following table.

mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS\G;
*******************************************1.row************************************
requesting_trx_id: 7311F4
requesting_lock_id: 7311F4:96:3:2
blocking_trx_id: 730FEE
blocking_lock_id: 730FEE:96:3:2
Copy the code

Using the SQL statement above, you can visually see which transaction blocks another transaction and then use the transaction ID and lock ID above to go to the INNODB_TRX and INNDOB_LOCKS tables for more detailed information.

Afterword.

We will continue to learn about InnoDB’s consistent non-locked read, please stay tuned.