Django + MySQL: savepoint does not exist?

Question

I'm running a small Web app on a shared hosting plan. I have a "worker function" which contains an infinite loop; the loop checks a task queue in the DB for new things to do. This necessitated using @transaction.commit_manually in order to defeat Django's caching and get up-to-date info on every iteration.

I recently implemented DB logging, and therefore needed to introduce using savepoints to my worker function - this way, if anything goes awry, I can rollback to a good savepoint, log to the database, and carry on until I reach the final transaction.commit()

Now, unlike my development server, the production server gives me the error:

 DatabaseError: (1305, 'SAVEPOINT s140364713719520_x1 does not exist')

pointing to a transaction.savepoint_rollback() call in an except block (see source below). The dev server has no such problems; and the production server happily yields savepoint IDs if I type transaction.savepoint() in an interactive shell.

This is the outline of my code, if it'd be of any help; I've tried to keep it concise.

If there's any benevolent Python gurus out there, please help me. I'm getting really frustrated over this, although I think I'm doing a fairly good job at handling it in a calm manner.

Answer 1

I had the same occasionally recurring nasty error:

OperationalError: (1305, 'SAVEPOINT {{name}} does not exist')

and Googling didn't make it clearer, except that it's sort of "normal" concurrency issue. So it's non-deterministic and hard to reproduce in development environment.

Luckily I was able to localise it by making the production app logging sufficiently verbose.

Cause

In MySQL there're some operations that could implicitly end a transaction:

• DDL statement (e.g. CREATE TABLE, ALTER TABLE, etc.) results in implicit commit. It's well-known that DDLs in MySQL aren't transactional,
• OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction') and OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') result in implicit rollback.

So the second case results indeed in somewhat "normal". It could be represented by the following code:

# db is an example database connection object, which 
#   - supports nested (stacked) transactions, 
#   - has autocommit on.

db.begin() # START TRANSACTION
try:
  # no-conflict op
  db.update() 

  db.begin() # SAVEPOINT sp1
  try:
    # conflict op, 
    # e.g. attempt to change exclusively locked rows by another transaction
    db.update() 

    db.commit() # RELEASE SAVEPOINT sp1
  except:
    # Everything interesting happens here:
    #   - the change attempt failed with OperationalError: (1213, 'Deadlock...'),
    #   - the transaction is rolled back with all the savepoints,
    #   - next line will attempt to rollback to savepoint which no longer exists,
    #   - so will raise OperationalError: (1305, 'SAVEPOINT sp1 does not exist'),
    #   - which will shadow the original exception.

    db.rollback() # ROLLBACK TO SAVEPOINT sp1
    raise

  db.commit() # COMMIT 
except:
  db.rollback() # ROLLBACK
  raise

Update

Note that the above about exception shadowing was said for Python 2. Python 3 implements exception chaining and in case of a deadlock the traceback will have all the relevant information.