Database deadlocks are the hardest to debug and trace.
The scenario is as follows:
For the same table, a transaction inserts a record and then updates the record, which will be deadlocked concurrently.
And it can reproduce.
What tools can be used to simulate concurrent transactions, view information, and resolve problems? That’s what I’m going to share today.
1. Pre-preparation
set session transaction isolation level repeatable read;
set session autocommit=0;
create table t (
id int(20) primary key AUTO_INCREMENT,
cell varchar(20) unique
)engine=innodb;
start transaction;
insert into t(cell) values(11111111111);
insert into t(cell) values(22222222222);
insert into t(cell) values(33333333333);
commit;
Description:
(1) When the incident occurred, the transaction isolation level RR;
(2) Multi-terminal experiment, automatic transaction submission needs to be disabled;
(3) Create table, set PK and UNIQUE, initialize data;
Second, concurrent transaction simulation
The Session A:
start transaction;
insert into t(cell)values(44444444444); [1]
The Session B:
start transaction;
insert into t(cell) values(55555555555); [2]
update t set cell=123 where cell=44444444444; [3]
update t set cell=456 where cell=55555555555; [4]
Enable two terminals to simulate concurrent transactions:
(1) Red SQL is transaction A;
(2) Black SQL is transaction B;
(3) [1][2][3][4] is the execution sequence;
3. Experimental phenomenon
insert into t(cell)values(44444444444); [1]
Transaction A inserts data and executes first
Result: The disk is inserted successfully
insert into t(cell) values(55555555555); [2]
Transaction B inserts data, second execution
Result: Insert result
update t set cell=123 where cell=44444444444; [3]
Transaction A modifies the data inserted in [1], and the third is executed
Result: blocks, waiting for execution results
Voiceover: Modify a self-inserted data, what are you waiting for?
update t set cell=456 where cell=55555555555; [4]
Transaction B modifies the data inserted in [2] and executes last
Results:
(1) Transaction B is deadlocked and transaction B is rolled back;
(2) In transaction A, the statement [3] is blocked and executed successfully;
Voiceover: Indicates that A blocked statement in transaction A is indeed waiting for A lock in transaction B.
4. Result analysis
Two transactions, each modifying its own inserted data, create a deadlock, which is really weird.
The two core questions of the above experimental phenomena are:
(1) statement [3] blocks, waiting for what lock?
(2) statement [4] deadlocks, in which case transaction A and transaction B must hold locks on each other. What are these locks?
A tool:
show engine innodb status;
Voice-over: The preceding”Great, InnoDB debug deadlock method!InnoDB deadlock analysis practices are shared in detail.
After execution, the following is displayed (zoom in for a closer look) :
There’s a lot of information. Don’t worry.
The first part, the key words are:
(1) Transaction 1;
(2) In execution
update t set cell=123 where cell=44444444444;
Record locks (index primary), lock (mode X), lock (mode X), lock (mode X), lock (mode X), lock (mode X), lock (mode X), lock (mode X)) Physical Record, ASC 555555555;
Voice-over: English is relatively poor nothing, catch key words.
Voiceover, InnoDB storage engine, the way clustered indexes and non-clustered indexes are implemented, determines which locks are added to clustered indexes. See article:
“1 minute To understand the index difference between MyISAM and InnoDB”.
The second part, the key words are:
(1) Transaction 2;
(2) Executing
update t set cell=456 where cell=55555555555;
Asc 555555555 holds the lock, locks the index primary, locks the mutex, and physical records. Asc 555555555 holds the lock, locks the index primary, locks the mutex, and locks the physical record.
Lock_mode X is a lock on the index primary key and is locked in the lock mode X mode. Physical Record, ASC 11111111111;
(5) We roll back transaction 2;
Run show engine innodb status; It is helpful to see a lot of information between transactions and locks. This information can explain some of the problems, but there are still two puzzles:
(1) why does transaction 1 want to hold the lock on 555555555?
Voice-over: This is exactly why transaction 1 is blocked.
(2) why does transaction 2 want to hold the lock on 11111111111? If a deadlock occurs, transaction 1 is holding the lock on transaction 11111111111.
Voice-over: first transaction grab 555, second transaction grab 555, loop nesting, deadlock.
Tools. 2:
explain
To further identify the cause, you can look at the execution plan that led to the deadlock statement through Explain.
explain update t set cell=456 where cell=55555555555;
Select_type: SIMPLE
This is a simple type of SQL statement with no subqueries or unions.
Type: the index
Access type, i.e. the traversal method used to find the desired data. Potential ways are:
(1) ALL (Full Table Scan) : Full Table Scan;
(2) index: full table scan;
(3) range: range index scan that hits the WHERE clause;
(4) ref/ EQ_REF: non-unique index/unique index single-value scan;
(5) const/system: constant scan;
(6) NULL: no access table;
In the preceding scanning modes, ALL is the slowest and gradually becomes faster, while NULL is the fastest.
Doubt 1: Why scan the entire table through the PK index when there is a UNIQ index in the cell field?
Possible_keys: NULL
In which index records are likely to be found.
Key: PRIMARY
Actually use indexes.
Voice-over: Full table scan using PK.
Ref: NULL
Which columns, or constants, are used to find values on the index.
Doubt 2: The query 555555555 in the WHERE condition is supposed to be the value retrieved from the index.
5 rows:
Find the required records and estimate the number of rows to read.
Suspicion # 3: why should the initial 1, 2, and 3, as well as the 4 inserted in the first transaction, and the 5 inserted in the second transaction, be read? You shouldn’t scan all tables.
Through explain, it can be judged basically:
update t set cell=456 where cell=55555555555;
We did not go through the cell index for query as we expected, but went through the PK index for full table scan.
Take a closer look:
create table t (
id int(20) primary key AUTO_INCREMENT,
cell varchar(20) unique
)engine=innodb;
The cell is defined as a string when the table is created.
And when you update it,
update t set cell=456 where cell=55555555555;
The integer type is used.
A type conversion will cause a full table scan and lock escalation, locking all records.
Again, verify with Explain in quotes:
explain update t set cell= ‘456 ‘ where cell= ‘55555555555 ‘;
The conjecture was confirmed:
(1) Type: range, which is changed to string alignment by index and range scan;
(2) Possible_keys: cell, the record can be found through the cell index;
(3) Key: cell, the actual use of cell index;
(4) ref: const, ‘555’;
(5) rows: 1;
That explains it all.
conclusion
In this case, you need to be careful about casting between strings and integers. Sometimes a missing quote will cause a row lock to be upgraded to a table lock.
Deadlock is a very difficult problem in MySQL debugging, common ideas and methods are:
(1) Simulate concurrent transactions through multiple terminals and reproduce deadlocks;
(2) Show engine innodb status; You can view transaction and lock information.
(3) The execution plan can be viewed through Explain;
Thinking is more important than conclusion, I hope you have a harvest.