How to achieve row-level locking on an in-memory SQLite db?

Question

I'm running SQLite v3.7.17 from my program in in-memory mode and using shared cache (as specified in Shared Cache And In-Memory Databases). My program is multi-threaded and all these threads access the same in-memory database.

Is there any way I can configure or use my SQLite database such that, when two threads run update query on same table (but different rows), one doesn't wait on another? That is, how can I achieve row-level locking on this in-memory db?

This should theoretically be possible as my SQLite data is not in a file (therefore filesystem writes do not apply).

Answer 1

It's not the filesystem that determines whether SQLite can lock rows. It's SQLite's architecture.

Even using write-ahead logging, you can only have one writer at a time.

Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time.

SQLite3 has a kind of table locking now, but not row locking.