Retry DB transaction on deadlock in multi-master replication

[tom--]

Feature request for discussion.

When the DB server uses multi-master replication, conflict can occur when clients of different replication masters write to the same row.

In the case of Galera replication (Dealing with Multi-Master Conflicts):

When two transactions come into conflict, the later of the two is rolled back by the cluster. The client application registers this rollback as a deadlock error. Ideally, the client application should retry the deadlocked transaction, but not all client applications have this logic built in.

If this is implemented it should be above the PDO layer but below the APIs that the application uses to access the DB. Hence I wonder if Yii might usefully provide this feature. I guess yii\db\Command::queryInternal() is roughly where this might happen.

If this were implemented it would

• be optional and turned off by default
• catch deadlock error exceptions from PDO
• retry a deadlocked transaction a configurable number of times, with the default number of times being, say, 2
• throw the caught exception if the number of retries is exhausted
• possibly wait a small time before retrying, I am not sure if this is useful

I imagine it would be fairly easy to implement but I it might be quite hard to properly test.

[samdark]

Want to try implementing it?

[rlucian]

Sounds really useful, we might give it a try, we're using the same setup.

[samdark]

Core team currently has no resources to work on this one but we can review/merge it. Next release (~2-3 months) is last chance to get enhancements into 2.0.

[tom--]

I put this in my team's todo list. But I wouldn't want to contribute it to Yii without decent tests.

Does anyone have an idea how to, from a unit test, cause MySQL to emit a deadlock error?

[rugabarbo]

https://github.com/yiisoft/yii2/blob/master/tests/framework/db/mysql/connection/DeadLockTest.php#L32

[tom--]

wow

thanks @rugabarbo