Insert into on Duplicate key Update statement was used to remove duplicate while working from home during the outbreak, but deadlocks were found during the test:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Copy the codeDue to the urgent development task, I only avoided it temporarily, but I don’t know much about the cause and related principles of triggering deadlocks. Therefore, I have been looking up relevant materials these days and summarized a series of articles for your reference. This article introduces the principle of MySQL lock and the different modes or types of lock basic knowledge. The following sections will explain the locking of common statements and analyze deadlock causes using MySQL deadlock logs.
Since this article covers a lot of the basics of MySQL, you can read the corresponding chapters in my previous MySQL series, “MySQL Secrets.”
Table and row locks
Table locks are used to lock a whole table, usually for DDL processing. A row lock locks a row or rows, or the gaps between rows.
Table locks are implemented by MySQL Server, while row locks are implemented by storage engines. Different engines implement different locks. InnoDB supports row locks in MySQL’s common engine, while MyISAM can only use table locks provided by MySQL Server.
Table locks
TABLE locking is implemented by MySQL Server. It is generally used to lock the entire TABLE when executing DDL statements, such as ALTER TABLE operations. You can also explicitly specify that a table is locked when executing an SQL statement.
mysql> lock table user read(write); Read locks and write locks
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 100; # success
mysql> select * from role where id = 100; Failed to obtain the read table lock for this role
mysql> update user set name = 'Tom' where id = 100; User's write table lock was not obtained in advance
mysql> unlock tables; Release table lock
Query OK, 0 rows affected (0.00 sec)
Copy the codeTable locks use the one-time lock technique, that is, you use the lock command at the beginning of the session to lock all tables that need to be used later. Until the table is released, only these tables can be accessed and other tables cannot be accessed.
In addition to using UNLOCK Tables to display the release of a lock, executing the Lock TABLE statement when a session holds another table lock releases the lock previously held by the session. When a session holds other table locks and executes start transaction or BEGIN to start a transaction, the previously held locks are also released.
Row locks
Different storage engines have different implementation of row locking. If not specified later, row locking refers to InnoDB’s implementation of row locking.
Before understanding the locking principle of InnoDB, we need to have a certain understanding of its storage structure. InnoDB is a clustered index, that is, leaves of a B+ tree store both primary key indexes and rows. The leaf node of InnoDB secondary index stores the primary key value, so when querying data through secondary index, it is necessary to take the corresponding primary key to cluster index for query again. For more information about InnoDB and MyISAM indexes, see Mysql Exploration (1):B+Tree indexes.
The following two SQL execution as an example, explain InnoDB for a single row of data lock principle.
update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';
Copy the codeSelect * from primary key where id = 49; select * from primary key where id = 49; Select * from InnoDB where id = 49; select * from InnoDB where id = 49; select * from InnoDB where id = 49; select * from InnoDB where id = 49;
That is, a primary key index requires a lock, and a secondary key index requires a lock.
If the update operation involves more than one row, as in the following SQL execution scenario.
update user set age = 10 where id > 49;
Copy the codeThe SQL execution process is shown in the following figure. MySQL Server reads the first record that meets the WHERE condition. InnoDB engine then returns the first record and locks it. MySQL Server then issues an UPDATE request to UPDATE the row record. Once a record operation is completed, the next record is read until no matching record is found.
The lock release in this scenario is complicated, and there are many optimization methods. I haven’t understood this yet, please leave a comment below to explain.
The following describes the mode and type of lock in InnoDB. The type of lock refers to the granularity of the lock or where the lock is placed. The lock mode describes the compatibility of locks, that is, what locks are added, such as write or read locks.
Innodb-lock is the technical documentation of MySQL. If you are interested in innodb-lock, you can read the original text directly. The original address is at the end of the article.
Row lock mode
The lock modes are: read intention lock, freehand lock, read lock, write lock and auto_INC, we look at them in turn.
Read-write lock
A read lock, also known as a Share lock (S-lock for short), is a record for which a read lock is added. All transactions can be read but cannot be modified. Multiple transactions can also add a read lock to the record.
A write lock is also called an Exclusive lock. After an Exclusive lock is added to a record, only the transactions that own the lock can read and modify the records. Only one transaction can be added to the write lock at a time.
Read-write intent lock
Because table lock and row lock although the lock scope is different, but will conflict with each other. So when you want to add a table lock, you must first go through all the records in the table to determine whether there is an exclusive lock. MySQL has introduced intentional locks to detect conflicts between table locks and row locks.
Intent lock IS also table level lock, also can be divided into read intent lock (IS lock) and freehand direction lock (IX lock). When a party wants to place a read or write lock on a record, it first places an intent lock on the table. It is easy to determine if there is a record lock in the table, just look at the table if there is an intent lock on the line.
Intent locks do not conflict with each other, nor with AUTO_INC table locks. They only block table read locks or table write locks. Intent locks do not conflict with row locks, but row locks only.
On the lock
An AUTO_INC lock, also called an AUTO_INCREMENT lock (commonly abbreviated as AI lock), is a table lock that occurs when a table has an AUTO_INCREMENT column. When an insert table has an AUTO_INC table lock, the database automatically generates an AUTO_INC table lock for the table, blocking the insert operations of other transactions, and ensuring that the generated AUTO_INC column is unique. AUTO_INC locks have the following features:
- AUTO_INC locks are incompatible, which means that only one autoincrement lock is allowed on the same table at the same time.
- Self-increment is +1 once allocated and does not go back if the transaction is rolled back, so self-increment may be interrupted.
MySQL has introduced an optional lightweight lock (MUtex) mechanism to replace the AUTO_INC lock since version 5.1.22. In order to improve concurrency, MySQL has introduced an optional lightweight lock (MUtex) mechanism to replace the AUTO_INC lock. You can use the innodb_autoinc_lock_mode parameter to flexibly control the concurrency policy when allocating auto-increment. For details, see MySQL AUTO_INCREMENT Handling in InnoDB, linked at the end of this article.
Compatibility matrix for different mode locks
Below is the compatibility matrix between the individual table locks.
To sum up the following points:
- Intent locks do not conflict with each other;
- S locks are compatible with S/IS locks but conflict with other locks.
- [Fixed] X lock conflicts with all other locks.
- [Fixed] AI locks are only compatible with intent locks
The type of row lock
According to the granularity of the Lock can be subdivided into table Lock and row Lock, row Lock according to the different scene can be further subdivided, Next Key Lock, Gap Lock Gap Lock, Record Lock and insert intention Gap Lock.
For example, a record Lock only locks the corresponding record, a gap Lock locks the interval between the record and the record, and a next-key Lock locks the record and the interval before the record. The locking ranges of different types of locks are shown in the following figure.
Let’s look at the different types of locks in turn.
Record locks
Record locks are the simplest row locks, and there’s nothing to say. InnoDB lock (id = 49 or name = ‘Tom’)
When an index cannot be used by an SQL statement, a full table scan is performed. In this case, MySQL locks all rows of the entire table and filters them by the MySQL Server layer. However, when filtering at the MySQL Server layer, if the WHERE condition is not met, the lock of the corresponding record is released. This ensures that only the records that meet the criteria will end up holding the lock, but the lock operation for each record cannot be omitted.
Therefore, the update operation must be performed according to the index. Without the index, it will not only consume a large number of lock resources, increase the overhead of the database, but also greatly reduce the concurrency performance of the database.
Clearance lock
If id = 49 does not exist, will the SQL statement still be locked? The answer is probably yes, depending on the isolation level of the database. In this case, no locks are added at the RC isolation level, and a gap lock is added between indexes before and after ID = 49 at the RR isolation level.
A gap lock is a lock placed between two indexes, either before the first index or after the last index. This gap can span one index record, multiple index records, or even empty. Using a gap lock prevents other transactions from inserting or modifying records in this range, ensuring that the records in this range will not change after two reads, thus preventing phantom reads.
It is worth noting that there is no conflict between gap locks and gap locks. The only function of gap locks is to prevent the insertion of other transactions, so there is no difference between adding gap S locks and adding gap X locks.
Next, the Key lock
A next-key lock is a combination of a record lock and a gap lock. It refers to a lock placed on a record and the gap in front of that record. Suppose an index contains the values 15, 18, 20, 30,49,50. The possible next-key locks are as follows:
(- up, 15], (15, 18], (18, 20], (20, 30], (30, 49], (49, 50], (50 + up)Copy the codeThe next-key lock is typically represented by a left open, right closed interval, where parentheses indicate exclusion of the record and square brackets indicate inclusion of the record. The first four are next-key locks, and the last one is a gap lock. Like gap locks, there is no next-key lock at the RC isolation level, only at the RR isolation level. As in the previous example, if the ID is not a primary key, but a secondary index, and not a unique index, then the SQL at RR isolation level will add the following next-key lock (30, 49) (49, 50)
Inserting a record with id = 31 will block. If id = 49 is not a unique index, there may be multiple records. In order to prevent the insertion of another record with ID = 49.
Insert intent lock
INSERT intent lock is a special GAP lock (II GAP) that indicates INSERT intent and is only available during INSERT. Note that this lock is also called an intent lock, but it is a completely different concept from the table level intent lock introduced above.
There is no conflict between insert intent locks and insert intent locks, so multiple transactions can insert records of different indexes simultaneously in the same gap. Insert intent lock between id = 30 and id = 49; insert intent lock between id = 30 and id = 50; insert intent lock between id = 30 and id = 49; insert intent lock between id = 32 and id = 33; insert intent lock between id = 30 and id = 50;
INSERT intent locks only conflict with a gap lock or next-key lock. As mentioned above, the only purpose of a gap lock is to prevent phantom reads from other transaction inserts. INSERT intent locks conflict with a gap lock, preventing INSERT operations.
Compatibility matrices for different types of locks
The following figure shows the compatibility of different types of locks.
Where the first row represents the existing lock and the first column represents the lock to be added. Insert intention lock is special, so we first make a summary of insert intention lock, as follows:
- Inserting an intent lock does not affect any other locks added to other transactions. In other words, one transaction has acquired the intent lock for insertion, which has no effect on other transactions.
- Insert intent locks conflict with gap locks and next-key locks. That is, a transaction attempting to acquire an insert intent lock will block if another transaction has a gap lock or a next-key lock.
The rules for other types of locks are simpler:
- [Fixed] Gap locks do not conflict with other locks (excluding insert intent locks);
- A record lock conflicts with a record lock, a next-key lock conflicts with a next-key lock, and a record lock conflicts with a next-key lock.
Afterword.
Next article we will look at the specific SQL lock analysis and deadlock log analysis, please pay attention to partners and pay attention to, if you have questions can leave a message below the article.
Personal blog, welcome to play
reference
- Dev.mysql.com/doc/refman/…
- Dev.mysql.com/doc/refman/…
- www.aneasystone.com/archives/20…