How do I prevent SQLite database locks?

Question

From SQLite FAQ I've known that:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

So, as far as I understand I can: 1) Read db from multiple threads (SELECT) 2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

But, I read about Write-Ahead Logging that provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Finally, I've got completely muddled when I found it, when specified:

Here are other reasons for getting an SQLITE_LOCKED error:

• Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
• Trying to write to a table while a SELECT is active on that same table.
• Trying to do two SELECT on the same table at the same time in a multithread application, if sqlite is not set to do so.
• fcntl(3,F_SETLK call on DB file fails. This could be caused by an NFS locking issue, for example. One solution for this issue, is to mv the DB away, and copy it back so that it has a new Inode value

So, I would like to clarify for myself, it is necessary to avoid the lock? Can I read and write at the same time from two different threads? Thanks.

Answer 1

That page you linked, besides being quite old, talks about accesses from the same process through the same database connection (or through multipe connections in shared cache mode, which you should not use).

When not in WAL mode, multiple connections can read from the same database, but a writing transaction is exclusive, i.e., no other readers or writers are allowed.

In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed.

• Justin Dearing: What is wrong with shared cache mode? I was able to get multiple python threads in the same process to write to a table with it via a joblb Parallel for loop. I had locking issues until a set isolation_level=None though. Without shared cache mode, how do i share instances between threads?
• CL.: The documentation describes the disadvantages. It is especially dangerous if you don't know how to avoid deadlocks.
• infocyde: Coming in very late to the party, but this link is useful. manski.net/2012/10/sqlite-performance