选择更新查询：锁定等待超时超出错误 [英] Select for update query: Lock wait timeout exceeded error
问题描述
为了避免竞争条件，我需要在查询数据库时使用 select for update 功能，以便锁定行直到事务结束。由于Django 1.3中不存在select_for_update查询，所以我通过使用一个通过执行一个原始sql查询返回查询集的类方法来完成一个解决方法。
To avoid race conditions, I need to use select for update functionality while querying database so that it locks the row until the end of the transaction. Since select_for_update query is not present in Django 1.3, I have done a workaround it by using a class method which returns query sets by doing a raw sql query.
#models.py
class AccountDetails(models.Model):
user = models.OneToOneField(User)
amount = models.IntegerField(max_length=15,null=True,blank=True)
@classmethod
def get_locked_for_update(cls,userid):
accounts = cls.objects.raw("SELECT * FROM b2b_accountdetails WHERE user_id ="+str(userid)+" FOR UPDATE")
return accounts[0]
如何在视图中使用。
account = AccountDetails.get_locked_for_update(userid)
account.amount = account.amount - fare
account.save()
在最后一行我收到此错误：
OperationalError：（1205，超过锁定等待超时;尝试重新启动事务）
On the last line I'm getting this error:
OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')
I n dbshell，运行 save（）行：
In dbshell, after running the save() line:
mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+-----------+---------+------+----------+-----------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+-----------+---------+------+----------+-------------------------- ---------------------------------------------------------------------------+
| 51 | root | localhost | dbname | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 767 | root | localhost | dbname | Sleep | 59 | | NULL |
| 768 | root | localhost | dbname | Query | 49 | Updating | UPDATE `b2b_accountdetails` SET `user_id` = 1, `amount` = 68906 WHERE `appname_accountdetails`.`id` = 1 |
+-----+------+-----------+-----------+---------+------+----------+-------------------------- ---------------------------------------------------------------------------+
根据我的理解，应该在第一个数据更改查询（如更新，删除等）上发布锁。
According to my understanding the lock should be released on the first data-altering query like update, delete etc.
但是， save（） / code>语句被阻止并保持等待。任何想法为什么会发生这种情况？当我打电话 account.save（）时，我认为是没有选择由更新查询选择开始的上一个事务。
But the save() statement is getting blocked and is kept on wait. Any idea why this is happening? What I think is when I'm calling account.save() it's not picking up the previous transaction started by select for update query.
我错过了一些明显的东西吗？请帮助。
Am I missing something obvious? Kindly help.
推荐答案
将Django保留到这种操作的默认自动提交状态可能会导致几种错误（没有锁定数据库可以轻而易举的另一个结果）;详细信息可能取决于该特定RDBMS的RDBMS和/或Django数据库驱动程序。最好使用 @commit_on_success 或 @commit_manually 或 TransactionMiddleware 。
Leaving Django to its default autocommit-like behaviour for such an operation can easily lead to several kinds of errors (not locking the database at all could easily be another outcome); the details probably depend on the RDBMS and/or Django database driver for that particular RDBMS. It would be better to use @commit_on_success or @commit_manually or TransactionMiddleware.
这篇关于选择更新查询：锁定等待超时超出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
