1

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?

1
  • What value do you use for autocommit? Show us the SQL statements, including BEGIN and COMMIT.
    – Rick James
    Nov 24, 2016 at 0:31

3 Answers 3

0

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

1
  • Thanks for your response. Any suggestion on how to mitigate the problem?
    – Urbanleg
    Nov 24, 2016 at 8:38
0

Using locks is the wrong approach as this ties up one thread on your server for each player who is waiting. If there are many players, your server will quickly run out of threads. It is also a vector for a DOS attack, as this system lets one hacker easily create enough threads on your server, all waiting on locks, to shut down service.

A better way to do it is:

  • Insert the row and commit the transaction immediately, freeing up myslq connection

  • When the player has finished his turn, delete the row and commit

  • If between the previous steps, another client from the same player attempts to insert the same row , reply "try again later", if possible giving an estimate of the waiting delay

This moves the waiting threads from the server into the client, which is scalable.

-1

It looks like your lock function or key needs to take into account who set and who can release the lock (i.e. an owner). Perhaps by using a randomly generated ID. With a specific lock owner each thread has to verify that they are the owner when they set a lock and when they release a lock.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.