How do I lock an entire SQLite connection (locked

Question

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:

• File locking - read then write whilst locked
• How perform SQLite query with a data reader without locking database?

Answer 1

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()