MySQL deadlocks and "retry"

[Jonathan LaCour]

My application uses MySQL with InnoDB tables and replication. We're
starting to encounter an issue on a few particular parts of the
application where we're getting tracebacks like this:

<class 'sqlalchemy.exc.OperationalError'>: (OperationalError)
(1213, 'Deadlock found when trying to get lock; try restarting
transaction')

According to MySQL documentation, there are a large variety of
circumstances in which this could happen, mostly innocuous in
nature. The official recommended solution to the problem is: "retry
the transaction".

Does SQLAlchemy offer some method for me to catch this exception and
then retry the transaction?

Thanks in advance!

--
Jonathan LaCour
http://cleverdevil.org

[Michael Bayer]

there's nothing offered beyond the usual notion of catching an exception
and running the function again. It also depends very much upon the
construction of your application, whether you're looking to do this in an
ORM context, etc.

[Jonathan LaCour]

Michael Bayer wrote:

>> Does SQLAlchemy offer some method for me to catch this exception
>> and then retry the transaction?
>
> there's nothing offered beyond the usual notion of catching an
> exception and running the function again. It also depends very
> much upon the construction of your application, whether you're
> looking to do this in an ORM context, etc.

Yeah, we sort of figured. Our application uses WSGI middleware
to wrap particular requests in transactions. We ended up writing
something in there to catch this particular exception and re-run the
request up to three times. Its not quite as pretty as I'd like, but
it works :)