Sqlite WAL blocks despite doc sais it doesn't

0
votes

sqlite3_step() frequently returns SQLITE_LOCKED, despite doc saying:

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

I have two threads, each has one connection. WAL is turned on, shared cache is turned on. Multithreading mode is chosen (SQLITE_OPEN_NOMUTEX passed when opening). When this problem occurs, the locked thread is only reading (other thread is writing). Did I miss something?

Thank You!

1
multithreadingsqlite
Did you see the faq (question 5 and 6( on threading? sqlite.org/faq.html#q5 – Jay
Yes, but sqlite.org/wal.html implies that with WAL turned on an arbitrary quantity of reads and 1 write can go concurrently without blocking. – Zsolt Szatmari
I don't see anything that says "Reading and writing can proceed concurrently" on different threads. They only mention checkpoints on separate threads – Jay

1 Answers

0
votes

Turns out my understanding was true, but the locking issues arose from the shared cache, I needed to turn that off. This way there are no SQLITE_LOCKED errors. There are still SQLITE_BUSY errors sometimes when writing, in that case I need to retry, or in the case of being in the middle of a transaction, rollback and retry transaction.

Hope this helps someone!