Android: SQLite (ORMLite) transaction isolation le

Question

I'm using ORMLite in my Android project. I know that Sqlite takes care of the file level locking. Many threads can read, one can write. Locks prevent more than one writing. Could anybody please explain what will happen in case one thread is updating some record and another thread is trying to read this record? Will the thread (that is trying to read) get out-of-date data? Or will it be locked until the first thread completes its write-operation? As I know there are 4 transaction isolation levels: Serializable, Repeatable read, Read committed, Read uncommitted. And is there any way to change it in SQLite or ORMLite?

Answer 1

SQLite has 5 different lock levels - http://www.sqlite.org/lockingv3.html : unlocked, shared, reserved, pending, exclusive. The important lock for this question is the Shared lock:

"SHARED - The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active."

The locks are table-level (so while doing something with a single row in the DB - the whole table is locked).

So while you are selecting data, no other process is allowed to alter the data. The lock steps for reading data are: UNLOCKED→PENDING →SHARED →UNLOCKED (you can run selects in a transaction). So a situation where you are selecting something and someone will alter the data can not happen.

Your question is what happens if you are updating the database and do a select on the same table. In autocommit mode the lock mechanism for write/update is: UNLOCKED →PENDING →SHARED →RESERVED →PENDING →EXCLUSIVE →UNLOCKED. While in the Exclusive lock, no new readers (connections) can connect to the database. Only one EXCLUSIVE lock may exists at a single time. SQLite will then wait until all other PENDING locks from reading connections are released and will prevent any new. At this time, it will begin writing the data.

So, my answer would be - as long as the update process is not finished, your other process will get old data of course. Be sure to run the update in an transaction, so that inconsistency in data will not happen. SQLite is ACID compliant, so a situation where you get partially updated and incosisten data should not happen.

A great book on this is "The Definitive Guide to SQLite", especially the Transactions chapter.

Answer 2

SQLite supports a couple different isolation levels that are selectable at compile time and runtime.

http://www.sqlite.org/threadsafe.html

I assume that Android's SQLite is in Serialized mode by default. In terms of multi-threaded access, it does read/write locking at the file system level to allow multiple readers but only one writer at a time:

http://www.sqlite.org/faq.html#q5

However, ORMLite recommends strongly and tried hard to maintain a single connection to the database so the locking issues may not be relevant.

More specifically, if one thread is updating the record and another thread reading then it is a race condition. Either the reading thread will get the record prior to update or after the update has completed. However, the reader will not get partially updated data. But I suspect you knew that.

In terms of isolation levels supported by SQLite, the default is Serialized as mentioned but it looks like it supports read-uncommitted at least to some degree.

http://www.sqlite.org/sharedcache.html

You can use ORMLite's Dao.executeRaw() method to enable it:

dao.executeRaw("PRAGMA read_uncommitted = True;");

However, I don't have any experience with this nor am I sure if it will give you consistent view of the row that was updated if it is accessed multiple times in the same query. It may have more to do with consistent views during write transactions.

After reading this similar question, I'm not sure it is possible to be guaranteed the same view of a row in a table if it is accessed multiple times the in the same query.