Let’s talk about the various modes and types of locks in MYSQL

This article brings you relevant knowledge about the various types and modes of locks in mysql. I hope it will be helpful to you.

In daily development work, we need to deal with the database almost every day. As a SQL BOY who only knows CRUD, in addition to using mybatis-generator to automatically generate DAO layer code every day In addition, we almost don’t need to care about how to handle concurrent requests in the database, but suddenly one day the MYSQL database alerts and a deadlock occurs. We panic in our hearts and can’t help but wonder: Isn’t this just an ordinary query? Still locked up?

In order to avoid the panic expression being captured by the supervisor, we need to know in advance what locks are in the database.

In MySQL, locks are actually divided into two categories: lock type (lock_type) and lock mode (lock_mode).

The lock type describes the granularity of the lock, that is, where the lock is specifically added; and the lock mode describes what kind of lock is added, whether it is a read lock or a write lock. Lock modes are often used in conjunction with lock types.

According to the lock mode

Read lock

Read lock, also called shared lock/S lock/share locks .

Read lock is a lock created by a transaction (such as transaction A) when performing a read operation (such as reading a table or reading a row). Other transactions can read these data concurrently. (locked), but the data cannot be modified (unless the user holding the lock has released the lock).

After transaction A adds a read lock to the data, other transactions can still add read locks (shared) to it, but cannot add write locks.

Add read locks on records

InnoDB supports table locks and row locks, locks on rows (that is, records), and Instead of locking the record, lock the index corresponding to the record. If there is no index in the where condition, all records will be locked.

The explicit locking statement is:

Note: The read mentioned here refers to the current read, and snapshot read does not require locking. Ordinary select reads are generally snapshot reads, except for explicit locking statements such as select...lock in share mode, which will become current reads. Under the serializable level of the InnoDB engine, ordinary select reads will also become snapshot reads.

In addition, it should be noted that for the analysis of the row lock locking process, it is necessary to combine the analysis based on the transaction isolation level, whether to use an index (which type of index), whether the record exists and other factors to determine where to add it. Locked.

Several situations of adding read locks in the innodb engine

Normal queries will add S locks to records when the isolation level is serializable. But this also depends on the scenario: non-transactional reading (auto-commit) does not require locking under the Serializable isolation level;

Serializable isolation level: If the query condition is a unique index and a unique equal value query: Add S lock to the record; non-unique condition query (when the query scans multiple records): the gap in the record itself (need to specifically analyze the range of the gap), add S lock;

select ... in share mode, S lock will be added to the record, but the locking behavior will be different depending on the isolation level:

RC isolation level: S lock will be added to the record. RR/Serializable isolation level: If the query condition is a unique index and a unique equal value query: an S lock is added to the record; non-unique condition query (when the query scans multiple records): the gap between the records itself (Need to specifically analyze the range of the gap), add S lock;

Usually the insert operation is not locked, but if a duplicate key is detected when inserting or updating a record (or there is a duplicate that is marked for deletion) key), for ordinary insert/update, S lock will be added, and for SQL statements like replace into or insert ... on duplicate, X lock will be added.

insert ... select When inserting data, S lock will be added to the data scanned on the select table;

Foreign key check: When we delete a record on the parent table, we need to Check whether there are reference constraints. At this time, the corresponding records on the sub-table will be scanned and an S lock will be added.

Adding read locks on the table

Table locks are implemented by the MySQL server. Table locks can be used regardless of the storage engine. Generally, when executing a DDL statement, such as ALTER TABLE, the entire table will be locked. You can also explicitly lock a table when executing a SQL statement.

The explicit locking statement for the table is:

When using the MYISAM engine, usually we do not need to manually lock it, because the MYISAM engine will target us The sql statement is automatically locked, and the entire process does not require user intervention:

• Query statement (select): Read locks will be automatically added to the tables involved;

• Update statements (update, delete, insert): will automatically add write locks to the tables involved.

Write lock

Write lock, exclusive lock/X lock/exclusive locks. The blocking nature of write locks is much stricter than that of read locks. After a transaction adds a write lock to the data, other transactions can neither read nor change the data.

The same range as read locks and write locks can be added to records or tables.

Add a write lock on the record

Add a write lock on the record, the engine needs to use InnoDB.

Usually ordinary select statements will not lock (except when the isolation level is Serializable). If you want to add an exclusive lock during query, you need to use the following statement:

Add write lock during query:

Same as adding a read lock, a write lock is also added to the index.

Add write lock when updating:

Add write lock on the table

The statement to explicitly add a write lock to the table is:

When the engine selects myisam, the insert/update/delete statement will automatically add an exclusive lock to the table.

Read-write lock compatibility:

• The read lock is shared, it will not block other read locks, but it will block other write locks;

• Write lock is exclusive, it will block other read locks and write locks;

• Summary: reading and reading are not mutually exclusive, reading and writing are mutually exclusive, writing Write mutual exclusion

Intention lock

Intention lock is a table-level lock that does not conflict with row-level locks , indicating the type of lock (S lock or InnoDB supports multiple granularity locks, allowing the coexistence of row-level locks and table-level locks.

Intention locks are divided into:

Intention shared locks (IS locks): IS locks indicate that the current transaction intends to set shared locks on rows in the table

When the following statement is executed The IS lock will be acquired first, because this operation is acquiring the S lock: Acquire the S lock: select ... lock in share mode

Intention exclusive lock (IX lock): The IX lock indicates that the current transaction intention is in the table Set an exclusive lock on the row

When the following statement is executed, the IX lock will be acquired first, because this operation is acquiring the X lock: Acquiring the X lock: select... for update

Transaction to acquire Before obtaining the S lock and X lock on a certain table, the corresponding IS lock and IX lock must be obtained respectively.

What is the role of intention locks:

If another transaction attempts a shared lock or exclusive lock at the table level, it is subject to the table-level intention lock controlled by the first transaction. block. The second transaction does not have to check for individual page or row locks before locking the table, but only for intent locks on the table.

Example: Table test_user:

##Transaction A acquired an exclusive lock on a row but did not commit it;

Transaction B wants to obtain the table shared lock of the test_user table;

Because shared locks and exclusive locks are mutually exclusive, when transaction B attempts to add a shared lock to the test_user table, it must ensure:

• No other transaction currently holds an exclusive lock on the users table (table exclusive lock).

• No other transaction currently holds an exclusive lock (row exclusive lock) on any row in the users table.

In order to check whether the second condition is met, transaction B must check whether there is an exclusive lock on each row in the table while ensuring that there is no exclusive lock on the test_user table. Obviously this is a very inefficient approach, but with the intention lock, the situation is different:

Because transaction A has acquired two locks at this time: the intention exclusive lock and id on the users table Exclusive lock on 28 data rows.

Transaction B wants to obtain the shared lock of the test_user table:

Transaction B only needs to detect whether transaction A holds the intention exclusive lock of the test_user table, and then it can know that transaction A must hold the If there is an exclusive lock on certain data rows in the table, then transaction B's lock request for the test_users table will be excluded (blocked), so there is no need to detect whether there is an exclusive lock on each row of data in the table.

Transaction C also wants to obtain an exclusive lock on a row in the users table:

• Transaction C detects that transaction A holds an intention exclusive lock on the test_user table;

• Intention locks are not mutually exclusive, so transaction C obtained the intention exclusive lock of the test_user table;

• Because the data row with id is 31 There is no exclusive lock, and eventually transaction C successfully acquires the exclusive lock on the data row.

Intention locks are not mutually exclusive, but there is a certain degree of compatibility and mutual exclusion between intention locks and other table locks, as follows:

Compatibility and mutual exclusivity between intention locks:

# Mutual exclusivity between intention locks and ordinary exclusive/shared locks:

Auto-increasing lock

When we design the table structure, we usually set the primary key to self-increasing (think about why?).

In the InnoDB storage engine, a self-increasing counter is set for each self-increasing field. We can execute the following statement to get the current value of this counter:

#When we perform an insertion operation, the operation will be performed based on the current value of this self-increasing counter. 1 operation and assign it to a self-increasing column. This operation is called auto-inc Locking, which is an auto-increment lock. This lock actually uses a special table lock mechanism. If the insert operation occurs in a transaction, this The lock is released immediately after the insert operation is completed, rather than waiting for the transaction to commit.

According to the type of lock

Global lock

The so-called global lock actually locks the entire database instance.

There is a difference between a database instance and a database:

A database is a warehouse that stores data. Specifically in mysql, the database is actually a collection of data files (which is what we usually call database, for example, the statement to create a database is create database...).

Database instance refers to the application program that accesses the database. In Mysql, it is the mysqld process.

To understand simply, the database instance contains various databases you create.

If you add a global lock to the database instance, the entire library will be in a read-only state (this is very dangerous).

Generally speaking, the typical usage scenario of global locks is for full database backup, that is, to select all tables in the database. However, be aware that leaving the entire library in a read-only state will cause some serious problems:

• Add a global lock on the main library. During the lock period, no update operations can be performed. , many functions of the business are basically unavailable;

• Add a global lock on the slave database. During the lock period, master-slave synchronization cannot be performed, which will cause master-slave synchronization delay.

The locking statement of the global lock is:

The method of releasing the global lock is:

• Just disconnect the session that executes the global lock;

• Execute the unlock sql statement: unlock tables;

If you need a database For backup, you can use the official logical backup tool mysqldump.

Since we already have the dump tool, why do we need FTWRL? Consistent reading is good, but only if the engine supports this isolation level. For example, engines like MyISAM do not support transactions. At this time, we need to use the FTWRL command.

If there is reading or writing before FTWRL, FTWRL will wait for the reading and writing to be completed before executing it.

When FTWRL is executed, dirty page data must be flushed to disk. Because data consistency must be maintained, FTWRL is executed when all transactions are submitted.

The implementation of global lock still relies on metadata lock.

Metadata Lock

MetaData Lock, also called MDL lock, is used to protect metadata information at the system level. The lock cannot be actively controlled. In MySQL version 5.5, MDL locks were introduced, mainly to maintain metadata consistency under simultaneous operations of DDL and DML in a concurrent environment. For example, the following situation:

Isolation level: RR

If there is no protection of metadata lock, then transaction 2 can directly perform DDL operations, resulting in a transaction 1 error. The MDL lock was added in the MYSQL5.5 version to protect this situation from happening. Since transaction 1 starts the query, it obtains the metadata lock. The lock mode is MDL read lock. If transaction 2 wants to execute DDL, it needs to obtain the MDL write lock. Since the read and write locks are mutually exclusive, transaction 2 needs to wait for transaction 1 to be released. It can only be executed if the read lock is released.

• When adding, deleting, modifying, and checking (DML operations) records in the table, MDL read locks are automatically added;

• The structure of the table (DDL operation) When making modifications, the MDL write lock is automatically added.

The granularity of MDL locks

MDL locks are implemented at the Mysql server level, not in the storage engine plug-in realized in. According to the locking scope, MDL locks can be divided into the following categories:

MDL lock mode

##Page level lock

MySQL lock granularity is between row-level locks and table-level locks. Table-level locks are fast but have many conflicts. Row-level locks have few conflicts but are slow. So a compromised page level was adopted, locking a group of adjacent records at a time. Different storage engines support different locking mechanisms. According to different storage engines, the characteristics of locks in MySQL can be roughly summarized as follows:

Page-level lock is a unique locking level in MySQL, which is applied to the BDB engine. The concurrency is average. The characteristic of page-level locking is that the locking granularity is between row-level locking and table-level locking. Therefore, the resource overhead required to obtain the lock and the concurrent processing capability it can provide are also between the above two. between. In addition, page-level locking and row-level locking will cause deadlock.

Comparison of locking granularity sizes: table-level locks> page-level locks> row-level locks

Table-level locks

Table locks have been introduced above. Compared with the fine-grained locking of row locks, table locks lock the entire table. Since the entire table is locked, it is not as complicated as row locking, so locking is faster than row locking, and there will be no deadlock (because the transaction acquires the desired table lock at one time ), but table locks also have some problems: the lock range is too large, and when concurrency is relatively high, the probability of lock conflicts will increase, which will greatly reduce the concurrency performance.

Table lock locking method

When the engine selects MYISAM

The MYISAM engine only supports table locks and does not support rows Lock.

The statement to manually add table-level locks is as follows:

When using the MYISAM engine, usually we do not need to manually add locks, because the MYISAM engine will target us The sql statement is automatically locked, and the entire process does not require user intervention:

• Query statement (select): Read locks will be automatically added to the tables involved;

• Update statements (update, delete, insert): write locks will be automatically added to the tables involved

When the engine selects InnoDB

The InnoDB engine supports both row-level locks and table-level locks, and the default is row-level locks.

Manually lock the table of the InnoDB engine, and also use the lock table {tableName} read/write statement to add read/write locks.

In addition, innodb also supports a table-level lock: intention lock (already introduced above).

In general, the table-level locks of the InnoDB engine include five lock modes:

• LOCK_IS: Intentional read lock

• LOCK_IX: Table intention write lock

• LOCK_S: Table read lock

• LOCK_X: Table write lock

• LOCK_AUTO_INC: Self-increasing lock

Row-level lock

In the process of writing business code , the one we come into contact with the most is row-level locks (table-level locks are generally not recommended due to performance issues). Compared with table-level locks, row-level locks have obvious performance advantages:

• Few conflicts: There are only a few lock conflicts when accessing different records in multiple threads;

• The lock granularity is small: a single row can be locked for a long time without affecting other rows, so the concurrency is the highest;

But use row locks If you are not careful, it is very easy for deadlock to occur (table lock does not exist for deadlock), so when using row lock, you need to pay attention to the locking order and locking range.

InnoDB's row locks are implemented by locking index items, which means that row locks will only be used when querying records through the index. If the data is queried without the index, table locks will be used, and the performance will be greatly reduced. .

Need to remember: row locks are also called record locks, and record locks are added to the index.

• where condition specifies the primary key index: then lock the primary key index;

• wehre condition specifies the secondary index: record The lock will not only be added to this secondary index, but also to the clustered index corresponding to this secondary index;

• where condition If the index cannot be accessed: MySQL will give the entire Record locks are added to all data rows in the table, and the storage engine layer returns all records to the MySQL server for filtering.

Record lock: LOCK_REC_NOT_GAP (only lock records)

Record lock is the simplest row lock. For example, at the RR isolation level, when executing the select * from t_user where id = 1 for update statement, the record id = 1 (where id is the primary key) is actually locked (the lock is added to the clustered index).

Record locks are always added to the index. Even if a table does not have an index, the database will implicitly create an index. If the column specified in the WHERE condition is a secondary index, the record lock will not only be added to the secondary index, but also to the clustered index corresponding to the secondary index.

Note that if the SQL statement cannot use the index, it will use the main index to implement a full table scan. At this time, MySQL will add record locks to all data rows in the entire table.

If a WHERE condition cannot be quickly filtered by the index, the storage engine layer will lock all records and return them, and then the MySQL Server layer will filter them. When there is no index, it will not only consume a lot of lock resources and increase the overhead of the database, but also greatly reduce the concurrency performance of the database. Therefore, you must remember to use the index during the update operation (because the update operation will add X lock).

Several types of row-level locks:

Gap lock: LOCK_GAP (only lock gap)

Gap lock is A range lock. The lock is added to the free space that does not exist, or between two index records, or the first index record, or the space after the last index, used to indicate that only a range is locked (usually in isolation when performing range queries) level in RR or Serializable intervals).

Generally, GAP locks are used under the RR isolation level. The main purpose of using GAP lock is to prevent phantom reading. In the interval locked by GAP lock, data is not allowed to be inserted or updated.

The conditions for gap lock generation: the isolation level of innodb is Repeatable Read or Serializable.

Description of the scope of gap lock:

Isolation level: RR

Take the Student table as sample data, id as the primary key, stu_code is the student number and adds a normal index.

Gap lock area definition:

According to the search conditions, look to the left for the closest value A as the left interval, and to the right for the closest value B as the right interval. The gap lock is ( A, B)

The nearest value A cannot be found to the left, which is infinitesimal. As the left interval, the closest value B is found to the right as the right interval. The gap lock is (infinitesimal, B)

Find the nearest value A to the left, as the left interval, and find the nearest value B to the right, which is infinity, as the right interval, the gap lock is (A, infinity)

Interval (A,B) Example:

Transaction 1:

select * from student where stu_code = 4 for update

Transaction 2:

insert into student vaues(2, 2, 'A')；
insert into student values(4, 5, 'B');

According to the SQL statement analysis of transaction 1, the range of the gap lock is: stu_code = 4 The record exists, so the most recent index value in the left interval is stu_code = 3, and the most recent index value in the right interval is stu_code =7, so the gap range is: (3, 7), so the two insert statements of transaction 2, One is outside the range and the other is within the range. The one outside the range can be inserted, while the one within the range is blocked, so (2, 2, 'A') can be inserted successfully; (4, 5, 'B') is inserted blocked.

Interval (infinitely small, B) example:

Transaction 1:

select * from student where stu_code = 1 for update

Transaction 2:

insert into student vaues(2, 0, 'c')；
insert into student vaues(2, 2, 'r')；
insert into student vaues(5, 2, 'o')；

According to the SQL statement analysis of transaction 1, gap lock The range is: stu_code = 1 exists, there is no recent record on the left, so it is infinitesimal on the left, and the nearest index value on the right is stu_code = 3, so the gap lock range is: (infinite, 3). Therefore, the execution of the first and second insert sql statements of transaction 2 is blocked and is within the scope of the gap lock. The third insert sql statement can be executed successfully and is not within the gap lock range.

Interval (A, infinity) example:

Transaction 1:

select * from student where stu_code = 7 for update

Transaction 2:

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

According to the SQL statement analysis of transaction 1, gap lock The range is: stu_code = 7 exists, the nearest index value on the left is stu_code = 4, and there is no index value on the right, so the range of the gap lock is: (4, infinity), the first inset statement can be executed successfully , not within the gap range; the execution of the second insert statement is blocked and is within the gap lock range.

If the query statement is not recorded in the database, how to lock it?

The above is that the query is recorded. If the query statement is not recorded in the database, how to lock it? Let’s continue:

Transaction 1:

update student set stu_name = '000' where stu_code = 10

Transaction 2:

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

According to the above execution statement, the record cannot be found, and the nearest record is taken to the left ( 10, 7, 'Xiao Ming') as the left interval, that is, the range of the gap lock is: (7, infinity), the first insert statement is not within the interval range and can be executed successfully; the second insert execution statement is blocked within the interval , execution failed. If the where condition of transaction 1 is greater than 10, the nearest record value is also found to the left as the left interval, so the range of the gap lock is also: (7, infinity)

Summary: Conditions for gap lock generation

Under the RR/Serializable isolation level: Select...Where...For Update:

When only using a unique index query and locking only one record, InnoDB will use row locks.

When only a unique index query is used, but the search condition is a range search, or a unique search but the search result does not exist (trying to lock non-existent data), Next-Key Lock will occur.

When using ordinary index retrieval, no matter what kind of query it is, as long as it is locked, gap lock will be generated.

When using unique indexes and ordinary indexes at the same time, since the data rows are sorted first according to the ordinary index and then according to the unique index, gap locks will also occur.

Next-Key lock: LOCK_ORDINARY, also known as Next-Key Lock

Next-Key lock is a combination of record lock gap lock. Like gap locks, there is no Next-key lock under the RC isolation level (unless it is forcibly turned on by modifying the configuration), only the RR/Serializable isolation level has it.

MySQL InnoDB works under the repeatable read isolation level (RR), and will lock data rows using Next-Key Lock, which can effectively prevent the occurrence of phantom reads. Next-Key Lock is a combination of row lock and gap lock. When InnoDB scans the index record, it will first add a row lock (Record Lock) to the index record, and then add a gap lock (Gap Lock) to the gaps on both sides of the index record. After adding the gap lock, other transactions cannot modify or insert records in this gap.

When the queried index contains unique attributes (unique index, primary key index), the Innodb storage engine will optimize the next-key lock and reduce it to a record lock, that is, only lock the index itself instead of scope.

Insert intention lock: LOCK_INSERT_INTENSION

Insert intention lock, used when inserting records, is a special gap lock. This lock represents the intention of inserting. This lock will only occur when the insert statement is executed.

Assume that the values ​​of the index records are id = 1 and id = 5 (there are no records between 1 and 5). Separate transactions try to insert id = 2 and id = 3 respectively. After obtaining the inserted row Before the exclusive lock, each transaction used an insertion intention lock to lock the space between 1 and 5, but would not block each other. Because there will be no conflict between inserted intention locks.

Inserting intention locks will conflict with gap locks or Next-Key locks: the function of gap locks is to lock the interval to prevent other transactions from inserting data and causing phantom reads.

In the above scenario, assuming that transaction A has acquired the gap lock with id in the (1, 5) range in advance, then when transaction B tries to insert id = 2, it will first try to acquire the insertion intention lock, but Due to the conflict between the insertion intention lock and the gap lock, the insertion fails, thus avoiding the occurrence of phantom reads.

Conclusion

The locking mechanism of MYSQL is very complex. In actual development work, you need to be very careful about setting the isolation level, such as The RR level will have one more gap lock than the RC level, which may cause serious performance problems. This article briefly introduces the classification of MYSQL locks from the perspective of lock mode and lock scope. I hope that in the process of database development, we can carefully analyze and study whether our SQL statements are reasonable (especially we need to pay attention to whether deadlocks and other issues will occur). )!

Recommended learning: mysql video tutorial

The above is the detailed content of Let’s talk about the various modes and types of locks in MYSQL. For more information, please follow other related articles on the PHP Chinese website!