SQL deadlock upon subsequent server-side automatic plays

[Don Kiwi]

I'm developing a card game and am currently working on a system that allows players to preselect a card which is to be played automatically (from the server) as soon as it is the card's owner's turn to play. I set it up as follows.
- Clicking a card out of turn preselects that card, meaning an out of turn action (with corresponding ajax call) is carried out which updates the id of the preselected card for that player in the database. If the card is deselected, the same action resets the preselected card id for that player to -1.
- Before each playerTurn state, there is a "game" type state prePlayerTurn which checks whether the active player has a preselected card and, if allowed by the rules, plays that card and skips to the prePlayerTurn for the next player.
- "Playing a card" means that a method playCard is called, which among other things, sets the preselected card id of the card owner back to -1. (After all, there is no longer any preselected card after it was played.)
- If the player has no eligible preselected card, the state prePlayerTurn transitions into an "activeplayer" type state named playerTurn, which gives the turn to the player and waits till he clicks a card which is subsequently played.

The problem comes when two subsequent players have preselected an eligible card and it is the first of these two to play. In such a case, sometimes the second preselected card is not played, and sometimes this is accopanied by an sql deadlock error message. I'm quite certain that the cause of this problem is the fact that the playCard method is called twice with very little delay, which causes two subsequent database updates to reset the preselected card id to -1 for these two players, creating a deadlock for the second update.

Question: how can I make sure the update of the preselected card id for the second player is put on hold until the first player's update is done?
Or if you have another solution, that would of course also be appreciated
Thanks!

[KuWizard]

When player pre-selects a card, you could just remember this in a special DB table with player_id and card_id. Then, when prePlayerTurn state comes - you just select from this table finding if this player have pre-selected a card and play it. I guess this should order all the SQL requests (have never done that but theoretically this should work)

[Shazzypaz]

I had a lot of trouble with deadlocks with out-of-turn actions in my game. I suggest joining the BGA Developers Discord server. There's a lot of discussion around deadlocks in the Database channel.

One possible solution is documented here:

https://en.doc.boardgamearena.com/Troubleshooting

A couple of us chose a different solution of adding a function at the start of all actions, state functions and getAllDatas() that queries and locks all of the database tables in a fixed order. We did this prior to the above solution being documented. So we can't comment on whether the above solution would have worked for us. I recall someone tried the above solution for a slightly different use case, and it didn't resolve their issue.

[quietmint]

My game (Hardback) has many out of turn actions. Players can spend resources to draw cards at any time. For my use case, it was enough to run this before every update of the player table:

Code: Select all

self::DbQuery("SELECT * FROM global WHERE global_id < 10 FOR UPDATE");