Shulou(Shulou.com)06/01 Report--

Preface

In order to ensure the consistent integrity of data, there is a locking mechanism in any database. The advantages and disadvantages of locking mechanism should directly consider the concurrent processing ability and performance of a database system, so the implementation of locking mechanism has become one of the core technologies of various databases.

About a few months ago, transactions were used in the project to ensure strong data consistency, and mysql locks were also used during this period, but the locking mechanism of mysql was just a glimpse in the pipe at that time, so this article intends to summarize the locking mechanism of mysql.

This paper mainly discusses the mechanism of mysql lock, mysql version is 5.7and the engine is innodb. Because there are many knowledge and locking methods about innodb lock in practice, we do not have so much energy to list and analyze the locking process in all scenarios. Only according to the current knowledge, combined with official documents, talk about your own understanding. If you find something wrong, welcome to correct it.

Overview

In general, there are seven types of locks in InnoDB:

Shared / exclusive lock (Shared and Exclusive Locks) intention lock (Intention Locks) record lock (Record Locks) gap lock (Gap Locks) key lock (Next-key Locks) insert intention lock (Insert Intention Locks) self-increment lock (Auto-inc Locks)

Detailed explanation of mysql lock

1. Shared / exclusive lock (Shared and Exclusive Locks)

Shared lock (Share Locks, S lock), S lock (eXclusive Locks, X lock) when reading data, X lock when data modification

The semantics used are:

Shared locks are not mutually exclusive, abbreviated as: reading can be parallel exclusive lock and any lock mutually exclusive, abbreviated as: write and read, write and write cannot be parallel

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

two。 Intention lock (Intention Locks)

InnoDB introduces intentional locks (intention locks) to support the multi-granularity locking mechanism (multiple granularity locking), that is, allowing row-level locks and table-level locks to coexist. Intention lock means that at some point in the future, a transaction may have to add a shared / exclusive lock, so declare an intention in advance.

1. An intention lock is a table-level locking at the table level.

2. Intention lock is divided into:

Intention shared lock (intention shared lock, IS), which indicates that the transaction intends to add shared S locks to certain rows in the table, and intention exclusive locks (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; wants to set IX lock

In order for a transaction to obtain the IS/IX X lock of certain rows, it must first obtain the corresponding Shammox lock of the table. The intention lock only indicates the intention, and the intention locks are compatible with each other. The compatible mutex table is as follows:

ISIXIS is compatible with IX.

Although the intention lock is compatible with each other, it is mutually exclusive with the shared lock / exclusive lock, and its compatibility mutex table is as follows:

SXIS compatible mutex IX mutex

Exclusive locks are strong locks and are not compatible with other types of locks. It is easy to understand that when you modify and delete a row, you must acquire a strong lock to prohibit other concurrency on the row to ensure data consistency.

3. Record lock (Record Locks)

Record lock, which blocks index records, for example (where id is contention):

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, this is to 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 it directly hits the index), in order to prevent other transactions from inserting, updating, and deleting this row of id=1.

4. Gap lock (Gap Locks)

A gap lock that blocks the interval in the index record, or the range before the first index record, or after the last index record. Still the above example, InnoDB,RR:

Select * from lock_examplewhere id between 8 and 15 for update

This SQL statement blocks the interval (815) to prevent other transactions from inserting records where the id is in the interval.

The main purpose of the gap lock is to prevent other transactions from inserting data into the interval, resulting in "unrepeatable readings". If you downgrade the isolation level of a transaction to read commit (Read Committed, RC), the gap lock will automatically fail.

5. Key lock (Next-key Locks)

Key lock is a combination of record lock and gap lock. Its blocking range includes both index record and index interval.

By default, innodb uses next-key locks to lock records. However, when the index of the query contains unique attributes, Next-Key Lock optimizes and downgrades it to Record Lock, that is, 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, even if for update is declared here and the index is hit, because the index has no UK constraints here, innodb will use next-key locks, database isolation level RR:

Transaction An executes the following statement and is not committed:

Select * from lock_example where id = 20 for update

Transaction B begins and executes the following statement, which blocks:

Insert into lock_example values ('zhang',15)

As in the example above, after transaction An executes the query statement, next-key lock is added to the id=20 record by default, so transaction B inserts records between 10 (inclusive) and 30 (excluding) will block. The main purpose of the key lock is also to avoid Phantom Read. If the isolation level of the transaction is downgraded to RC, the critical lock will also fail.

6. Insert intention lock (Insert Intention Locks)

The mutex lock (X lock) must be strengthened for the modification and deletion of existing data rows, so is it necessary to add such a strong lock to implement mutex for data insertion? Insert the intention lock and give birth.

Insert intention locks, a kind of Gap Locks (therefore, also implemented on indexes), are specific to insert operations. When multiple transactions insert records in the same index and in the same range, if the inserted positions do not conflict, they will not block each other.

Insert Intention Lock signals the intent to insertin 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 the same as the example above, lock_example, the data is still the same as above), transaction An executes first, inserts a row in 10 and 20 records, and has not yet committed:

Insert into t values (11, xxx)

Executed after transaction B, and a row was also inserted into the 10 and 20 records:

Insert into t values (12, ooo)

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

7. Self-increasing lock (Auto-inc Locks)

A self-incrementing lock is a special table-level lock (table-level lock) that inserts AUTO_INCREMENT-type columns specifically for transactions. In the simplest case, if one transaction is inserting records into the table, all other transactions must wait so that the row inserted by the first transaction is a consecutive primary key value.

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 the same as the example lock_example above), but id is AUTO_INCREMENT, and the data in the database table is:

1, zhangsan

2, lisi

3, wangwu

Transaction An is executed first and has not yet been committed: insert into t (name) values (xxx)

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

At this point transaction B insert will block until transaction A commits.

Summary

According to personal understanding, the seven 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 and exclusive locks.

Shared locks (S lock, IS lock) can improve the concurrency of reading. In order to ensure strong consistency of data, InnoDB uses strong mutex locks (X lock, IX lock) to ensure the serialization of modification and deletion of records on the same line.

two。 According to the granularity of the lock, it can be divided into:

Table lock: intention lock (IS lock, IX lock), self-increment lock; row lock: record lock, gap lock, key lock, insert intention lock

Among them

InnoDB fine-grained locks (that is, row locks) are implemented on index records (my understanding is that they will be invalidated if the index is missed); record locks lock index records; gap locks lock intervals to prevent insertions by other transactions during intervals; key locks lock index records + intervals to prevent false reading; InnoDB uses insert intention locks to improve insertion concurrency Gap lock (gap lock) and key lock (next-key lock) only take effect at the level above RR, and will fail under RC

All right, that's all of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. If you have any questions, you can leave a message and exchange. Thank you for your support.

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.