[odb-users] Regarding Sqlite WAL model support in ODB

[Boris Kolpackov]

Hi Steve,

Steve Chen <steve.chen at ringcentral.com> writes:

> m_db = std::shared_ptr<odb::sqlite::database>(
>  new odb::sqlite::database(
>    dbFile, 
>    SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_WAL));

I don't think the SQLITE_OPEN_WAL flag is meant to be passed to
sqlite2_open_v2(). Quoting SQLite C API Reference[1]:

[...]
#define SQLITE_OPEN_PRIVATECACHE     0x00040000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_WAL              0x00080000  /* VFS only */

As discussed in the SQLite WAL page[2], the way to convert a database to
the WAL mode is with a PRAGMA:

m_db->execute ("PRAGMA journal_mode=WAL");

Also, that promise from the WAL doc you have quoted:

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

Sounds a lot better than the reality. In particular, check the "Isolation
And Concurrency" section in the Isolation in SQLite[3].

Finally, note that ODB supports SQLite's unlock notification feature which
is another way to improve concurrency in SQLite.

[1] https://www.sqlite.org/capi3ref.html#SQLITE_OPEN_AUTOPROXY
[2] https://www.sqlite.org/wal.html
[3] https://www.sqlite.org/isolation.html
[4] https://www.sqlite.org/unlock_notify.html

Boris