Android: SQLite (ORMLite) transaction isolation levels

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.