Handling Mysql Deadlocks

TrinityCore Development TrinityCore Code Discussion
1

[CODE] if (m_conn->GetLastError() == 1213)

{

    uint8 loopBreaker = 5;  // Handle MySQL Errno 1213 without extending deadlock to the core itself

    for (uint8 i = 0; i < loopBreaker; ++i)

	    if (m_conn->ExecuteTransaction(m_trans))

		    return true;

}[/CODE]

Is this code really considered good for handling mysql deadlocks? That loop can sometimes execute faster than lock gets freed.

2

So how can we fix it?

3

We could re-delay the transaction, but that could cause data desynchronization. We could also sleep the entire calling thread but I’m not sure about the implications thereof on performance.

4

Inserting transaction back in queue (must be inserted at beginning) and sleeping over and over again seems as the same thing, doesn’t it?

It would seem reasonable to wait until lock is free, as some things are saved to database just once (guilds, guild members, arena teams, arena team members, petitions, tickets etc)

[edit] Inserting it in back of queue doesn’t seem like good idea, as there could be something that affects same rows in queue already.

[edit2] Also I am not sure how it is with transactions. For example transaction like this:

[CODE]INSERT INTO tableA (a, VALUES (1, 2);

INSERT INTO tableB (c, d) VALUES (3, 4)[/code][/CODE]Are all locks (both tableA and tableB) acquired at start of execution, or locks are acquired at start of modifying each table?

If 2nd, then what if insert into tableA is successful, but upon trying to acquire tableB lock we face deadlock? Does data from tableA get reverted?