Scenarios that cocould cause deadlocks in MySQL

1 Overview

InnoDB automatically detects deadlocks. If a deadlock occurs, InnoDB rolls back the transaction with a relatively small weight. In fact, there are two types of deadlocks in InnoDB:

1. Real inter-transaction cyclic wait.
2. During the Deadlock Detection process, if InnoDB considers that the detection cost is too high (for example, more than 200 transactions need to be checked recursively), InnoDB abandons the Deadlock Detection and considers the deadlock to occur.

MySQL version used in this article: 5.1.42, InnoDB plugin version: 1.0.6.

 

2 scenarios

If a deadlock occursProgramIn addition to the logs, the most valuable information is probably the output of show InnoDB status. However, the deadlock-related information in the output of show InnoDB status is incomplete (for example, only the last two transactions that cause the deadlock are recorded, and the last two SQL statements executed ). Based on my daily work experience, I have summarized the following scenarios that may lead to deadlocks.

 

2.1 scenario 1

Create Table Test (ID int primary key, name varchar (10) engine = InnoDB;
Insert into test values (1, '1'), (2, '2 ');

Set @ tx_isolation = 'read-committed ';

Session	Session B
Start transaction;	Start transaction;
Update Test Set Name = '11' where id = 1;
	Update Test Set Name = '22' where id = 2;
Update Test Set Name = '21' where id = 2; # Blocked
	Update Test Set Name = '12' where id = 1; # Deadlock

Comment: This is one of the most common deadlock scenarios. The solution is resource ordering, which ensures that all associated transactions hold locks in the same order.

 

2.2 scenario 2

Create Table T (ID int primary key, Count INT) engine = InnoDB;
Insert into T values (1, 1 );

Set @ tx_isolation = 'read-committed ';

Session	Session B
Start transaction;	Start transaction;
Select * from t where I = 1 lock in share mode;
	Select * from t where id = 1 lock in share mode;
Update t set COUNT = 2 where id = 1; # Blocked
	Update t set COUNT = 3 where id = 1; # Deadlock

Comment: In this scenario, resource ordering does not help, select... lock in share mode is adjusted to select... for update.

 

2.3 scenario 3

Create Table parent (ID int primary key, Count INT) engine = InnoDB;
Create Table child (ID int primary key, parent_id int, foreign key (parent_id) References parent (ID) engine = InnoDB;
Insert into parent values (1, 0 );

Set @ tx_isolation = 'read-committed ';

Session	Session B
Start transaction;	Start transaction;
Insert into child values (1, 1 );
	Insert into child values (2, 1 );
Update parent set COUNT = count + 1 where id = 1; # Blocked
	Update parent set COUNT = count + 1 where id = 1; # Deadlock

Comment: During the foreign Key Integrity check, InnoDB will set a shared read lock on the checked records. In this example, when inserting a child, the records with the ID of 1 in the parent table are also set with the shared read lock.

It should be noted that CAs SSO performs similar database operations during login, so there is a potential deadlock.

 

2.4 scenario 4

Create Table parent (ID int primary key, Count INT) engine = InnoDB;
Create Table child (ID int primary key, parent_id INT) engine = InnoDB;
Insert into parent values (1, 0 );

Set @ tx_isolation = 'read-committed ';

Session	Session B	...	Session n
Start transaction;	Start transaction;		Start transaction;
Insert into child values (1, 1 );	Insert into child values (2, 1 );		Insert into child values (n, 1 );
Update parent set COUNT = count + 1 where id = 1;	Update parent set COUNT = count + 1 where id = 1;		Update parent set COUNT = count + 1 where id = 1;
Deadlock may occur in some sessions.

Comments: In the above scenario, if n> 200 and these transactions are executed concurrently, the deadlock may occur and some transactions will be rolled. This is a typical scenario of the second type of deadlock. The output of show InnoDB status contains the following content: "Too deep or long search in the lock table waits-for graph ".

Note that the operations performed before the update parent set COUNT = count + 1 where id = 1; statement are not important. The key is that these transactions update the same record concurrently, eventually, InnoDB gave up Deadlock Detection.

 

2.5 scenario 5

Create Table Test (ID varchar (10) primary key, Count INT) engine = InnoDB;
Insert into test values ('id1_0001 ', 0), ('id1_0002', 0), ('id1_0003', 0 );

Set @ tx_isolation = 'read-committed ';

Session	Session B
Start transaction;	Start transaction;
Update test inner join (select *, sleep (15) from test where ID <= 'id1_0002') T on test. ID = T. Id set test. Count = 1; # Sleeping
	Update Test Set COUNT = 3 where id = 'id1_0001 '; # Blocked
Error 1213 (40001): deadlock found when trying to get lock; try restarting transaction #15 × 2 (2 records selected) seconds later

Comment: Because InnoDB adopts MVCC, in general cases (non-serializable transaction isolation level), normal select statements do not lock records in the query result set, it will not be blocked by the existing lock. However, InnoDB sets a shared read lock for each record in the query result set of the select clause of the update statement. This is the cause of the import death lock in this example.

Note that the sleep function in the select clause in this example is called to make it easier to reproduce the deadlock and has no other special effect. For this type of deadlock, it is best to adjust the business logic, just as the update Statement of session A in this example tries to conditional update some records of the test table, adjust the update statement to avoid deadlocks.

 

2.6 scenario 6

Create Table T1 (ID int primary key, name varchar (10) engine = InnoDB;

Set @ tx_isolation = 'serializable ';

Session	Session B
Start transaction;	Start transaction;
Select * from T1 where id = 1;
	Select * from T1 where id = 1;
Insert into test values (1, 'A '); # Blocked
	Insert into test values (1, 'A '); # Deadlock

Comment: At the serializable transaction isolation level, if autocommit is disabled, InnoDB implicitly converts a normal SELECT statement to select... lock in share mode, that is, set the shared read lock on each record in the query result set.

Note that if the default configuration is used, spring batch 2.0.0 performs similar database operations at the serializable transaction isolation level, which may eventually lead to a deadlock. If MySQL is used to store database tables related to spring batch, you need to adjust the configuration of spring batch to adjust the transaction isolation level from the default serializable to Repeatable read.

 

2.7 scenario 7

Create Table T1 (I int, primary key (I) engine = InnoDB;

Set @ tx_isolation = 'read-committed ';

Session	Session B	Session C
Start transaction;	Start transaction;	Start transaction;
Insert into T1 values (1 );
	Insert into T1 values (1 ); # Blocked	Insert into T1 values (1 ); # Blocked
Rollback;
	Deadlock occurs in either session B or Session C

Comment: This type of deadlock is uncommon. If duplicate-key error occurs, InnoDB will set a shared read lock on repeated index records, which eventually leads to a deadlock in this example.