python - Retry on deadlock for MySQL / SQLAlchemy -

i have searched quite time , can't found solution problem. using sqlalchemy in conjunction mysql our project , encounter several time dreaded error:

1213, 'deadlock found when trying lock; try restarting transaction'.

we try restart transaction @ 3 times in case.

i have started write decorator don't know how save session state before fail , retry same transaction after ? (as sqlalchemy requires rollback whenever exception raised)

my work 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 e:                 if any(msg in e.message msg in lock_messages_error) \                         , 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 

did use code this?

try:        perform table transaction       break  except:       rollback       delay       try again perform table transaction  

the way handle deadlocks write code expect them. isn't difficult if database code written. can put try/catch around query execution logic , deadlock when errors occur. if catch one, normal thing attempt execute failed query again.

usefull links:

• how cope deadlocks
• understanding autocommit
• property mysqlconnection.autocommit