In-depth explanation of the locking mechanism in MySQL

Preface

In order to ensure the consistency and integrity of data, any database has a locking mechanism. The pros and cons of the locking mechanism are directly related to the concurrent processing capability and performance of a database system, so the implementation of the locking mechanism has become one of the core technologies of various databases.

About a few months ago, transactions were used in the project, and strong data consistency needed to be ensured. During this period, MySQL locks were also used, but at that time, I only had a glimpse of MySQL's locking mechanism, so this article intends to summarize MySQL's locking mechanism.

This article mainly discusses the MySQL lock mechanism. The MySQL version is 5.7 and the engine is InnoDB. Since there is a lot of knowledge and locking methods related to InnoDB locks in practice, there is not enough energy to list the locking process in all scenarios and analyze them. Only based on the current knowledge and combined with the official documents, I will talk about my understanding. If you find anything wrong, you are welcome to correct me.

Overview

In general, InnoDB has seven types of locks:

• Shared and Exclusive Locks
• Intention Locks
• Record Locks
• Gap Locks
• Next-key Locks
• Insert Intention Locks
• Auto-inc Locks

Detailed explanation of mysql lock

1. Shared and Exclusive Locks

• Shared locks (S locks) are used when reading data.
• Exclusive locks (eXclusive Locks, denoted as X locks), add X locks when modifying data

The semantics used are:

• Shared locks are not mutually exclusive, which can be abbreviated as: read and read can be parallel
• Exclusive locks are mutually exclusive with any other locks, which can be abbreviated as: write and read, write and write cannot be done in parallel

It can be seen that once the task of writing data is not completed, the data cannot be read by other tasks, which has a great impact on concurrency. Corresponding to the database, it can be understood that if the write transaction is not committed, the select to read related data will also be blocked. The select here refers to the one with a lock, and the ordinary select can still read the data (snapshot read).

2. Intention Locks

InnoDB introduced intention locks to support multiple granularity locking, which allows row-level locks to coexist with table-level locks. An intention lock means that at some point in the future, a transaction may need to add a shared/exclusive lock, so an intention is declared in advance.

1. Intention lock is a table-level lock (table-level locking);

2. Intention locks are divided into:

• Intention shared lock (IS), which indicates that the transaction intends to add shared S locks to certain rows in the table;
• Intention exclusive lock (IX), which indicates that the transaction intends to add exclusive X locks to certain rows in the table;

The syntax for locking is:

select ... lock in share mode; To set IS lock;
select ... for update; To set IX lock;

To obtain S/X locks for certain rows, a transaction must first obtain the IS/IX locks corresponding to the table. Intention locks only indicate intentions. Intention locks are compatible with each other. The compatible and mutually exclusive tables are as follows:

Although intention locks are compatible with each other, they are mutually exclusive with shared locks/exclusive locks. The compatible and mutually exclusive table is as follows:

Exclusive locks are very strong locks and are not compatible with other types of locks. This is actually very easy to understand. When modifying or deleting a row, you must obtain a strong lock to prohibit other concurrency on this row to ensure data consistency.

3. Record Locks

Record lock, which blocks the index record, for example (where id is pk):

create table lock_example(id smallint(10),name varchar(20),primary key id)engine=innodb;

The database isolation level is RR, and the table contains the following data:

10, zhangsan
20, lisi
30, wangwu

select * from t where id=1 for update;

In fact, here we first obtain the intention exclusive lock (IX) of the table, and then obtain the exclusive lock of this row of records (my understanding is that this is because the index is directly hit here) to prevent other transactions from inserting, updating, and deleting the row with id=1.

4. Gap Locks

Gap locks, which block a gap in an index record, or the range before the first index record, or the range after the last index record. Still using the above example, InnoDB, RR:

select * from lock_example
where id between 8 and 15 
for update;

This SQL statement blocks the interval (8,15) to prevent other transactions from inserting records whose id is in this interval.

The main purpose of gap locks is to prevent other transactions from inserting data in the interval, resulting in "non-repeatable reads". If the transaction isolation level is downgraded to Read Committed (RC), the gap lock will automatically become invalid.

5. Next-key Locks

A temporary key lock is a combination of a record lock and a gap lock. Its blocking range includes both index records and index intervals.

By default, InnoDB uses next-key locks to lock records. However, when the queried index contains a unique attribute, Next-Key Lock will be optimized and downgraded to Record Lock, which means that only the index itself is locked, not the range.

For example, the table lock_example is still the same as above, but id is downgraded to a normal index (key). That is to say, even if a lock is declared here (for update) and the index is hit, because the index has no UK constraint here, InnoDB will use next-key locks and the database isolation level RR:

Transaction A executes the following statement but is not committed:

select * from lock_example where id = 20 for update;

Transaction B starts and executes the following statement, which will block:

insert into lock_example values('zhang',15);

In the above example, after transaction A executes the query statement, the next-key lock is added to the record with id=20 by default, so transaction B will be blocked when inserting records between 10 (inclusive) and 30 (exclusive). The main purpose of temporary key lock is to avoid phantom read. If the transaction isolation level is downgraded to RC, the temporary key lock will also become invalid.

6. Insert Intention Locks

To modify and delete existing data rows, a mutual exclusion lock (X lock) must be strengthened. So, for data insertion, is it necessary to add such a strong lock to implement mutual exclusion? Insert the intention lock and give birth.

Insert intention lock is a type of gap lock (so, it is also implemented on the index), which is specifically for insert operations. When multiple transactions insert records into the same index and range, they will not block each other if the insertion positions do not conflict.

Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

For example (the table is still lock_example, and the data is still the same as above), transaction A is executed first, inserting a row into records 10 and 20, but has not been committed yet:

insert into t values(11, xxx);

Transaction B is executed later and also inserts a row into records 10 and 20:

insert into t values(12, ooo);

Because it is an insert operation, although the inserts are into the same interval, the inserted records do not conflict, so an insert intention lock is used. Here, transaction A does not block transaction B.

7. Auto-inc Locks

The auto-increment lock is a special table-level lock that is specifically used for transactions inserting AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting records into a table, all other transactions must wait so that the rows inserted by the first transaction have consecutive primary key values.

AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values ​​into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

For example (the table is still lock_example as above), but the id is AUTO_INCREMENT, and the data in the database table is:

1, zhangsan
2, lisi
3, wangwu

Transaction A is executed first but has not been committed yet: insert into t(name) values(xxx);

Execute after transaction B: insert into t(name) values(ooo);

At this time, transaction B's insert operation will be blocked until transaction A is committed.

Summarize

The 7 types of locks summarized above can be distinguished in two ways:

1. According to the degree of mutual exclusion of locks, they can be divided into shared locks and exclusive locks;

• Shared locks (S locks, IS locks) can improve read-read concurrency;
• In order to ensure strong data consistency, InnoDB uses strong mutex locks (X locks, IX locks) to ensure the seriality of modification and deletion of the same row of records;

2. According to the granularity of the lock, it can be divided into:

• Table lock: intention lock (IS lock, IX lock), self-increment lock;
• Row locks: record lock, gap lock, temporary key lock, insert intention lock;

in

• InnoDB's fine-grained locks (i.e. row locks) are implemented on index records (my understanding is that they will fail if the index is not hit);
• The record lock locks the index record; the gap lock locks the interval to prevent other transactions from inserting into the interval; the temporary key lock locks the index record + interval to prevent phantom reads;
• InnoDB uses insert intention locks to improve insert concurrency;
• Gap lock and next-key lock are only effective at levels above RR and will fail at RC.

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.