使用python sqlite在同一事务中选择和更新 [英] Select and update in same transaction with python sqlite

问题描述

我想使用Python在同一个sqlite3数据库上对一个select和一个更新查询进行一次事务，以确保当我没有对第一个线程进行更新查询时，没有其他线程可以进行选择.

I would like to do one transaction with a select and an update query on the same sqlite3 database with Python, to make sure no other thread can make a select while I have not done the update query with the first thread.

基础很简单，有点像要排队的工作，我想确保多个线程不能获得相同的工作ID.

The base is very simple, kind of a queue of jobs to do, and I want to make sure multiple thread can't get the same job id.

with sqlite3.connect('database.sqlite') as db:
    db_cursor = db.cursor()
    db_cursor.execute("SELECT id FROM mytable WHERE status=?", 'todo')
    myrow = db_cursor.fetchone()
    if myrow :
        id = myrow[0]
        db_cursor.execute("UPDATE mytable SET status=? WHERE id=?", ['done', id])
        # id is used after that.

隔离级别参数是否可以解决?隔离在释放连接后还是仅在"fetchone"功能之前有效?

Would the isolation-level parameter be a solution? Does the isolation work while I have released the connection, or only up to the "fetchone" function?

谢谢.

推荐答案

您当然可以在此处使用锁定或事务，但是您可能并不需要这些.

You could certainly use locking or transactions here, but you might not really need any of that.

只需确保在接受工作时该工作仍然可用:

Just make sure the job is still available when you take it:

with sqlite3.connect('database.sqlite') as db:
    while 1:
        db_cursor = db.cursor()
        db_cursor.execute("SELECT id FROM mytable WHERE status=?", 'todo')
        # ^^^^^^^ Consider adding LIMIT 1 here, you don't need all rows if you only
        # use one.

        myrow = db_cursor.fetchone()
        if not myrow :
            break

        id, = myrow
        accepted = db_cursor.execute("UPDATE mytable SET status=? WHERE id=? AND status=?", ['done', id, 'todo']) 
        # ^^^^^^ This will return the number of rows updated. 
        # Note that we only update if the status is still 'todo', so if we get 1 updated 
        # row, we're sure no one else took our job. This works because UPDATE is atomic.

        # A performance improvement would be to select multiple rows above, 
        # and try another one (maybe at random) if you didn't get your "first pick"

        if not accepted: 
            # Whoops this job was taken! Try again and get another one
            continue

        # This job is yours, do your thing!

请注意，这在高竞争情况下可能无法很好地执行.像往常一样:首先尝试一个简单的解决方案，一旦发现瓶颈就进行迭代(在您的情况下:迭代意味着使用实际的任务代理).

Note that this might not perform very well under high contention. As usual: try a simple solution first, iterate once you've identified bottlenecks (in your case: iterating means using an actual task broker).

这篇关于使用python sqlite在同一事务中选择和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！