In daily development work, we need to deal with the database almost every day. As a SQL BOY who can only CRUD, we hardly need to care how to deal with concurrent requests in the database except to automatically generate DAO layer code with Mybatis -Generator every day. But suddenly one day MYSQL database alarm, there is a deadlock, our inner panic, can not help asking: this is not a common query, why also locked?

To avoid being caught in a panic, we need to know what locks are in the database in advance.

In MySQL, locks are actually divided into two types: 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; The lock mode describes what lock is added, whether it is a read lock or a write lock. Lock mode is usually used in conjunction with lock type.

According to the lock mode

Read lock

Read locks, also known as shared locks /S locks /share locks. A read lock is A lock created when A transaction (such as transaction A) performs A read operation (such as reading A table or reading A row). Other transactions can concurrently read the data (which is locked), but cannot modify the data (unless the user who holds the lock has released the lock). After transaction A has read locks on data, other transactions can still add read locks (shared) to it, but not write locks.

Add a read lock to the record

InnoDB supports table locking and row locking. A lock on a row (a record) is not a lock on the record, but on the index corresponding to the record. If there is no index in the WHERE condition, all records are locked.

The explicit lock statement is:Note: The read here refers to the current read, snapshot read is not locked. Normal SELECT reads are generally snapshot reads, except select… Explicit locks such as Lock in Share mode become current reads, and normal SELECT reads become snapshot reads at the Serializable level of InnoDB.

In addition, it should be noted that the lock process analysis of row locks should be based on the transaction isolation level, whether the index is used (which type of index), whether the record exists and other factors to determine where the lock is added.

Several cases of add lock in InnoDB engine ####

  1. A normal query with an isolation level of SerialIZABLE adds an S-lock to the record. But it also depends on the scenario: non-transactional read (auto-commit) at the Serializable isolation level does not require locking; Serializable isolation level: If the query condition is a unique index and a unique equivalent query, an S lock is added to the record. Non-unique condition query (when the query will scan multiple records) : record itself + record gap (need to analyze the gap range in detail), add S lock;
  2. The select… In share mode, S locks are applied to records, but the behavior varies according to the isolation level: RC Isolation level: S locks are applied to records. RR/Serializable Isolation level: If the query condition is a unique index and a unique equivalent query, an S lock is added to the record. Non-unique condition query (when the query will scan multiple records) : record itself + record gap (need to analyze the gap range in detail), add S lock;
  3. Normally insert operations are not locked, but if a duplicate key is detected during insert or update (or if there is a duplicate key that has been marked for deletion), for normal insert/update operations, an S lock is added. For things like replace into or insert… SQL statements like on Duplicate add an X lock.
  4. Insert… Select * from table (S); select * from table (S);
  5. Foreign key check: When we delete a record from the parent table, we need to check whether there is a reference constraint. At this time, we scan the corresponding records on the child table and attach the S lock.

Add a read lock to the table

Table locks are implemented by MySQL server and can be used regardless of storage engine. When executing DDL statements, such as ALTER TABLE, the entire TABLE is locked. You can also explicitly lock a table when executing an SQL statement. SQL > alter table lock;When using the MYISAM engine, we usually do not need to manually lock because the MYISAM engine automatically locks our SQL statements without user intervention:

  1. Query statement (SELECT) : will automatically add read lock to the table involved;
  2. Update statements (UPDATE, DELETE, INSERT) : Write locks are automatically added to the tables involved.

Write lock

Write locks, exclusive locks /X locks /exclusive locks Write locks are much more restrictive than read locks. Once a transaction adds a write lock to the data, other transactions cannot read or change the data. Write locks can be applied to either records or tables in the same scope as read locks.

Put a write lock on the record

To add write locks to records, the engine needs to use InnoDB. In general, normal SELECT statements do not lock (except for Serializable isolation level). To add an exclusive lock to a query, use the following statement:Like read locks, write locks are added to indexes. Update with write lock:

Add write locks to tables

Alter table write lock; alter table write lock;When the engine selects myISAM, the insert/update/ DELETE statement automatically assigns an exclusive lock to the table.

Read/write lock compatibility:

  1. Read locks are shared. They do not block other read locks, but block other write locks.
  2. Write locks are exclusive and block other read and write locks;
  3. Conclusion: Reading is not mutually exclusive, reading is mutually exclusive, writing is mutually exclusive

intention The lock

An intentional lock is a table-level lock that does not conflict with row-level locks. It represents the type of lock (S lock or X lock) required for a record in a table (essentially, it tells you that row locks (the type of row locks) already exist in the table. InnoDB supports multiple granularity locks, allowing row-level locks and table-level locks to coexist. Intent locks are divided into:

  1. Intentional Shared lock (IS lock) : An IS lock indicates the current transaction intent to set a shared lock on a row in a table

    The following statement obtains the IS lock first, because this operation obtains the S lock: obtain the S lock: select… lock in share mode

  2. Intent exclusive lock (IX lock) : IX lock indicates that the current transaction intends to set an exclusive lock on a row in the table

    The following statement first acquires the IX lock because the operation acquires the X lock: get the X lock: select… for update

Before a transaction can acquire S and X locks on a table, it must first acquire the corresponding IS and IX locks, respectively. What does an intent lock do: If another transaction attempts a shared or exclusive lock at the table level, it is blocked by the table level intent lock controlled by the first transaction. 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 acquires an exclusive lock for A row and does not commit it;

Transaction B wants to acquire the table share lock for test_USER;SQL > alter table TEST_USER; SQL > alter table TEST_USER; SQL > alter table TEST_USER;

  1. No other transaction currently holds the exclusive lock on the Users table (table exclusive lock).
  2. No other transaction currently holds an exclusive lock (row exclusive lock) for any row in the Users table.

To test for the second condition, transaction B must test for the existence of exclusive locks on each row in the test_USER table, while ensuring that no exclusive locks exist on the table. This is obviously inefficient, but with intent locks, the situation is different: transaction A now acquires two locks: the intent exclusive lock on the Users table and the exclusive lock on the data row with ID 28. Transaction B wants to acquire a shared lock on test_user: Transaction A must hold an exclusive lock on some rows in the test_USERS table. Then transaction B will reject (block) the lock request on test_USERS table. This eliminates the need to detect the existence of an exclusive lock for each row in the table. Transaction C also wants to acquire the exclusive lock for a row in the Users table:

  1. Transaction C detects that transaction A holds an intent exclusive lock on test_USER;
  2. Intent locks are not mutually exclusive, so transaction C gets the intent exclusive locks for test_USER;
  3. Because there is no exclusive lock on the row with id 31, transaction C successfully obtains the exclusive lock on the row.

Intent locks are not mutually exclusive with intent locks, but there are certain compatible mutexes between intent locks and other table locks, as follows:Intent locks are mutually exclusive with normal exclusive/shared locks:

On the lock

When designing table structures, we usually set primary keys to auto-grow (think about why?). . In the InnoDB storage engine, a self-growing counter is set for each self-growing field. We can execute the following statement to get the current value of this counter:When we perform an insert operation, the +1 operation will be performed according to the current value of the self-growing counter and the self-growing column will be assigned. This operation is called auto-inc Locking, or self-growing lock. This Locking actually adopts a special table Locking mechanism, if the insert operation occurs in a transaction, This lock is released immediately after the INSERT operation completes, rather than waiting for the transaction to commit.

According to the type of lock

globalThe lock

Global locking is actually locking the entire database instance. Database instances are different from databases:

  1. A database is a collection of data files that store data in the database. In mysql, a database is a collection of data files. .
  2. A database instance is an application that accesses a database. In Mysql, it is the mysqld process.
  3. To put it simply, a database instance contains the various databases you create.

Placing a global lock on a database instance can cause the entire library to be read-only (which can be very dangerous). In general, a typical use scenario for global locking is for full database backup, where all tables in a database are selected. Be aware, however, that making the entire library read-only can cause some serious problems:

  1. A global lock is placed on the master library. During the lock, no update operations can be performed and many functions of the business are basically unavailable.
  2. When a global lock is placed on a slave library, master/slave synchronization cannot be performed during the lock, resulting in a delay in master/slave synchronization.

The global lock statement is:The global lock can be unlocked by:

  1. Disconnect the session that performs the global lock.
  2. SQL statement: UNLOCK tables;

If you need to backup a database, you can use mysqldump, the official logical backup tool. Why do you need FTWRL when you already have the dump tool? Consistent reads are fine, but only if the engine supports this isolation level. For example, an engine like MyISAM does not support transactions. At this point, we need to use the FTWRL command. FTWRL waits for read/write operations to complete before executing FTWRL. When FTWRL is executed, the dirty page data is flushed to disk. To maintain data consistency, FTWRL is executed when all transactions have been committed. The implementation of global locking depends on metadata locking.

metadata* * * * the lock

MetaData locks, also known as MDL locks, are used to protect MetaData information. System-level locks cannot be actively controlled. In MySQL5.5, MDL locks were introduced to maintain metadata consistency in concurrent DDL and DML operations. For example: Isolation level: RRWithout the protection of metadata locks, transaction 2 can directly perform DDL operations, causing transaction 1 to fail. MYSQL5.5 when addedMDLLocks are meant to protect against this happening. Because transaction 1 enabled the query, the metadata lock is obtained, and the mode of the lock is MDL read lock, which is required for transaction 2 to perform DDLMDLWrite lock, because the read and write locks are mutually exclusive, transaction 2 needs to wait for transaction 1 to release the read lock.

  1. MDL read lock is automatically added when the records in the table are added, deleted, or checked (DML operation).
  2. MDL write lock is automatically added when the table structure (DDL operation) is changed.

Granularity of MDL locks

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

MDL lock mode

Page levelThe lock

In MySQL, a lock whose granularity is intermediate between row-level and table-level locks. Table level locking is fast but has many conflicts, while row level locking is slow but has few conflicts. So a compromise page level is taken, locking adjacent sets of records at a time. Different storage engines support different locking mechanisms. According to different storage engines, the characteristics of locks in MySQL can be summarized as follows:Page-level locking is a unique locking level in MySQL. It is applied to BDB engine and has a moderate concurrency. The granularity of page-level locking is between row-level locking and table-level locking, so the resource cost required to obtain the lock and the concurrency capability provided are also between the above two levels. In addition, page-level locking, like row-level locking, is deadlocked. Comparison of lock granularity: table lock > page lock > row lock

* * table level lock

Table locks are used to lock the entire table, as opposed to fine-grained row locks. Table locking is not as complicated as row locking, so it is faster than row locking and there is no deadlock (because the transaction acquires the desired table lock at once), but there are some problems with table locking: If the scope of lock is too large, the conflict probability of lock grab will become high in the case of high concurrency, so the concurrency performance will be greatly compromised.

Table lock locking mode

Engine select MYISAM

MYISAM engine only supports table locks, not row locks. Add table locks manually as follows:When using the MYISAM engine, we usually do not need to manually lock because the MYISAM engine automatically locks our SQL statements without user intervention:

  1. Query statement (SELECT) : will automatically add read lock to the table involved;
  2. Update statements (UPDATE, DELETE, INSERT) : Write locks are automatically added to the tables involved

InnoDB engine selection

InnoDB engine supports both row-level locking and table-level locking. The default is row-level locking.

Lock InnoDB engine tables manually, also use lock table {tableName} read/write statement to add read/write locks. In addition, InnoDB also supports a table-level lock: intentional lock (described above). In general, the InnoDB engine contains five table locking modes:

  1. LOCK_IS: ideographic read lock
  2. LOCK_IX: Ideographic write lock
  3. LOCK_S: table read lock
  4. LOCK_X: table write lock
  5. LOCK_AUTO_INC: automatically adds a lock

Row-level locks**

Row-level locking (table level locking is generally not recommended due to performance issues) is one of the most common things we encounter when writing business code. Compared with table level locks, row level locks have obvious performance advantages:

  1. Low conflicts: there are only a few locking conflicts when accessing different records in multiple threads;
  2. Small lock granularity: a single row can be locked for a long time without affecting other rows, so the concurrency is the highest;

However, when using row lock, once a little attention is not paid, it is very easy to appear deadlock (table lock does not exist deadlock phenomenon), so the use of row lock need to pay attention to the order of lock and lock scope. InnoDB’s row locking is implemented by locking index entries. This means that row locking is used only when records are queried through the index. If data is queried without the index, table locking is used and performance deteriorates. Remember: row locks are also called record locks, and record locks are added to indexes.

  1. Select * from primary key index where ();
  2. The wehre condition specifies the secondary index: the record lock is not only applied to the secondary index, but also to the cluster index corresponding to the secondary index;
  3. The storage engine layer will return all the records to the MySQL server for filtering.

Record lock: LOCK_REC_NOT_GAP (lock records only)

Record locks are the simplest row locks. For example, in RR isolation, the select * from T_user where ID = 1 for UPDATE statement actually locks the record id = 1 (where ID is the primary key) (lock on the cluster index). A record lock is always placed on an index, and the database implicitly creates an index even if a table is not indexed. If the column specified in the WHERE condition is a secondary index, the record lock is not only applied to the secondary index, but also to the cluster index corresponding to the secondary index. Note that if the SQL statement cannot use the index, it will use the primary index to perform a full table scan. In this case, MySQL will lock all rows of the entire table. If a WHERE condition cannot be quickly filtered by index, the storage engine layer locks all records back and the MySQL Server layer filters them. When there is no index, it will not only consume a lot of lock resources, increase the overhead of the database, but also greatly reduce the concurrency performance of the database. Therefore, the update operation must remember to follow the index (because the update operation will add X lock). There are several types of row-level locks:

– #### gap lock: LOCK_GAP (only lock gap)

Gap lock is a kind of interval lock. A lock is applied to nonexistent free space, or the space between two index records, or the space after the first index record, or the last index, to indicate that only a range is locked (typically when a range query is performed and the isolation level is RR or Serializable). Generally, GAP locks are used in RR isolation. The GAP lock is mainly used to prevent phantom reading. It is not allowed to insert or update data in the interval locked by the GAP lock. The isolation level of InnoDB is Repeatable Read or Serializable.

Clearance lock scope description:

Isolation level: RRUse Student as the sample data, id as the primary key, STU_code as the Student number, add the normal index. Clearance lock area definition: **

  1. According to the retrieval conditions, search for the nearest value A to the left as the left interval, search for the nearest value B to the right as the right interval, and the gap lock is (A, B).
  2. If the nearest value A cannot be found to the left, it is also an infinitesimal, which is used as the left interval. If the nearest value B is found to the right, the gap lock is (infinitesimal, B).
  3. To the left, find the nearest value, A, as the left interval, to the right, find the nearest value, B, which is infinite, as the right interval, the gap lock is (A, infinite).

Interval (A,B) example:

Select * from student where stu_code = 4 for update 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 gap lock is: STU_code = 4 Records exist, so the most recent index value of the left interval is stu_code = 3, and the most recent index value of the right interval is STU_code =7, so the gap range is: Insert (2, 2, ‘A’); insert (2, 2, ‘A’); insert (2, 2, ‘A’); (4,5, ‘B’) insert block.

Interval (infinitesimal,B) Example:

Insert into student vaues(2, 0, ‘c’); select * from student where stu_code = 1; Insert into student vaues(2, 2, ‘r’); Insert into student vaues(5, 2, ‘o’);

According to analysis of transaction 1 SQL statements, the clearance lock range is: stu_code = 1 exists, the left no record recently, so it is on the left side of the infinitesimal, on the right side of the index value of recent stu_code = 3, so the clearance for lock range: (infinitesimal, 3). So the first and second INSERT SQL statement execution of transaction 2 is blocked, within the range of a gap lock. The third INSERT SQL statement can execute successfully, outside of the gap lock range.

Interval (A, infinity) example:

Select * from student where stu_code = 7 for update 2: insert into student vaues(2, 2, ‘m’); Insert into student vaues(20, 22, ‘j’);

Stu_code = 4; stu_code = 4; stu_code = 4; stu_code = 4; The second INSERT statement execution was blocked within a gap lock.

If the query has no record in the database, how do you lock it?

The above is the query is recorded, if the query statement in the database does not record, then how to lock? Let’s continue:

Update student set stu_name = ‘000’ where stu_code = 10 insert into student vaues(2, 2, ‘m’); Insert into student vaues(20, 22, ‘j’);

Insert statement (10,7, ‘xiaoming’); insert statement (10,7, ‘xiaoming’); The second INSERT execution statement was blocked in the interval and failed. If transaction 1’s where condition is greater than 10, the value of the most recent record is also used as the left interval, so the range of gap lock is also :(7, infinite)

Summary: conditions for clearance lock generation

RR/Serializable Isolation level: Select… Where… For Update:

  1. InnoDB uses row locks when only a single index query is used and only one record is locked.
  2. A next-key Lock occurs when a query is performed using only a unique index, but the search condition is a range search, or when a unique search is performed and the search result does not exist (an attempt is made to Lock non-existent data).
  3. A gap lock occurs whenever a query is locked using a normal index.
  4. When both unique and normal indexes are used, the rows are sorted first by the normal index and then by the unique index, resulting in gap locks.

Next Key Lock: LOCK_ORDINARY, also known as next-key Lock

Next-key lock is a combination of Record lock and Gap lock. As with gap locks, there is no next-key lock at the RC isolation level (unless forcibly enabled by a configuration change), and only at the RR/Serializable isolation level. MySQL InnoDB works at a repeatable read isolation level (RR) and locks rows in a next-key Lock to prevent phantom reads. Next Key Lock is a combination of row Lock and Gap Lock. When InnoDB scans an index Record, it first places a Record Lock on the index Record, and then places a Gap Lock on both sides of the index Record. With a gap lock, other transactions cannot modify or insert records in this gap. Innodb storage engine optimizes next-key lock to record lock when the query index has unique attributes (unique index, primary key index), that is, only the index itself is locked, not the range.

Insert intent lock: LOCK_INSERT_INTENSION

Insert intent lock, used when inserting records, is a special gap lock. This lock represents the intention to insert and is only available during insert statements. Given that the values of indexed records are ID = 1 and ID = 5 (there are no records between 1 and 5), separate transactions attempt to insert id = 2 and ID = 3, respectively. Each transaction locks the space between 1 and 5 with insert intent until it obtains an exclusive lock on the inserted row, but does not block each other. There is no conflict between intent locks. An intent lock conflicts with a gap lock or next-key lock: the purpose of a gap lock is to lock the interval and prevent phantom reads from being inserted by other transactions. In the above scenario, it is assumed that transaction A has obtained the gap lock whose id is in the interval of (1,5) in advance. Then, when transaction B tries to insert id = 2, it will first try to obtain the insert intention lock. However, due to the conflict between the insert intention lock and the gap lock, the insert fails, thus avoiding phantom read generation.

conclusion

The lock mechanism of MYSQL is very complex. In actual development work, the isolation level should be set carefully. For example, RR level may have one more gap lock than RC level, which may cause serious performance problems. This paper introduces the classification of MYSQL lock from the lock mode and lock scope. We hope that in the process of database-oriented development, we can carefully analyze and study whether our SQL statement is reasonable (especially need to pay attention to whether there will be deadlock and other problems)!