zelusp zelusp - 1 month ago 15
Python Question

How do I lock an entire SQLite connection (locked read + locked write)?

I have an sqlite3 db that is being accessed concurrently. I have

ClientA
that reads the state of some table (Column1 has rows A, B, C) and needs to update the table with new letters of the alphabet. If
ClientB
reads the state of the table before
ClientA
updates the table (say with the new letter D), then it's possible that both clients could (and in my case do) write D to the table - such that Column1 becomes A, B, C, D, D. But I need to ensure Column1 only has unique letters!

How do I lock the db connection so that its read AND write operations get exclusive access so that Column1 doesn't accidentally change states between some other read-write cycle?

It's hard to find anything about "locking a sqlite read" online because everyone seems more interested in unlocking the db. The following doesn't seem to give
con
's read operations exclusive access

con = sqlite3.connect(db, isolation_level='EXCLUSIVE', timeout=10)


Related:


Answer

Just setting the isolation level is not enough. You must also place your exclusive statements in transaction:

con = sqlite3.connect(db, isolation_level='EXCLUSIVE', timeout=10)
con.execute('BEGIN EXCLUSIVE')
# Exclusive access here; no other transaction can access the database.
# 1. Check for presence of letter
# 2. Add the letter if it doesn't exist
con.commit()
con.close()
Comments