The innoDB Lock Mechanism of MySQL and the handling method of deadlock 02/23 Update SLTechnology News&Howtos

The innoDB Lock Mechanism of MySQL and the handling method of deadlock

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly talks about "MySQL's innoDB lock mechanism and how to deal with deadlocks". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "MySQL's innoDB lock mechanism and how to handle deadlocks".

NnoDB locking Mechanism of MySQL

There are two biggest differences between InnoDB and MyISAM: one is to support transactions (TRANSACTION); the other is to use row-level locks. There are many differences between row-level locks and table-level locks. Select ID from table where id=1; with normal innodb will not have any locks. Next, the locking problem of InnoDB will be discussed in detail.

One: the introduction of InnoDB row lock.

Shared lock (S): allows one transaction to read a row, preventing other transactions from acquiring the exclusive lock of the same dataset, that is, the row I read, which you cannot modify

Exclusive lock (X): allows transaction update data to be acquired with exclusive locks, preventing other transactions from acquiring shared read locks and exclusive write locks for the same dataset. That is, the row I updated does not allow other transactions to read and update the same row.

In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity locking mechanism, InnoDB also has two internally used Intention Locks locks, both of which are table locks.

Intention shared lock (IS): a transaction intends to add a row shared lock to a data row, and the transaction must acquire the IS lock of the table before adding a shared lock to the data row.

Intention exclusive lock (IX): a transaction intends to add an exclusive lock to a data row, and the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row.

The intention lock is automatically added by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds exclusive locks (X) to the dataset involved; for normal SELECT statements, InnoDB does not add any locks; transactions can be shown to add shared or exclusive locks to the recordset through the following statement.

Shared lock (S): SELECT * FROM table_name WHERE... LOCK IN SHARE MODE

Exclusive lock (X): SELECT * FROM table_name WHERE. FOR UPDATE

InnoDB row lock mode compatibility list:

If the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction; conversely, if the two are not compatible, the transaction waits for the lock to be released.

Second, about the innodb lock mechanism, the implementation principle:

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks! Indexes are divided into primary key index and secondary index. If a sql statement operates on the primary key index, MySQL will lock the primary key index; if a statement operates on the secondary index, MySQL will lock the secondary index first, and then lock the relevant primary key index.

Then the innodb row lock is divided into three situations:

1) Record lock: lock an index entry, that is, lock a record.

2) Gap lock: lock the gap between index entries, the gap before the first record or the gap after the last record, that is, lock a range of records, excluding the record itself

3) Next-key Lock: locks a range of records and contains the record itself (a combination of the above two).

Note: the default level of InnoDB is the repeatable-read level, so what is said below is in the RR level.

Next-Key Lock is a combination of row locks and gap locks, so that when InnoDB scans an index record, it first adds a row lock (Record Lock) to the selected index record, and then adds a gap lock (Gap Lock) to the gap between the two sides of the index record. If a gap is locked by transaction T1, other transactions cannot insert records in that gap

Example 1:

Suppose we have a table:

+-+ +

| | id | age |

+-+ +

| | 1 | 3 |

| | 2 | 6 |

| | 3 | 9 |

+-+ +

The table structure is as follows:

CREATE TABLE `liuhe` (

`id`int (11) NOT NULL AUTO_INCREMENT

`age`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

KEY `keyname` (`age`)

) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=gbk

In this way, the index of our age segment is divided into

(negative infinity, 3)

(3pd6)

(6pr 9)

(9pr positive infinity)

Let's take a look at several situations:

1) when transaction An executes the following statement:

Mysql > select * from liuhe where age=6 for update

Not only do you use row locks to lock the corresponding rows of data, but also in the intervals on both sides, gap locks are added to (3pd6] and (6jue 9).

In this way, transaction B cannot insert new data in these two intervals, nor does it allow update liuhe set age=5 where id=1 (because this is also similar to adding data in the (3je 6] range), but transaction B can insert data in intervals outside the two intervals.

The experiments are as follows:

Transaction A:

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from liuhe; (index is available on age)

+-+ +

| | id | age |

+-+ +

| | 1 | 3 |

| | 2 | 6 |

| | 3 | 9 |

+-+ +

4 rows in set (0.00 sec)

Mysql > select * from liuhe where age=6 for update

+-+ +

| | id | age |

+-+ +

| | 2 | 6 |

+-+ +

1 row in set (0.00 sec)

Transaction B, trying the data of insert age=5, does have a lock waiting, indicating that it is true (3pc6) to put on an interval lock to prevent insertion in this interval.

Mysql > insert into liuhe (id,age) values

Check the status of the transaction and find that it is indeed waiting

Mysql > select * from INNODB_TRX\ G

* * 1. Row *

Trx_id: 27162

Trx_state:LOCK WAIT

Trx_started: 2018-04-06 00:03:39

Trx_requested_lock_id: 27162:529:4:3

Trx_wait_started: 2018-04-06 00:03:39

Trx_weight: 3

Trx_mysql_thread_id: 46

Trx_query: insert into liuhe (id,age) values (5penny 5)

Trx_operation_state: inserting

Trx_tables_in_use: 1

Trx_tables_locked: 1

Trx_lock_structs: 2

Trx_lock_memory_bytes: 360

Trx_rows_locked: 1

Trx_rows_modified: 1

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

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

As explained above: (3, 6) and (6, 9) have added gap locks. In this way, transaction B cannot insert new data in these two intervals, but transaction B can insert data in intervals outside the two intervals.

2) when transaction An executes the following statement:

Select * from fenye where age=7 for update

Then the interval (6. 9) is locked, and no other transaction can insert or update data in this interval.

3) when transaction An executes:

Select * from fenye where age=100 for update

Then the locked infinity is (9), where other transactions cannot insert new data and are not allowed to update existing data to this interval, that is, update liuhe set age=19 where id=1 is not allowed (because it is also similar to adding).

The whole example 1 illustrates:

Row locks prevent other transactions from being modified or deleted, and GAP locks prevent other transactions from being added (to prevent adding existing data from insert and update to this range). The Next-Key lock formed by the combination of row lock and GAP lock solves the problem of partial phantom reading when writing data at RR level, so we must pay attention to the problem of partial phantom reading.

Example 2:

If there are only 101 records in the emp table, the empid values are 1, 2, and 100, respectively, and the following SQL:

Select * from emp where empid > 100 for update

Is a range condition retrieval, InnoDB will not only lock records with an empid value of 101, but also lock "gaps" with an empid greater than 101 (these records do not exist), so that other transactions cannot insert data in a range greater than 100.

The purpose of InnoDB using gap lock is, on the one hand, to prevent false reading in order to meet the requirements of the relevant isolation level. For the above example, if gap lock is not used, if any record whose empid is greater than 100 is inserted by another transaction, then if the transaction executes the above statement again, false reading will occur.

Example 3

If there are only 101 records in the emp table, and their empid values are 1, 5, 7, 9, 10, 19, then the following sql:

Select * from emp where empid > 2 and empid desc innodb_locks

+-+-

+-+-

+-+-

10 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:28:56 > desc innodb_lock_waits

+-+-

+-+-

+-+-

4 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:29:05 > desc innodb_trx

+-- +

+-- +

| | trx_weight | bigint (21) unsigned | NO | | 0 | | #

| | trx_lock_structs | bigint (21) unsigned | NO | | 0 | | #

| | trx_unique_checks | int (1) | NO | | 0 | | # check whether it is unique or not |

| | trx_foreign_key_checks | int (1) | NO | | 0 | | # whether to check with foreign keys |

| | trx_adaptive_hash_latched | int (1) | NO | | 0 | | #

| | trx_adaptive_hash_timeout | bigint (21) unsigned | NO | | 0 | | #

+-- +

22 rows in set (0.01 sec)

You can see that mysql > show processlist; # #

Mysql > show engine innodb status\ G # # can also see the related deadlock problem.

Or:

Mysql > select ID,STATE from information_schema.processlist where user='system user'

Mysql > select concat ('KILL', id,';') from information_schema.processlist where user='system user'

+-- +

| | concat ('KILL', id,';') | |

+-- +

| | KILL 3101; | |

| | KILL 2946; | |

+-- +

2 rows in set (0.00 sec)

Batch kill multiple processes.

Mysql > select concat ('KILL', id,';') from information_schema.processlist where user='root' into outfile'/ tmp/a.txt'

Query OK, 2 rows affected (0.00 sec)

Five: about deadlocks:

MyISAM table locks are deadlock free, because MyISAM always gets all the locks you need at once, either satisfying them or waiting, so there is no deadlock. However, in InnoDB, locks are acquired step by step except for transactions composed of a single SQL, which determines that it is possible to have a deadlock in InnoDB.

After a deadlock occurs, InnoDB usually automatically detects it and causes one transaction to release the lock and roll back, while the other transaction acquires the lock and continues to complete the transaction. However, when external locks or table locks are involved, InnoDB does not automatically detect deadlocks, which needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve the deadlock problem. In the case of high concurrent access, if a large number of transactions hang because they cannot get the locks they need immediately, it will take up a lot of computer resources and cause serious performance problems. Even drag across the database. We can avoid this by setting the appropriate lock wait timeout threshold.

Generally speaking, deadlocks are a problem of application design, and most of them can be avoided by adjusting business processes, database object design, transaction size, and SQL statements to access the database.

Here are several common ways to avoid deadlocks through examples.

(1) in the application, if different programs will access multiple tables concurrently, it should be agreed to access the tables in the same order as far as possible, which can greatly reduce the chance of deadlock.

(2) when the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can also be greatly reduced.

(3) in a transaction, if you want to update the record, you should directly apply for a lock of sufficient level, that is, an exclusive lock, instead of applying for a shared lock first, and then apply for an exclusive lock when updating, because when the user applies for an exclusive lock, other transactions may have acquired the shared lock of the same record, resulting in lock conflicts or even deadlocks.

If a deadlock occurs, you can use the mysql > show engine innodb status\ G command to determine the cause of the last deadlock. The return results include details of the deadlock-related transactions, such as the SQL statement that caused the deadlock, the locks that the transaction has acquired, what locks it is waiting for, and the transactions that have been rolled back. Based on this, the causes of deadlock and improvement measures can be analyzed.

Conclusion: the locking mechanism of the MySQL innodb engine is more complex than that of the myisam engine, but the innodb engine supports a finer-grained locking mechanism, which of course brings more maintenance costs; then the row-level of innodb is realized by locking the index items, it is worth noting that if the table does not have an index, it will be locked at the table level, and the gap lock in the row-level lock will be used to solve the problem of partial false reading. As long as you know how locks work in MySQL innodb, it's easy to solve or avoid deadlocks!

At this point, I believe you have a deeper understanding of "MySQL's innoDB lock mechanism and how to handle deadlocks". 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.