About the locks in the MySQL InnoDB Storage Engine

About the locks in the MySQL InnoDB Storage Engine

Recently, I encountered many lock problems. After solving these problems, I carefully read the books about locks and sorted them out as follows:

1. Lock type

The Innodb Storage engine implements the following two standard row-level locks:

1. The S lock allows a transaction to read a row of data.
2. X lock allows a transaction to delete or update a row of data.

When a transaction acquires the row r shared lock, another transaction can immediately obtain the row r shared lock because the read does not change the row r data, which means the lock compatibility. However, if a transaction wants to obtain the row r's exclusive lock, it must wait for the transaction to release the shared lock on the row r-in this case, the lock is incompatible, as shown in the following table:

Compatibility between exclusive locks and shared locks
	X exclusive lock	S shared lock
X exclusive lock	Conflict	Conflict
S shared lock	Conflict	Compatible

2. Lock Extension

The Innodb Storage engine supports multi-granularity locking, which allows row-level locks and table-level locks to coexist. To support lock operations at different granularities, the InnoDB Storage engine supports an additional lock method, that is, the intention lock. Intention locks are table-level locks designed to reveal the type of locks that the next row will be requested in a transaction. It can also be divided into two types:

1. The intention to share the Lock (IS Lock). A transaction wants to obtain the share Lock of several rows in a table.
2. The transaction wants to obtain the exclusive locks for certain rows in a table.

Because InnoDB supports row-level locks, the intention locks do not block any requests except for full table scan. Shared locks, exclusive locks, intent locks, and intention locks are mutually compatible and mutually exclusive. They can be expressed using a compatibility matrix (y indicates compatibility, n indicates incompatibility), as shown below:

	X exclusive lock	S shared lock	IX intention exclusive lock	IS intention sharing lock
X exclusive lock	Conflict	Conflict	Conflict	Conflict
S shared lock	Conflict	Compatible	Conflict	Compatible
IX intention exclusive lock	Conflict	Conflict	Compatible	Compatible
IS intention sharing lock	Conflict	Compatible	Compatible	Compatible

Resolution: The relationship between X and s is described in step 1. The relationships between IX and IS are all compatible. This IS also easy to understand, because they are both "intentional ", it is still in the YY stage and is compatible with other tasks;

The rest IS the relationship between X and IX, X and IS, S and IX, S and IS. We can deduce these four relationships from the relationship between X and S.

To put it simply: the relationship between X and X = X of IX. Why? Because the transaction has the right to obtain the X lock after obtaining the IX lock. If X and IX are compatible, two transactions obtain the X lock. This conflicts with the known X and X mutex, therefore, X and IX can only be mutually exclusive. The other three relationships are derived in the same way.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

3. Simulate lock scenarios

Before InnoDB Plugin, we can only view the current database request through show full processlis and show engine innodb status, and then judge the lock situation in the transaction. In the new InnoDB Plugin version, three tables are added to the information_schema Database: INNODB_LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS. With these three tables, you can easily monitor the current transaction and analyze possible lock problems. If the database runs normally, the three tables are empty and there are no records.

3.1. Enable transaction t1 and t2 to simulate the lock.

Enable two session windows and enable two transaction t1 and t2.

Start transaction t1 in the first window to execute a lock operation, as shown in the following t1 transaction window:

 

Mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

Mysql> begin;

Query OK, 0 rows affected (0.00 sec)

Start locking

Mysql> select * from test. t1 where a <5 for update;

+ --- + ---- +

| A | B | c |

+ --- + ---- +

| 1 | c2 | c2 |

| 2 | a |

| 3 | r5 | r3 |

| 4 | r4 | r5 |

+ --- + ---- +

4 rows in set (0.00 sec)

 

Mysql>

At this time, transaction t1 has locked all a <5 data rows of table t1, and then opened the second transaction t2 in the second window, as shown below, the update statement is always waiting for transaction t1 to release the lock resource. After several seconds, an error message is displayed, as shown in the following t2 transaction window:

Mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

Mysql> update test. t1 set B = 't2' where a = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Mysql>

 

3.2. view the lock information through three system tables

L 1, INNODB_TRX table

First, let's look at the important fields in the following table and the recorded information.

A) trx_id: unique transaction id of the internal transaction of the innodb Storage engine.

B) trx_state: the state of the current transaction.

C) trx_started: the start time of the transaction.

D) trx_requested_lock_id: id of the LOCK waiting for the transaction. If the status of the trx_state is lock wait, this value indicates the id of the LOCK resource occupied before the current transaction. If the trx_state is not lock wait, the value is null.

E) trx_wait_started: the start time of the transaction wait.

F) trx_weight: the weight of a transaction, reflecting the number of rows modified and locked by a transaction. In the innodb Storage engine, when a deadlock occurs and requires rollback, the innodb Storage engine selects the transaction with the minimum value for rollback.

G) trx_mysql_thread_id: the thread id in the running mysql, and thread_id in the record displayed by show full processlist.

H) trx_query: the SQL statement run by the transaction. In practice, it is found that it is sometimes displayed as a null value. When it is null, the trx_query is displayed as null after ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction)

......

Because we have simulated the transaction lock scenario and started transactions t1 and t2, now we can view the table information, there will be two records as follows:

Mysql> select * from INNODB_TRX \ G

* *************************** 1. row ***************************

Trx_id: 3015646

Trx_state: LOCK WAIT

Trx_started: 2014-10-07 18:29:39

Trx_requested_lock_id: 3015646: 797: 3: 2

Trx_wait_started: 2014-10-07 18:29:39

Trx_weight: 2

Trx_mysql_thread_id: 18

Trx_query: update test. t1 set B = 't2' where a = 1

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: READ COMMITTED

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

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

* *************************** 2. row ***************************

Trx_id: 3015645

Trx_state: RUNNING

Trx_started: 2014-10-07 18:29:15

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 2

Trx_mysql_thread_id: 17

Trx_query: NULL

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 2

Trx_lock_memory_bytes: 376

Trx_rows_locked: 4

Trx_rows_modified: 0

Trx_concurrency_tickets: 0

Trx_isolation_level: READ COMMITTED

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

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

 

Mysql>

Here we only record some currently running transactions, for example, transaction t2 is running trx_query: update test. t1 set B = 't2' where a = 1 SQL statement, t1 is executed first, so it is trx_state: The resources applied for first RUNNING have been RUNNING, after t2, the run is trx_state: lock wait has been waiting for t1 to release resources after execution. However, the lock details cannot be determined carefully. We need to look at the INNODB_LOCKS table data.

For more details, please continue to read the highlights on the next page:

1 2 3 4 Next Page