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.
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:
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.
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: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 MYISAMThe MYISAM engine only supports table locks and does not support rows Lock. The statement to manually add table-level locks is as follows:
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: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: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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
