mysql不存在则保存存,Django + MySQL：保存点不存在？

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.

解决方案

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 it was localized, so I made the production app log enough verbose about it.

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 exemplary 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