Database Deadlock

Game development with Board Game Arena Studio
Post Reply
User avatar
predatorc
Posts: 7
Joined: 05 April 2012, 19:07

Database Deadlock

Post by predatorc »

I got this message:
Server syntax error:
Warning: mysqli::query(): (40001/1213): Deadlock found when trying to get lock; try restarting transaction in /var/tournoi/release/tournoi-230510-1001-gs/www/include/APP_DbObject.inc.php on line 275
{"status":"0","error":"mysql_deadlock_restart_transaction (reference: GS1 15\/05 10:18:45)","expected":0,"code":501,"stack":"#0 \/var\/tournoi\/release\/tournoi-230510-1001-gs\/www\/game\/module\/common\/deck.game.php(334): APP_DbObject::DbQuery()\n#1 \/var\/tournoi\/release\/games\/greatestgames\/999999-9999\/greatestgames.game.php(440): Deck->getCardsInLocation()\n#2

As you can see, it occured while I was trying to get cards from a location in a standard Deck component. Why did it occurre and how can I avoid this?
Ditto11
Posts: 64
Joined: 31 March 2023, 19:14

Re: Database Deadlock

Post by Ditto11 »

Ok, so not really sure if you know or not . so will briefly explain:

When a session in the database tries to update something, they will request a LOCK on that object (ie row/table/etc depending on the scope of the request).
If another session tries to access this data, it will WAIT for the lock to clear. (in some databases (including MySQL) Reads don't wait, and are not blocked). If, however, the other session also tries to UPDATE the same record, they'll have to WAIT.

Now, image a scenario:
Session 1 updates/locks Row 10.
at same time, Session 2 updates/locks Row 20.
Next, Session 1 tries to update Row 20 - it has to WAIT.
Finally, Session 2 tries to update Row 10 - it has to WAIT.

Both sessions are waiting on each other = Deadlock.

Almost every time a deadlock occurs, it's a logic error in the coding. I'd suggest to revisit the logic you have, specifically in regards to multiple users access/triggering code at same time. ie Do you have a Multiple Active Player situation ?

You're saying you're drawing form a deck - is it just one player drawing? or are multiple players drawing at same time?

[disclaimer] I'm new on here, and although an experienced Oracle DBA, still learning my way around the BGA interface and API. [/disclaimer]
User avatar
Shazzypaz
Posts: 75
Joined: 27 December 2020, 15:22

Re: Database Deadlock

Post by Shazzypaz »

Deadlocks are common for multiplayeractive games, and games that have "out of turn" actions.

This topic discusses a solution:
https://en.doc.boardgamearena.com/Troubleshooting

If you join the bga-developers discord server, the database channel has a LOT of discussion of this issue.
User avatar
predatorc
Posts: 7
Joined: 05 April 2012, 19:07

Re: Database Deadlock

Post by predatorc »

Thank you for your advices.
My case was a bit simplier, but instructive, so: newbies, listen and learn! :D
I initialized an event listener - dojo.connect() - in the onEnteringState() function, but I never destroyed the listener. So, when the game entered this state again and again, every time a new listener was added. It resulted in a multiple function calls with a single event (click). When this event handler called the database, the deadlock occured during a sigle player active state.
So, the advices are:
1. Initialize all your event listeners in the setup function.
2. If you cannot, (for example, the object enters the game later) destroy the listener or the object that the listener was connected to.
User avatar
Victoria_La
Posts: 603
Joined: 28 December 2015, 20:55

Re: Database Deadlock

Post by Victoria_La »

predatorc wrote: 19 May 2023, 16:49 Thank you for your advices.
My case was a bit simplier, but instructive, so: newbies, listen and learn! :D
I initialized an event listener - dojo.connect() - in the onEnteringState() function, but I never destroyed the listener. So, when the game entered this state again and again, every time a new listener was added. It resulted in a multiple function calls with a single event (click). When this event handler called the database, the deadlock occured during a sigle player active state.
So, the advices are:
1. Initialize all your event listeners in the setup function.
2. If you cannot, (for example, the object enters the game later) destroy the listener or the object that the listener was connected to.
Also it helps to read API docs on BGA wiki about dojo.connect which explicitly states all theose things
Post Reply

Return to “Developers”