First, avoid concurrent access to sqlite database files. Concurrency is one of sqlite’s weak points and if you have a highly concurrent application, consider using another database engine.
If you cannot avoid concurrency or drop sqlite, wrap your write transactions in
BEGIN IMMEDIATE; …
END;. The default transaction mode in sqlite is
DEFERRED which means that a lock is acquired only on first actual write attempt. With
IMMEDIATE transactions, the lock is acquired immediately, or you get
SQLITE_BUSY immediately. When someone holds a lock to the database, other locking attempts will result in
SQLITE_BUSY is something you have to decide for yourself. For many applications, waiting for a second or two and then retrying works quite all right, giving up after
n failed attempts. There are sqlite3 API helpers that make this easy, e.g.
sqlite3_busy_timeout() but it can be done manually as well.
You could also use OS level synchronization to acquire a mutex lock to the database, or use OS level inter-thread/inter-process messaging to signal when one thread is done accessing the database.
If you want to use Python’s automatic transaction handling, leave
isolation_level at its default value, or set it to one of the three levels.
If you want to do your own transaction handling, you have to prevent Python from doing its own by setting