1. An overview of the
The reason why the previous chapter introduces so many knowledge points and examples of locks, in fact, the ultimate purpose is to troubleshoot and solve the problem of deadlocks. Here we review and supplement the knowledge of locks learned before, and then demonstrate how to troubleshoot and solve deadlocks through examples.
2. Prepare
● Database transaction isolation level
SHOW VARIABLES LIKE 'transaction_isolation%';
Copy the code
MYSQL transaction isolation level is repeatable by default. (For those of you who are not familiar with transaction isolation levels, please refer to this article.) ● Automatically commit transactions to close
SET AUTOCOMMIT=0;
Copy the codeTransaction auto-commit configuration: 0. Transaction non-auto-commit, 1. Transaction auto-commit ● Create a demo membership table
CREATE TABLE goods.members (' ID' int NOT NULL AUTO_INCREMENT COMMENT 'MemberName' varchar(50) CHARACTER SET Utf8mb4 COLLATE UTF8MB4_0900_AI_CI NOT NULL COMMENT 'member name ',' Tel 'VARCHar (20) CHARACTER SET UTf8MB4 COLLATE Utf8mb4_0900_ai_ci NOT NULL COMMENT 'mobile phone ',PRIMARY KEY (' ID'));Copy the code● create a non-clustered index on the MemberName MemberName field
ALTER TABLE goods.members ADD INDEX IX_MemberName(MemberName);
SHOW INDEX FROM goods.members;
Copy the code
● Insert four data to member table, convenient gap lock with record lock example demonstration
INSERT INTO goods.members (MemberName,Tel) VALUES ('A','110'),('B','120'),('C','130'),('D','140');
Copy the code
SELECT * FROM goods.members;
Copy the code
Ok, the preliminary conditions are ready, before the demonstration, let’s review and supplement the knowledge of locking.
3. Review and supplement lock knowledge
3.1 Introduction to locks
The following is based on the above diagram to review and supplement the knowledge points studied before the lock.
3.2 Optimistic locks and pessimistic locks
Pessimistic lock and optimistic lock are two common design ideas of resource concurrent lock, and also a very basic concept in concurrent programming. Pessimistic locks are characterized by acquiring the Lock first and then carrying out business operations. That is, Pessimistic locks assume that the acquisition of the Lock is likely to fail. Therefore, we need to ensure the Lock is successfully acquired before carrying out business operations. The common term “one lock, two checks, and three updates” refers to the use of pessimistic locks. In general, pessimistic locking on a database requires support from the database itself, via the usual select… The for Update operation implements pessimistic locking. When the database performs a SELECT for update, it acquires a row lock on the selected row. Therefore, any other concurrent select for update attempts to select the same row will be rejected (waiting for the row lock to be released), thus obtaining the lock effect. Row locks obtained by select for UPDATE are automatically released at the end of the current transaction and therefore must be used in a transaction. Optimistic Lock is characterized by business operations first, not to pick up the Lock until absolutely necessary. That is, “optimistic” people think that the lock is likely to be successful, so it is good to take the lock at the last step of the business operation that needs to actually update the data. The implementation of optimistic locking on the database is completely logical and does not require special support from the database. A common practice is to add a version number, or timestamp, to the data that needs to be locked. For example, UPDATE SET data = new_data, version = new_version WHERE version = old_version;
3.3 Shared locks and Exclusive Locks
InnoDB storage engine has two main types of row locks: **● Shared locks (S locks) : ** Allows locking transactions to read rows. ** Exclusive lock (X lock) : ** allows locking transactions to update or delete rows. If transaction T1 holds S lock on R row, transaction T2 will do the following when requesting R row: ◎T2 will not be allowed to request X lock, wait for T1 to release S lock. = T2 must wait for T1 to release the X lock before it can operate on row R, because the S lock is incompatible with the X lock.
3.4 intention to lock
**● Intended Shared lock (IS lock) : ** Shared lock that allows transactions to obtain table rows. ** * Intentional exclusive lock (IX lock) : ** Allows transactions to acquire exclusive locks for table rows. If transaction T1 wants to change the row R on a table, transaction T2 needs to change the row R on a table by adding a lock IX. Similarly, if transaction T1 imposes an IS lock on a table, transaction T2 imposes an IX lock on a row in table R when it wants to change the row in table R. InnoDB storage engine lock compatibility is as follows:
3.5 Record Locks
**SELECT * FROM goods.members WHERE ID=1 FOR UPDATE; ● When an SQL query does not run on any index, an X lock will be placed after each clustered index. This is similar to a table lock, but the principle should be completely different from a table lock. ● InnoDB creates a hidden aggregate primary key index in the background and enforces record locking even if there is no index on the table being queried. ● Blocks insertion, update, and deletion of other transactions.
RECORD LOCKS space id 51 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 270900 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Copy the code3.6 Gap Locks
** only one index interval (open interval) is locked. ** locks gaps in the range of index entries (locks are placed between gaps in index records, or locks are placed before or after an index record, excluding the index record itself) to avoid phantom reads. They do not prevent other transactions from obtaining a gap lock on the same gap, so gap X lock and GAP S lock have the same effect. If members ID primary key gaps in the table scope: (- up, 1), (1, 2), (2, 3), (3, 4), (4, + up). Example: transaction T1:
SELECT * FROM goods.members WHERE ID>1 AND ID<4 FOR UPDATE;
Copy the code
Transaction T2:
UPDATE goods. Members SET Tel='110' WHERE ID IN (1,4);Copy the code
UPDATE goods. Members SET Tel='110' WHERE ID IN (2,3);Copy the code
It is obvious that T1 has added a gap lock in the interval of primary key ID (2,3). When T1 does not release the lock, T2 will be blocked when it wants to update the values in the interval of ID>1 AND ID<4.
3.7 Next-key Locks
** Next-key Locks are also a special type of Gap lock. They are a combination of Record Locks and Gap Locks. ** next-key is an S lock or an X lock (S lock for read, X lock for write) on the space between the Next index record and the space before the index.
3.8 Insert Intention Locks
Gap Locks have an Insert Intention Lock that occurs during an Insert operation. When multiple transactions simultaneously write different data to the same index gap, there is no need to wait for other transactions to complete and no lock wait occurs. Suppose there is a record index with key values 4 and 7, and different transactions insert 5 and 6, respectively. Each transaction generates an insert intent lock between 4 and 7, acquiring an exclusive lock on the insert row, but not locking each other, because rows do not conflict.
3.9 Compatibility matrix of row locks
4. A deadlock
A deadlock is a situation in which multiple processes compete for resources during operation. When a process is in a deadlock, it cannot move forward without external forces. As shown below:
Therefore, we take an example to describe that if there is A transaction A that first holds lock A and then obtains lock B, at the same time there is another transaction B that holds lock B and then obtains lock A, deadlock will occur.
4.1 Four necessary conditions for deadlock
** Mutually exclusive: ** refers to the exclusive use of allocated resources by a process, that is, only one process occupies a certain resource in a period of time. If another process requests the resource at this time, the requester can only wait until the process holding the resource is released. ● Request and hold conditions: ** refers to a process that has held at least one resource, but makes a new request for the resource, which has been occupied by another process. In this case, the requesting process blocks, but does not let go of the other resources it has obtained. ** Undeprivable condition: ** means that a process has acquired a resource that cannot be taken away until it is used up. It can only be released when it is used up. ● Loop waiting condition: ** indicates that there must be a process — resource loop chain when deadlock occurs, that is, process set {P0, P1, P2, •••, Pn} P0 is waiting for a resource occupied by P1; P1 is waiting for resources occupied by P2…… Pn is waiting for a resource that has been occupied by P0.
4.2 Deadlock Example
Use the goods.members table again, where the MemberName column is a non-clustered index, to clear the previous sample data:
TRUNCATE TABLE goods.members;
Copy the codePre-insert two member data:
INSERT INTO goods.members (MemberName,Tel) VALUES ('A','110'),('C','130');
Copy the code
Transaction T1:
UPDATE goods.members SET Tel='130' WHERE MemberName='C';
Copy the code
** Because the MemberName field is an index, the Update statement must have a record lock with MemberName=’C’. ● Clearance lock: **Update adds the left interval (A,C) and the right interval (C, +∞) to the non-unique index MemberName=’C’ (there is currently only one entry in the goods.members table where MemberName=’C’). **● Next-key Locks: ** Record Locks + Gap Locks, meaning that the Update statement holds (A,C) next-key Locks. Transaction T2:
UPDATE goods.members SET Tel='110' WHERE MemberName='A';
Copy the code
** Because the MemberName field is an index, the Update statement must have A record lock with MemberName=’A’. ● Clearance lock: **Update adds A gaplock to the left segment (-∞,A) and the right segment (A,C) of the non-unique index MemberName=’A’ (there is currently only one member in the goods.members table where MemberName=’A’). **● NEXT-key Locks: ** Record Locks + Gap Locks, meaning that the Update statement holds (-∞,A] next-key Locks. Transaction T1:
INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120');
Copy the code
The first is to block and wait for T2 to finish execution before showing the result! ** insert MemberName= ‘B’ (B is between A and C), so you need to add (A,C) gap lock. ** Insert Intention: ** An Insert Intention lock is A gap lock that is set prior to an Insert row operation. This lock releases the signal of an Insert mode that transaction T1 needs to Insert an Intention lock (A,C). Transaction T2:
INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140');
Copy the code
** insert MemberName= ‘D’ (D after C), so add (C,+∞) gap lock. ** Insert Intention: ** An Insert Intention lock is a gap lock that is set before an Insert row operation. This lock releases the signal of one Insert mode, that transaction T2 requires an Insert Intention lock (C,+∞). Transaction T1:
After T2 completes, transaction T1 inserts MemberName= ‘B’ statement from block to deadlock.
4.3 Deadlock analysis
In the deadlock example above, I have drawn a table for you to understand the deadlock process more clearly:
| The order number | Transaction T1 | Transaction T2 |
|---|---|---|
| 1. | BEGIN; | |
| 2. | UPDATE goods.members SET Tel=’130′ WHERE MemberName=’C’; Hold locks :(A,C] next-key locks and (C, +∞) gap locks. | |
| 3. | BEGIN; | |
| (4) | UPDATE goods.members SET Tel=’110′ WHERE MemberName=’A’; Holding locks :(-∞,A] next-key lock and (A,C) gap lock. | |
| 5. | INSERT INTO goods.members (MemberName,Tel) VALUE (‘B’,’120′); Hold lock :(C, +∞) gap lock. Wait lock :(A,C) insert intent lock. | |
| 6. | INSERT INTO goods.members (MemberName,Tel) VALUE (‘D’,’140′); Holding locks :(A, C) gap locks. Wait lock :(C, +∞) insert intent lock. | |
| All landowners | Deadlock found when trying to get lock; try restarting transaction |
Deadlock log: deadlock log: deadlock log
SHOW ENGINE INNODB STATUS;Copy the codeThe log is as follows:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-08-04 11:39:12 0x7fee8b558700
*** (1) TRANSACTION:
TRANSACTION 271069, ACTIVE 590 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 1123904, OS thread handle 140662933055232, query id 4785256 localhost root update
INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120')
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271069 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 43; asc C;;
1: len 4; hex 80000002; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271069 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 43; asc C;;
1: len 4; hex 80000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 271070, ACTIVE 432 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 1123909, OS thread handle 140662461384448, query id 4785257 localhost root update
INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271070 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 43; asc C;;
1: len 4; hex 80000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271070 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Copy the code4.3.1 Transaction T1 Log
● Find the latest deadlock log record and find transaction T1(271069) :
● Query transaction T1 log and execute SQL statement
INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120');
Copy the code● Hold THE LOCK in THE transaction T1 log: index IX_MemberName, PHYSICAL RECORD, interval (unknown, +∞), (unknown, C)
● WAITING FOR THIS LOCK TO BE GRANTED Lock_mode X locks gap before rec insert intention waiting, index IX_MemberName, PHYSICAL RECORD, interval (unknown, C).
4.3.2 Transaction T2 log
● Then find transaction T2(271070) :
SQL > select * from transaction T2;
INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140');
Copy the code● Hold THE LOCK in THE transaction T2 log Index (index IX_MemberName), lock_mode X locks gap before REC, PHYSICAL RECORD, interval (unknown, C).
WAITING FOR THIS LOCK TO BE GRANTED Lock_mode X locks gap before rec insert intention waiting, index IX_MemberName, PHYSICAL RECORD, interval (unknown, + up).
4.3.3 Viewing the Log Summary
● The insert intent exclusive lock that transaction T1 is waiting for is in the arms of transaction T2. ● Transaction T2 holds a gap lock and is waiting to insert an intentional exclusive lock.
4.4 summarize
● Transaction T1 holds (A,C] next-key lock and (C, +∞) interval lock after the Update MemberName=’C’ statement. ● Transaction T2 executes Update MemberName=’A’ and holds (-∞,A] next-key lock and (A,C) gap lock. Insert MemberName=’B’, Insert intent lock (A,C), Insert intent lock (A,C), Insert intent lock (A,C)) ● Transaction T2 continues to wait because (C, +∞) is not released in transaction T1, while (C, +∞) is not released in transaction T1. ● Transaction T1 holds (C, +∞) gap lock and is waiting for (A,C) insert intention lock, while transaction T2 holds (A,C) gap lock and is waiting for (C, +∞) insert intention lock, so A closed loop of deadlock is formed (gap lock and insert intention lock will conflict, you can see the compatibility matrix of row lock). ● After transaction T1 and T2 form a dead lock ring, because of InnoDB’s underlying mechanism, it will make one transaction give up resources and let the other transaction execute successfully. This is why you finally see transaction T2 insert successfully. The insertion of transaction T1 finally shows that the Deadlock found when trying to get lock by blocking; Try restarting the transaction. Mysql > SELECT * FROM performance_schema.data_locks; mysql > SELECT * FROM performance_schema.data_locks;
Search the public account of “Programmer Black brother” on wechat, and brush it every day to easily improve skills and win various offers: