SQLite DB (with WAL) locked when preparing a "select" statmement - why?

Question

I am seeing my reads getting blocked by the writes to the database which is in WAL mode - I'm stumped as to why.

My setup:

• SQLite3 database, journal_mode=WAL, synchronous=NORMAL
• Mulitple C++ processes (3 to be exact) use the database - Any method within these process open and close their own non-shared connection with sqlite3_open_v2.
• Methods that are inserting data open the db in SQLITE_OPEN_READWRITE mode
• Methods that read from the database (i.e. only do select statements) open the db in SQLITE_OPEN_READONLY mode

In WAL mode I believe it should be possible to have concurrent readers whilst there is a write occuring.

Yet I am seeing "database is locked" when I am preparing a select statement using sqlite3_prepare_v2

What could I be doing wrong which is causing the reader to get blocked? Am I misunderstanding what a "Read" actually is?

Any tips appreciated,

thanks :)

Answer 1

check whether you have sqlite3_reset after every sqlite3_step because this is one case that causes database is locked error. after preparing a statement with sqlite3_prepare and executing it with sqlite3_step,you need to always reset it with sqlite3_reset.

The sqlite3_reset(S) interface resets the prepared statement S back to the beginning of its program.

hope this solves your problem...!!!