Mysql – Deadlock on inserts to a single table with a single key. (Mysql)

deadlockjavaMySQL

I have a simple game server.
One of the requirement i received was to handle a single request for a single player at a time. so in order to achieve that i decided to create a lock table:

CREATE TABLE `GameRoundLock` (`theKey` varchar(255) NOT NULL,
PRIMARY KEY (`theKey`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Before I handle any request from any player I am inserting a row into that table like so:

// save and flush == acquiring the lock
GameRoundLock lock = this.gameRoundLockRepository.saveAndFlush(somePlayerKey);

   /** Play busniess logic here...  **/

// deleting == releasing the lock
this.gameRoundLockRepository.delete(lock); 

This actually works.

In the following scenario the whole system works as expected:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request… (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again – This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) thread of 2) completes

5) thread of 3) now continuing

6) DONE, playerA total played rounds == 2.

****Pay attention that the id (playerKey) is identical.****

The problem arises when the playerA tries another play request while the handling of the first thread still occurs. here is the scenario that resulting in a weird deadlock:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request… (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again – This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) While 2) is still executing and 3) is still waiting, PlayerA plays again – This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2)/3) completes.

5) thread of 2) completes

6) ERROR – a deadlock is thrown!!!!!

Here is the deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-11-23 17:05:26 0x1d98
*** (1) TRANSACTION:
TRANSACTION 5493356, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5191, OS thread handle 5152, query id 15582091 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table      `game_server`.`gameroundlock` trx id 5493356 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) TRANSACTION:
TRANSACTION 5493355, ACTIVE 12 sec inserting, thread declared inside InnoDB    1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5193, OS thread handle 7576, query id 15582083 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table     `game_server`.`gameroundlock` trx id 5493355 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info     bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table    `game_server`.`gameroundlock` trx id 5493355 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info    bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** WE ROLL BACK TRANSACTION (2)

It seems like two "close\near" inserts for the locks of the 2nd and 3rd play requests causes this. why a deadlock and how to mitigate it?

Best Answer

I guess you have following issue.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html