I believe you will inevitably encounter deadlock problems because of improper writing in the process of using the database. How do I prevent deadlocks? And, after the deadlock problem occurs, how should we analyze step by step to unlock the deadlock? This article will discuss this topic with you.
What is a deadlock
For the definition of deadlock, Baidu Baike defines it as follows:
A deadlock is a phenomenon in which two or more processes are blocked during execution, either by competing for resources or by communicating with each other, and cannot proceed without external action. The system is said to be in a deadlock state or a deadlock occurs in the system. These processes that are always waiting for each other are called deadlocked processes.
However, a program that is at risk of deadlock may not be deadlocked, and deadlocks are not 100% guaranteed. The following conditions must be met for a deadlock to occur:
- Mutually exclusive: A process uses the allocated resources exclusively. That is, only one process occupies a 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: a process that has held at least one resource makes a new request for the resource that has been occupied by another process. In this case, the requesting process blocks but does not release other resources that it has obtained.
- Undeprivable condition: a resource acquired by a process cannot be deprivable until it is used up. It can only be released when it is used up.
- Loop waiting condition: when a deadlock occurs, there must be a process — resource loop chain, that is, P0 in process set {P0, P1, P2, ··· Pn} 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.
Deadlock combat
After understanding deadlocks, next, I will take a database deadlock case I recently encountered as an example, take you step by step to analyze the generation of deadlocks. In order to avoid the business scenario increasing everyone’s understanding cost, I will simplify this case accordingly.
Relevant background
There’s a list of details
CREATE TABLE `detail` (
`id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
`code` VARCHAR ( 10 ) NOT NULL DEFAULT ' ' COMMENT 'code',
`desc` VARCHAR ( 50 ) NOT NULL DEFAULT ' ' COMMENT 'description',
`status` TINYINT ( 1 ) NOT NULL DEFAULT '1' COMMENT 'Status, 1-valid, 0-invalid'.PRIMARY KEY ( `id` ),
KEY `idx_code` ( `code` ) USING BTREE COMMENT 'code'
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4
Copy the codeIndex: primary key index ID, non-unique secondary index idx_code
The isolation level of the database is RR
There is a process logic in business
@Transactional(rollbackFor = Exception.class)
public void updateDetail(Detail newDetail) {
detailMapper.invalidByCode(newDetail.getCode())
detailMapper.insertSelective(newDetail);
}
Copy the codeA relatively simple section of processing logic, transaction, according to the code first invalid existing data, and then insert the code corresponding to the new data
Existing data in the database:
In this case, the index of the detail table is:
Supremum, what is this record?
We can simply think of it as a “pseudo record” in a data page. No matter how many of its own records there are in the mysql data page, there are always two virtual records, also known as pseudo records, which are “Infimum” (minimum record) and “Supremum” (maximum record). Infimum->1->2->3->… ->Supremum, this kind.
Deadlock occurs:
2021-03-22 20:15:44.067[- | xxxxxxThread2] ERROR C.L.S.S.T.T.E.X abnormal org. XXXX - mission springframework. Dao. DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock;try restarting transaction
### The error may exist in class path resource [mybatis/mappers/xxxxxMapper.xml# # #The error may involve com.xxx.xxxxxMapper.insertSelective-Inline# # #The error occurred while setting parameters# # #SQL: insert into detail xxxx# # #Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator .java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy149.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)... . .Copy the codeLog view: After a deadlock occurs, the deadlock log of the database is viewed
Run the following command to view the mysql deadlock log:
show engine innodb status
I captured the deadlock section of the log as follows:
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-03-26 19:35:39 0x70000c70f000 *** (1) TRANSACTION: TRANSACTION 16632, ACTIVE 8 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 15, OS thread handle 123145510760448, Query ID 2824 localhost 127.0.0.1 root update INSERT INTO 'test'. 'detail' (' code ', 'desc ',' status') VALUES ('w', 'white', 1) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 67 page no 4 n bits 80 index idx_code of table `test`.`detail` trx id 16632 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;; *** (2) TRANSACTION: TRANSACTION 16633, ACTIVE 6 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 16, OS thread handle 123145511038976, Query ID 2828 localhost 127.0.0.1 root update INSERT INTO 'test'. 'detail' (' code ', 'desc ',' status') VALUES ('y', 'yellow', 1) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 67 page no 4 n bits 80 index idx_code of table `test`.`detail` trx id 16633 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;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 67 page no 4 n bits 80 index idx_code of table `test`.`detail` trx id 16633 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;; *** WE ROLL BACK TRANSACTION (2)Copy the codeDeadlock log interpretation
In the background above, I listed the table structure, index, data condition of the database, as well as the code snippet where the deadlock occurs, and looked at the mysql deadlock log. Next, it is to interpret the log, combined with the existing knowledge, step by step to analyze the cause of deadlock.
Transaction 1 related information
TRANSACTION 16632, ACTIVE 8 sec inserting
TRANSACTION 16632 indicates the ID of a TRANSACTION
ACTIVE 8 SEC Indicates the transaction activity time
Inserting refers to the current running state of the transaction. Possible transaction states include: fetching rows, updating, deleting, inserting, and so on.
mysql tables in use 1, locked 1
Tables in Use 1 indicates that a table is in use
Locked 1 indicates that there is a table lock
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
LOCK WAIT indicates that the transaction is waiting for a LOCK
3 Lock struct(s) indicates that the length of the chain table of the transaction is 3, and each linked list node represents a lock structure held by the transaction, including table lock, record lock, autoinc lock, etc
Heap Size 1136 Specifies the size of the locked heap memory allocated for transactions
2 Row lock(s) indicates the number of row locks held by the current transaction. Find the number of records whose type is LOCK_REC by iterating through the three lock structures mentioned above
Undo log entries 1 indicates that the current transaction has one Undo log record
MySQL thread id 15, OS thread handle 123145510760448, query id 2824 localhost 127.0.0.1 root update
The thread information about the transaction is not very useful
INSERT INTO test.detail( code, desc, status) VALUES ( 'w', 'white', 1)
Here is the SQL statement that is waiting for the lock, and you can see that the lock wait occurred and was blocked during the INSERT
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
Literally, a lock that indicates that the current transaction is waiting on
RECORD LOCKS space id 67 page no 4 n bits 80 index idx_code of table test.detail trx id 16632 lock_mode X insert intention waiting
Mysql > update mysql > update mysql > update mysql > update mysql As we all know, mysql can be divided into many kinds of locks according to different dimensions, row locks, table locks; Shared lock, exclusive lock; Record locks, gap locks, neighborhood locks, and so on. So what does InnoDB lock look like?
RECORD LOCKS: The LOCKS on which transactions are waiting are RECORD LOCKS
Space ID 67: The tablespace id is 67
Page no 4: The page number of the data page is 4
N bits 80: For row locking, each record corresponds to one bit. A page contains many records. Different bit bits are used to distinguish which record is locked. This puts a bunch of bits at the end of the row lock structure, and the n_bits attribute represents how many bits are used.
Index idx_code of table test.detail: indicates the index information, indicating the lock on the secondary index IDx_code
Lock_mode X insert intention: Inserts an intention lock
-Dan: I’m waiting
How do you tell which lock it is from the log?
-lock_mode X locks rec but not gap
-lock_mode X locks gap before REc
Next-key lock (next-key lock) -lock_mode X
Insert intention lock -lock_mode X locks gap before rec insert intention
The particularity of the lock on the supremum record
-lock_mode X locks rec but not gap
Gap lock – lock_mode X
Next-key lock (next-key lock) -lock_mode X
Insert intention lock -lock_mode X insert intention
What is an intent lock?
In InnoDB, when a transaction inserts a record, it needs to determine whether the position of the insert is locked by another transaction (temporary key locks also contain gap locks). If so, the current transaction inserts will wait until the transaction with a gap lock commits. Insert intention lock itself is also a special gap lock.
Is it ok to use gap lock instead of insert intention lock?
Since inserting intention lock itself is a special gap lock, why not replace inserting intention lock with gap lock? Why do I have to insert an intent lock? As we know, gap locks exist only to prevent the insertion of phantom records and are not mutually exclusive. Besides, gap lock itself does not cause jam, that is to say, it arouses waiting, while insert intention lock has blocking function. The joint action of gap lock and insert intention lock is the fundamental solution to phantom read.
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Now that we know the type of lock the transaction is waiting for, which record is the waiting lock attached to?
The structure of the data page has an attribute, heAP_no, that represents the current record position on the page. The heAP_NO value of Infimum is 0, and the heAP_NO value of Supremum is 1.
Supremum: Indicates that the waiting lock is the lock on the record “supremum”
We should now understand the behavior of transaction 1 when a deadlock occurs:
Transaction 1 is waiting for the supremum record to insert an intent lock on the IDx_code index.
Transaction two related information
The log information for transaction two is basically similar to that for transaction one, so we won’t repeat the analysis. SQL > LOCK(S); SQL > LOCK(S); SQL > LOCK(S); SQL > LOCK(S);
RECORD LOCKS space id 67 page no 4 n bits 80 index idx_code of table test.detail trx id 16633 lock_mode X
Indicates that transaction 2 is holding a neighbor key lock on index IDx_code
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Indicates that transaction two holds the lock on the record “supremum”
Combined with the rest of the log information, we can know the behavior of transaction two when a deadlock occurs:
Transaction two holds the adjacent key lock for the supremum record on the idx_code index. And is waiting for the insert intent lock for supremum on the idx_code index.
Deadlock reasoning
So far, we have learned a lot from the logs about transaction one and transaction two when a deadlock occurs. Now it’s time to reason and unlock the deadlock.
There is a sentence in a detective game I played before, I like it very much, “the so-called reasoning, but imitate the murderer’s process”. The same is true for us to unlock deadlocks. To unlock deadlocks, we need to simulate the process of deadlock occurrence.
So based on the above analysis, can we simulate the occurrence of a deadlock? It’s actually very difficult. If there is only one SQL statement per transaction, we can analyze the cause of the deadlock just by using the deadlock log. However, in most cases, there is more than one SQL statement per transaction. The above undo log entries 1 indicates that additional SQL statements were executed during the insert, but the details are not shown in the log. In addition, from the log, we only know the lock holding condition of transaction 2 when the deadlock occurs, but not the lock holding condition of transaction 1. Therefore, deadlock diagnosis should not only rely on deadlock logs, but also be analyzed in conjunction with the application code. Of course, if you can’t see the specific application code, you can also use the database’s binlog to analyze. Of course, I am a developer myself, so next, from a developer’s perspective, I use the application code to deduce the occurrence of deadlocks.
Let’s take out the business code mentioned at the beginning:
@Transactional(rollbackFor = Exception.class)
public void updateDetail(Detail newDetail) {
detailMapper.invalidByCode(newDetail.getCode())
detailMapper.insertSelective(newDetail);
}
Copy the codeThat is, before inserting a new detail, an invalid operation is performed according to the code of the new detail. The above two lines correspond to the following SQL:
UPDATE detail set `status`=0 where `code`= ? ;
INSERT INTO `test`.`detail`( `code`, `desc`, `status`) VALUES ( ?, ?, ?);
Copy the codeSQL = ‘insert’; SQL = ‘insert’; INSERT INTO test.detail(code, desc, status) VALUES (‘w’, ‘white’, 1) INSERT INTO test.detail(code, desc, status) VALUES (‘w’, ‘white’, 1)
UPDATE detail set `status`=0 where `code`='w' ;
INSERT INTO `test`.`detail`( `code`, `desc`, `status`) VALUES ( 'w'.'white'.1);
Copy the codeSimilarly, the specific SQL for transaction 2 is:
UPDATE detail set `status`=0 where `code`='y' ;
INSERT INTO `test`.`detail`( `code`, `desc`, `status`) VALUES ( 'y'.'yellow'.1);
Copy the codeWe know that in order to prevent phantom reads, at the RR level, a gap lock/neighborhood lock is added to the update statement (depending on whether the record to be updated exists). Transaction 1 needs to update the record with code=’w’. The record with code=’w’ does not exist. To prevent other transactions from inserting the record with code=’w’, a gap lock is used. Code =’w’ is inserted before ‘supremum’, so a gap lock is applied to ‘supremum’. When the insert statement is executed, the position to be inserted is determined to be before “supremum”, thus generating an insert intent lock for the “supremum” record. Same with transaction two.
We use a schematic to restore the scene of the deadlock (simplifying the lock structure) :
Careful readers will no doubt notice that the supremum record has a gap lock. Why is it a neighboring lock? Here to sell a secret, if the subsequent time, will specifically write a database lock article. The adjacent key lock itself contains a gap lock, which can be treated as a gap lock.
The reason for the deadlock is clearly deduced from the figure above.
When transactions 1 and 2 execute update statements, a neighborhood lock of the supremum record is generated. When transaction one and transaction two execute insert statements, the insert intent lock for the “supremum” record is generated. Gap locks and insert intent locks cannot exist together. Transaction one waits for transaction two to release the neighbor lock recorded by supremum. Transaction two waits for transaction one to release the neighbor lock recorded by supremum. When you wait for each other, deadlocks form! The timeline is as follows:
| A transaction | Transaction 2 |
|---|---|
| UPDATE detail set status=0 where code=’w’ | |
| UPDATE detail set status=0 where code=’y’ | |
| INSERT INTO test.detail( code, desc, status) VALUES ( ‘w’, ‘white’, 1) | |
| INSERT INTO test.detail( code, desc, status) VALUES ( ‘y’, ‘yellow’, 1) |
Solution of deadlock
After finding the cause of the deadlock, it is necessary to unlock the deadlock and destroy the condition of the deadlock.
Because the field we need to update is status, which is not in the index IDx_code, we can use the form of BY ID to update, because idx_code index does not contain the status field, because the way by ID, Idx_code will not be locked, so there is no deadlock.
conclusion
Unlocking a deadlock simply mimics the process of deadlock formation.
In the course of our daily development, we can inadvertently write code that has the potential for deadlocks. After all, deadlocks aren’t 100% guaranteed, and it’s likely that deadlock-prone code won’t be deadlocked for a very long time. But what we need to do is to strengthen our understanding of database locks. Understanding the types and functions of locks is only the first step. What we need to do more is to understand the structure of locks and understand the real idea of mysql bosses to create various locks.
After all, locks and indexes are the essence of mysql. If you are interested in tuning SQL indexes, you can refer to my other article SQL tuning practice summary