问题描述
几周前,我在 SO 上发布了有关如何在 python 中锁定 sqlite3 数据库的问题:
A few weeks ago, I posted this question on SO regarding how to lock a sqlite3 database in python:
但是,我不太相信答案是否有效.或者,也许我只是误解了答案.
However, I'm not quite convinced that the answer works. Or, maybe I'm just misunderstanding the answer.
这是我遇到的情况:
- 我有一个数据库测试"
- 在数据库test"中有一个表book"
- 在book"表中有两列:title"和checked_out_by"
然后我有一个像这样工作的函数:
Then I have a function that works like this:
def checkout(title, user):
con = get_connection_from_db()
with con:
checked_out_by = get_checked_out_by(title)
if checked_out_by == '': # If NOT checked out:
checkout(title, user)
print user, "checked out", title
elif checked_out_by == 'user':
print user, "already got it"
else:
print user, "can't check it out because", checked_out_by, "has it!"
所以 checkout() 函数首先验证这本书没有被签出,如果是,则签出这本书.请注意,我使用了推荐的with con:"技巧来确保一切都是事务性的、愉快的和共情的.
So the checkout() function first verifies that the book is NOT checked out, and, if so, checks out the book. Note that I'm using the recommended "with con:" trick to ensure that everything is transactional and happy and copacetic.
然而,我运行了一堆并发测试并发现了问题.具体来说,当我同时运行以下两个调用时:
However, I ran a bunch of concurrency tests and found problems. Specifically, when I run the two following calls concurrently:
checkout('foo', 'steve')
checkout('foo', 'tim')
输出表明它不能正常工作.我希望看到以下两个可能的输出之一:
The output indicates that it doesn't work quite right. I expect to see one of the two following possible outputs:
steve checked out foo
tim can't check it out because steve has it!
或:
tim checked out foo
steve can't check it out because tim has it!
但偶尔,我会得到这样的输出:
But occasionally, I'll get this output:
tim checked out foo
steve checked out foo
我认为with con:"技巧可以确保我的数据库调用捆绑在一起.有人可以向我解释我是否/是如何弄错的吗?如果是这样,有没有办法让这个工作?
I thought the 'with con:' trick would ensure that my DB calls would be bundled together. Can someone explain to me if/how I got this wrong? If so, is there a way to make this work?
推荐答案
'with con' 不是这里想要的.(或者这个线程锁垃圾)
'with con' is NOT what is wanted here. (or this thread locking rubbish)
要获得特定时间段的独占访问权限(不仅仅是在进行单个查询/交易时),您需要执行以下操作;
To get exclusive access for a specific period (not just while an individual query/trasaction is taking place) you need to do;
con = sqlite3.connect()
con.isolation_level = 'EXCLUSIVE'
con.execute('BEGIN EXCLUSIVE')
#exclusive access starts here. Nothing else can r/w the db, do your magic here.
con.commit()
con.close()
希望这能让某人免于我刚刚经历的搜索/实验!
Hopefully this saves someone from the searching/experimenting i've just been through!
请记住,在您运行 begin exclusive 之前它不是独占的,并且在您关闭(或运行 commit,我认为)之前它将保持独占.如果您不确定,您可以随时使用 python 解释器/CL sqlite3 应用程序进行测试.
Remember it's not exclusive until you run begin exclusive, and it will stay exclusive until you close (or run commit, i think). You can always test w/ the python interpreter / CL sqlite3 app if you aren't sure.
这篇关于在 Python 中锁定 sqlite3 数据库(重新要求澄清)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!