Sqlite Simultaneous Reading and Writing

SQLite simultaneous reading and writing

On Android 3.0 and higher SQLiteDatabases support WAL mode (write-ahead logging):

When write-ahead logging is not enabled (the default), it is not
possible for reads and writes to occur on the database at the same
time. Before modifying the database, the writer implicitly acquires an
exclusive lock on the database which prevents readers from accessing
the database until the write is completed.

In contrast, when write-ahead logging is enabled, write operations
occur in a separate log file which allows reads to proceed
concurrently. While a write is in progress, readers on other threads
will perceive the state of the database as it was before the write
began. When the write completes, readers on other threads will then
perceive the new state of the database.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#enableWriteAheadLogging()

To start a transaction in WAL mode use beginTransactionNonExclusive() instead of beginTransaction().
While beginTransaction() starts a transaction in EXCLUSIVE mode, beginTransactionNonExclusive() starts one in IMMEDIATE mode

• EXCLUSIVE mode uses exclusive locks (http://www.sqlite.org/lockingv3.html#excl_lock) meaning no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete
• IMMEDIATE mode uses reserved locks (http://www.sqlite.org/lockingv3.html#reserved_lock) meaning no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE, other processes can continue to read from the database, however.

In simpler words: call beginTransactionNonExclusive() for IMMEDIATE mode and we can read while another thread is writing (the state before the write transaction started because we won't use read_uncommitted connections -> http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Dirty_reads).

Can I read and write to a SQLite database concurrently from multiple connections?

I collected information from various sources, mostly from sqlite.org, and put them together:

First, by default, multiple processes can have the same SQLite database open at the same time, and several read accesses can be satisfied in parallel.

In case of writing, a single write to the database locks the database for a short time, nothing, even reading, can access the database file at all.

Beginning with version 3.7.0, a new “Write Ahead Logging” (WAL) option is available, in which reading and writing can proceed concurrently.

By default, WAL is not enabled. To turn WAL on, refer to the SQLite documentation.

Strategies for concurrent read/writing and reading in SQLite

Use WAL mode. It supports concurrent readers and one writer.

Concurrent writing with sqlite3

The sqlite library will lock the database per process when writing to the database and each process will wait for the lock to be released to get their turn.

The database doesn't need to be written to until commit time however. You are using the connection as a context manager (good!) so the commit takes place after your loop has completed and all insert statements have been executed.

If your database has uniqueness constraints in place, it may be that the commit fails because one process has already added rows that another process conflicts with.

SQLite suitable for concurrent reading?

No problem. The concurrent reading/writing will actually be serialized by SQLite so you don't need to care about it.

For details : http://www.sqlite.org/lockingv3.html

---

Related Topics