Retry on deadlock for MySQL / SQLAlchemy

Question

I have searched for quite some time now and can't found a solution to my problem. We are using SQLAlchemy in conjunction with MySQL for our project and we encounter several time the dreaded error: 1213, 'Deadlock found when trying to get lock; try restarting transaction'. We would like to try to restart the transaction at most three times in this case. I have started to write a decorator that does this but i don't know how to save the session state before the fail and retry the same transaction after it ? (As SQLAlchemy requires a rollback whenever an exception is raised) My work so far, ``` def retry_on_deadlock_decorator(func): lock_messages_error = ['Deadlock found', 'Lock wait timeout exceeded'] @wraps(func) def wrapper(*args, **kwargs): attempt_count = 0 while attempt_count < settings.MAXIMUM_RETRY_ON_DEADLOCK: try: return func(*args, **kwargs) except OperationalError as e: if any(msg in e.message for msg in lock_messages_error) \ and attempt_count <= settings.MAXIMUM_RETRY_ON_DE…

Accepted Answer

You can't really do that with the `Session` from the outside. `Session` would have to support this internally. It would involve saving a lot of private state, so this may not be worth your time.

I completely ditched most ORM stuff in favour of the lower level SQLAlchemy Core interface. Using that (or even any dbapi interface) you can trivially use your `retry_on_deadlock_decorator` decorator (see question above) to make a retry-aware `db.execute` wrapper.

```
 @retry_on_deadlock_decorator
 def deadlock_safe_execute(db, stmt, *args, **kw):
     return db.execute(stmt, *args, **kw)
```

And instead of

```
 db.execute("UPDATE users SET active=0")
```

you do

```
 deadlock_safe_execute(db, "UPDATE users SET active=0")
```

which will retry automatically if a deadlock happens.

Retry on deadlock for MySQL / SQLAlchemy

Solution

Problem