Database Deadlock

[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]

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]

[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.

[predatorc]

Thank you for your advices.
My case was a bit simplier, but instructive, so: newbies, listen and learn!
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.

[Victoria_La]

Thank you for your advices.
My case was a bit simplier, but instructive, so: newbies, listen and learn!
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