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.

Answer 2

If you check out the docs on Django relating to Savepoints it is mentioned that not all MySQL Storage Engines support them. Basically MyISAM doesn't handle transactions so you don't get to rollback and InnoDB does. So I would check that you dev and prod tables are both using the same storage engine type. You can check this by running:

SHOW CREATE TABLE mytable