CODEWITHSUNDEEP
mysqltransactionsinnodbdeadlockdatabase-deadlocks
anirudh_raja
anirudh_raja

Reputation: 379

Mysql concurrent updates on a row leading to deadlock

Using mysql 5.7 with storage engine as innodb. I have a table that stores product information. The table looks like this with unique key on productId

| Field     | Type         | Null | Key | Default           | Extra                       |
+-----------+--------------+------+-----+-------------------+-----------------------------+
| id        | bigint(20)   | NO   | PRI | NULL              | auto_increment              |
| productId | varchar(50)  | NO   | UNI | NULL              |                             |
| seller    | varchar(100) | NO   | MUL | NULL              |                             |
| updatedAt | timestamp    | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status    | varchar(100) | NO   | MUL | NULL              |                             |
| data      | longtext     | NO   |     | NULL              |                             |
+-----------+--------------+------+-----+-------------------+-----------------------------+

I have two operations via a java app connected to this mysql :
1. New incoming events (that contain information about product changes) for the productId need to be inserted if they have a version greater than the existing event. The version is stored as a json blob in my data column
2. Update row for the productId to change the status.

My isolation level is read-committed. I tried two approaches but both are leading to a deadlock :

Approach1: 

Transaction1 starts
 Insert ignore into products where productId='X' values();  // Takes a S lock on the row 
 select * from products where productId='X' for update ;    // Take a X lock on the row to prevent new writes and compare the incoming event with the current event
 Insert into products values on duplicate key update values // insert into row and on duplicate key update values
commit

A concurrent update will open another transaction : 

Transaction2 starts
 select * from products where productId='X' for update ;    // Take a X lock on the row to prevent new writes and compare the incoming event with the current event
 Insert into products values on duplicate key update values // insert into row and on duplicate key update values
commit;

This is leading to a deadlock in the situation when the following :
1. Transaction 1 - Insert ignore statement has taken an S lock on the row.
2. Transaction 2 - Select for update statement is waiting to take an X lock on the row.
3. Transaction 1 - Select for update statement tries to take a X lock on the row.

This leads to a deadlock because an S lock is being held by transaction 1 and transaction 2 is waiting to take to X lock and when transaction 1 tries to take a X lock, it leads to a deadlock.

Approach 2 : 

Transaction 1 starts: 
 select * from products where productId='X' for update ; // If a row exists then it will be locked else I know it does not exist
 Insert ignore into products where productId='X' values(); 
commit

Transaction 2 starts:
 select * from products where productId='X' for update ; // If a row exists then it will be locked else I know it does not exist
commit

This is leading to a deadlock in the situation when the following :
1. Transaction 1 - Select for update statement takes an X lock on the row.
2. Transaction 2 - Select for update statement is waiting to take a X lock on the row.
3. Transaction 1 - Insert ignore statement tries to take an S lock on the row, but transaction 1's X lock is already waiting for a lock which leads to deadlock

So, I wish to know how to handle concurrent update and insert new events (rather row updates) into my table without leading to deadlocks.
1. What should be the locking order ?
2. How to make sure that concurrent updates and new row insertions work without deadlocks.

Any help would be appreciated :)

Upvotes: 2

Views: 2833

Answers (1)

anirudh_raja
anirudh_raja

Reputation: 379

I managed to solve it after some experiments, the core problem was with the sequence of S and then X locks taken in one transaction and the X lock taken in another. Basically, the S lock taken at the start was leading to all cases having a deadlock.
So, I moved the insert ignore statement outside the transaction as the first statement. The transaction now only takes X locks, which means that one of the transaction waits on the other taking an X lock.

Event1 : Inserting a new event

result = Insert ignore into products where productId='X' values(); 
if result == null
 return
end
Transaction start
 select * from products where productId='X' for update ;    // Take a X lock on the row to prevent new writes and compare the incoming event with the current event
 Insert into products values on duplicate key update values // insert into row and on duplicate key update values
commit

Event 2 : Updating an existing event

 Transaction start
   select * from products where productId='X' for update ; // If a row exists then it will be locked else I know it does not exist
  commit

So, both the events have transaction that only compete for an X lock, which helped me avoid deadlocks.

Upvotes: 1

Related Questions