Locking a sqlite3 database in Python (re-asking fo

问题:

A few weeks ago, I posted this question on SO regarding how to lock a sqlite3 database in python:

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.

Here's the situation I ran into:

• I've got a database "test"
• In database "test" there is one table "book"
• In table "book" there are two columns: "title", and "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!"

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!

OR:

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

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?

回答1:

'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!

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.

回答2:

One important thing to note is that when the database is locked, it means that it doesn't accept multiple writers. However, it does accept multiple readers..

One easy way to check if a transaction worked as expected is to write a value to the database and, after that, raise an exception before the transaction code finishes. If the value is not written, then the transaction worked fine. Otherwise, something is wrong.

Database transactions are an optimistic approach to concurrency, that is, they fail only when they're about to commit. Since it seems you're looking for a pesimistic approach, maybe you should try with threading.Lock:

import threading
db_lock = threading.Lock()

def checkout(title, user):
    with db_lock:
        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!"