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?
Asked
Active
Viewed 8,411 times
2 Answers
10
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.
Daniel Novak
- 2,746
- 3
- 28
- 37
-
"Be sure to run the update in an transaction, so that inconsistency in data will not happen"... According to Sqlite docs "No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes." (ref: http://www.sqlite.org/lang_transaction.html) – samus Mar 20 '13 at 15:20
-
That's true - but it's only a single command within that transaction. Let's say you have two rows and would like to remove value from first row and add it to the second row. You select the value, remove it and then add it to the value in second row. If you wouldn't execute this in a single transaction then these calls would be run in separate transactions. Let's say something goes wrong (power goes out, application exception) after you have selected and removed the value from the first row, but have not yet added it to the second table. This is now an inconsistent state - data is lost. – Daniel Novak Mar 20 '13 at 22:44
-
I took "update" literally, as in a single UPDATE statement. Yea, your totally right, and I've been thinking about these cases the past two weeks while trying to sync local and remote id's/foreign-keys between tablet app and remote DB - my brain hurts. – samus Mar 22 '13 at 15:28
4
SQLite supports a couple different isolation levels that are selectable at compile time and runtime.
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:
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.
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.
-
Concurrency issues don't disappear if you share a connection globally. Of course you should not do that. You want to talk to a SQL DBMS, you open a connection, you begin a transaction, you commit, you close the connection. Anything else is a) wrong or b) comes with so many caveats that I have rarely met a developer who could explain them all. You might want to rethink the design of ORMLite, as I've not even been able to find a transaction control API. The second part of your answer is probably wrong too, "read uncomitted" is NOT the same as "read committed". – Dec 20 '11 at 14:21
-
Thanks for sharing and thanks for the -1. Is there a correction you wanted to make or are you just being contrary for some other reason? – Gray Dec 20 '11 at 14:23
-
1Oh, and for the record I DO know about Android and SQLite and I do know about database stuff. – Gray Dec 20 '11 at 14:23
-
Ok, there was a typo that I fixed. Thanks for that. In terms of sharing a connection, you don't know what you are talking about. SQLite does internal locking. I bet you didn't look at any of the sources that I quoted did you? And you admit that you don't know about SQLite. Are you for real or is this some sort of joke? – Gray Dec 20 '11 at 14:42
-
1SQLite is an ACID compliant database with database-level shared/exclusive locking for concurrency control. It does not have range locks (locking the rows of a SQL restriction, for example), so its default isolation level is apparently Read Committed (phantoms possible). This answers the original question and has nothing to do with connection management. – Dec 20 '11 at 14:53
-
And it seems like I found the ORMLite transaction control API: TransactionManager.callInTransaction(). – Dec 20 '11 at 14:55
-
You need to do some reading about Android databases. Here's one link: http://stackoverflow.com/questions/3610147/android-sqlite-concurrency-without-exceptions and here's another one: http://www.touchlab.co/blog/android-sqlite-locking/ – Gray Dec 20 '11 at 15:00
-
Oh and yes, ORMLite has the `TransactionManager`. And outside of Android it does normal connection management -- i.e. it leaves it up to the `ConnectionSource`. It's just the Android `ConnectionSource` implementation where it has to keep a single connection to the database. – Gray Dec 20 '11 at 15:02
-
Thanks, I didn't expect that there would be additional locking at the Android level. It's not really surprising that SQLiteHelper is crap, given the name. The short answer then is that SQLite on Android, if you use their recommended API, is effectively in isolation mode Serializable, implemented with JVM synchronization at the connection level. There is no connection pooling or sharing. – Dec 20 '11 at 15:17