dbal
dbal copied to clipboard
nextras
Allow or automatically handle transaction nesting reset on "external" rollback
I just hit a problem with transaction handling:
I have an operation that can result in a deadlock and I just want to retry it. But then commit or rollback fails with SAVEPOINT NEXTRAS_SAVEPOINT_xyz does not exist.
The transaction that is the victim of a deadlock resolution is rolled back immediately by the server (MySQL for sure, I think others too) but that means that the value of Nextras\Dbal\Connection::$nestedTransactionIndex becomes invalid.
I see a way to reset it by doing a reconnect() but I don't want to do that because there are some temp tables that would vanish and I would have to find what has to be recreated.
A possible simple solution would be to allow a manual reset of the value, but that would allow breaking the stuff the other way around.
A more complex option would be to catch the deadlock error/exception somewhere inside the driver or connection and handle the reset automatically.
Currently I try to handle it by looping rollback while index>0 and swallowing the exceptions :)
Thanks for reporting this. agreed that it would be ideal to handle this automatically. I will have to study what are all cases when the rollback happens automatically.
You won't be able to catch ALL the cases probably - in MySQL (I know the most about) there are implicit commits before all DDL statements, but that does not throw any error - that means almost any CREATE, DROP, ALTER... (but not CREATE TEMPORARY). Anyone doing that inside their transaction loses the nesting level too. Implicit rollbacks are less common and the deadlock is fine, but the second possible one is a lock-wait timeout, and that may or may not do a rollback based on server config.. And a third one is server crash. So fun stuff and thats only one platform :(
I'm trying to reproduce this but it seems a simple manual deadlock (FOR UPDATE) is not enough :X https://github.com/nextras/dbal/pull/211