15

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?

Community
  • 1
  • 1
Stephen Gross
  • 5,274
  • 12
  • 41
  • 59
  • Would opening the connection with isolation_level=EXCLUSIVE solve this problem (http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions)? – Stephen Gross Jan 30 '12 at 21:36

2 Answers2

45

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

hops
  • 581
  • 5
  • 5
  • Just to clarify: note that you _can_ use 'with con', such that it wraps everything in the "exclusive access" part, and then remove con.commit() - since that happens automatically at the end of the with block. You do still need the two rows before, which set the isolation level and begin the exclusive part. Also note that if you are keeping the connection open, you probably want to set the isolation_level to not be EXCLUSIVE in many cases. – Moot Jul 30 '18 at 23:57
  • 2
    This seems to work even without the `con.isolation_level = 'EXCLUSIVE'` line. – EliadL Feb 04 '20 at 10:37
-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!"
jcollado
  • 39,419
  • 8
  • 102
  • 133
  • That's an interesting trick. Ideally, I'd like to know how to acquire an exclusive read/write lock so that I don't have to do that. Is this possible? – Stephen Gross Jan 30 '12 at 21:27
  • Thanks for the info on lock; do you know how to use it properly in sqlite3 in Python? – Stephen Gross Jan 30 '12 at 21:54
  • Since `threading.Lock` objects can be used as a context manager and context managers can be nested, I've updated my answer with an example that just adds a new context manager to have a global database lock in your program. – jcollado Jan 30 '12 at 22:39
  • Looking at your example, it looks like the lock will only apply to invocations of the checkout() function. But I want the lock to apply to all operations on the database itself. Will your example accomplish that? – Stephen Gross Jan 31 '12 at 00:42
  • 1
    No, it won't. You'll need to use `db_lock` in every function in which you need to limit the number of readers to just one. If you need that in many places, I suggest to use a decorator to avoid having too much boilerplate code. – jcollado Jan 31 '12 at 07:03
  • This post needs more clarification that this only works for threads that share the Python interpreter state. If multiple interpreter instances exist in the same process, or multiple processes exist then this code will not work as expected. – kmcguire Aug 05 '15 at 21:36