重试MySQL/SQLAlchemy的死锁 [英] Retry on deadlock for MySQL / SQLAlchemy

问题描述

我已经搜索了很长时间，但是找不到解决我问题的方法.在我们的项目中，我们将SQLAlchemy与MySQL结合使用，并且几次遇到了可怕的错误:

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，'尝试获取锁时发现死锁；尝试重新开始交易".

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.

我已经开始编写一个装饰器来执行此操作，但是我不知道如何在失败之前保存会话状态，并在失败之​​后重试相同的事务? (由于SQLAlchemy要求在引发异常时进行回滚)

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)

到目前为止我的工作

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_DEADLOCK:
                    logger.error('Deadlock detected. Trying sql transaction once more. Attempts count: %s'
                                 % (attempt_count + 1))
                else:
                    raise
            attempt_count += 1
    return wrapper

推荐答案

使用外部的Session不能真正做到这一点. Session将必须在内部支持此功能.这将涉及保存大量私有状态，因此这可能不值得您花费时间.

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.

我完全放弃了大多数ORM内容，转而使用较低级别的SQLAlchemy Core接口.使用该(甚至任何dbapi接口)，您可以轻松地使用retry_on_deadlock_decorator装饰器(请参见上面的问题)来制作可重试的db.execute包装器.

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)

而不是

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

你做

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

如果发生死锁，它将自动重试.

which will retry automatically if a deadlock happens.

这篇关于重试MySQL/SQLAlchemy的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！