Django + MySQL：保存点不存在？ [英] Django + MySQL: savepoint does not exist?

问题描述

我正在共享托管计划上运行一个小型Web应用程序。
我有一个工作者函数，它包含一个无限循环;该循环将检查数据库中的任务队列以执行新操作。为了击败Django的缓存并获取每一次迭代的最新信息，必须使用 @ transaction.commit_manually 。

我最近实施了DB日志记录，因此需要使用保存点对我的worker函数进行介绍 - 这样，如果有任何问题，我可以回滚到一个好的保存点，登录数据库，继续进行，直到达到最终现在，与我的开发服务器不同，生产服务器给了我错误： c $ c $数据库错误：（1305，'SAVEPOINT s140364713719520_x1不存在'）

 $ p 
 
 
指向 transaction.savepoint_rollback（）调用除块（参见下面的源）。 dev服务器没有这样的问题;如果在交互式shell中键入 transaction.savepoint（），则生产服务器会愉快地生成保存点ID。
 
 
 
 < a href =http://pastebin.com/FvWPbP20 =nofollow>这是我的代码的大纲，如果有任何帮助;我试图保持简洁。
 
 
 
如果有任何仁慈的Python专家，请帮助我。我真的很沮丧，虽然我认为我在做一个很好的工作，以平静的方式处理它。
解决方案
我有同样的偶然的错误
  OperationalError：（1305，'SAVEPOINT {{name}}不存在'）
  
 Googling没有让它更清楚，除了它是一种正常并发问题。所以在开发环境中是非确定性的，很难重现。
 
 
 
幸运的是，它是本地化的，所以我使生产应用程序日志足够详细。 
 
 
 
在MySQL中有一些可以隐式结束交易的操作：
 
 
 
 
 
 DDL语句（例如 CREATE TABLE ， ALTER TABLE 等）导致隐式提交。众所周知，MySQL中的DDL不是事务性的，
 
 
  OperationalError：（1213，尝试锁定时发现死锁;尝试重新启动事务）和 OperationalError：（1205，超过锁定等待超时;尝试重新启动事务）导致隐式回滚。
 
 
 
 
 
所以第二种情况确实有些正常。它可以由以下代码表示：
 
 ＃db是一个示例性数据库连接对象，其中
＃ - 支持嵌套（堆叠）交易，
＃ - 具有自动提交功能。 
 
 db.begin（）＃START TRANSACTION 
 try：
＃no-conflict op 
 db.update（）
 
 db。 begin（）＃SAVEPOINT sp1 
 try：
＃conflict op，
＃eg尝试通过另一个事务更改专门锁定的行
 db.update（）
 
 db.commit（）＃RELEASE SAVEPOINT sp1 
除了：
＃一切有趣的事情发生在这里：
＃ - 更改尝试失败与OperationalError：（1213，'死锁...'），
＃ - 事务回滚与所有的保存点，
＃ - 下一行将尝试回滚到不再存在的保存点，
＃ - 将会引发OperationalError：（1305，'SAVEPOINT sp1不存在'），
＃ - 将影响原始异常。 
 
 db.rollback（）＃ROLLBACK TO SAVEPOINT sp1 
 raise 
 
 db.commit（）＃COMMIT 
除了：
 db。 rollback（）＃ROLLBACK 
 raise 
  
 
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


                        
这篇关于Django + MySQL：保存点不存在？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！