SQL Deadlock

[Buckwheat999]

I am working on a card game with two medieval kingdoms are battling with different character cards. There are 30 playing spots on the board, six for each kingdom they are fighting over. Some of the cards have the ability to stack on top of each other like and advisor making a colored playing area wild and opening to any color of card being played on top of it. Some cards have the ability to 'kill' other characters and in the case of a stack of cards each card needs to be updated in the database of where it is in the stack. The 'Bard' card can charm another card and pull it over to an opponents side the kingdom. In these types of cases there are multiple updates to the database that need to occur that cannot be done in one query which have to occur before changing state and\or moving to a new routine in the program. Sometimes when these updates are done too close together, but not all of the time, I get a 'Unexpected error: mysql_deadlock_restart_transaction' and the subsequent database updates are not done. If I put a short pause inbetween the updates they still happen but the error message still pops up sometimes even though the updates have occured properly. These messages can be distressing to the user who is wondering if his game is going to error out.
How can I do multiple updates to the database with getting the 'Unexpected error: mysql_deadlock_restart_transaction' error? This happens several places in the program where I have to do this.

[Victoria_La]

That can happen only there are concurrent actions initiated by client side. It is likely you client sends multiple events quickly, to avoid this
use lock: true on action (see doc on ajax call)

The other option if you using multiplayer states and users have race conditions. Is it?

If you doing some operation during one action - its one transaction and db locked during that, nothing is needed there.

[DexterHugo]

I agree with Victoria that seems to be the likely cause, but - even if you were updating several tables, have you considered just wrapping it all one transaction and running them together? See this link for additional help:

https://www.mysqltutorial.org/mysql-transaction.aspx

You should be able to update all your tables in a single transaction/sql call (albeit, several different sql update/insert statements within that call). If you were breaking them apart because you want to send multiple resultsets back to the client, just make a series of get collection calls after your bulk update is successful.

[Victoria_La]

I agree with Victoria that seems to be the likely cause, but - even if you were updating several tables, have you considered just wrapping it all one transaction and running them together? See this link for additional help:

https://www.mysqltutorial.org/mysql-transaction.aspx

You should be able to update all your tables in a single transaction/sql call (albeit, several different sql update/insert statements within that call). If you were breaking them apart because you want to send multiple resultsets back to the client, just make a series of get collection calls after your bulk update is successful.

You don't need to do it here - it is done by framework, all code within one action is wrapped in transaction, if you doing it manully you likely break more
than fix

[DexterHugo]

You don't need to do it here - it is done by framework, all code within one action is wrapped in transaction, if you doing it manully you likely break more
than fix

That's good to know! I've always just seen calls to dbquery, but I never saw anything that said that was auto-wrapping a sql transaction statement around it. I'm sure it's buried somewhere in the docs!

[Buckwheat999]

I am not getting that error any more. Thanks for your help.!