How do I prevent SQLite database locks?

Question

From sqlite FAQ I've known that:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

So, as far as I understand I can: 1) Read db from multiple threads (SELECT) 2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

But, I read about Write-Ahead Logging that provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Finally, I've got completely muddled when I found it, when specified:

Here are other reasons for getting an SQLITE_LOCKED error:

• Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
• Trying to write to a table while a SELECT is active on that same table.
• Trying to do two SELECT on the same table at the same time in a multithread application, if sqlite is not set to do so.
• fcntl(3,F_SETLK call on DB file fails. This could be caused by an NFS locking issue, for example. One solution for this issue, is to mv the DB away, and copy it back so that it has a new Inode value

So, I would like to clarify for myself, when I should to avoid the locks? Can I read and write at the same time from two different threads? Thanks.

Answer 1

For those who are working with Android API:

Locking in SQLite is done on the file level which guarantees locking of changes from different threads and connections. Thus multiple threads can read the database however one can only write to it.

More on locking in SQLite can be read at SQLite documentation but we are most interested in the API provided by OS Android.

Writing with two concurrent threads can be made both from a single and from multiple database connections. Since only one thread can write to the database then there are two variants:

1. If you write from two threads of one connection then one thread will await on the other to finish writing.
2. If you write from two threads of different connections then an error will be – all of your data will not be written to the database and the application will be interrupted with SQLiteDatabaseLockedException. It becomes evident that the application should always have only one copy of SQLiteOpenHelper(just an open connection) otherwise SQLiteDatabaseLockedException can occur at any moment.

Different Connections At a Single SQLiteOpenHelper

Everyone is aware that SQLiteOpenHelper has 2 methods providing access to the database getReadableDatabase() and getWritableDatabase(), to read and write data respectively. However in most cases there is one real connection. Moreover it is one and the same object:

SQLiteOpenHelper.getReadableDatabase()==SQLiteOpenHelper.getWritableDatabase()

It means that there is no difference in use of the methods the data is read from. However there is another undocumented issue which is more important – inside of the class SQLiteDatabase there are own locks – the variable mLock. Locks for writing at the level of the object SQLiteDatabase and since there is only one copy of SQLiteDatabase for read and write then data read is also blocked. It is more prominently visible when writing a large volume of data in a transaction.

Let’s consider an example of such an application that should download a large volume of data (approx. 7000 lines containing BLOB) in the background on first launch and save it to the database. If the data is saved inside the transaction then saving takes approx. 45 seconds but the user can not use the application since any of the reading queries are blocked. If the data is saved in small portions then the update process is dragging out for a rather lengthy period of time (10-15 minutes) but the user can use the application without any restrictions and inconvenience. “The double edge sword” – either fast or convenient.

Google has already fixed a part of issues related to SQLiteDatabase functionality as the following methods have been added:

beginTransactionNonExclusive() – creates a transaction in the “IMMEDIATE mode”.

yieldIfContendedSafely() – temporary seizes the transaction in order to allow completion of tasks by other threads.

isDatabaseIntegrityOk() – checks for database integrity

Please read in more details in the documentation.

However for the older versions of Android this functionality is required as well.

The Solution

First locking should be turned off and allow reading the data in any situation.

SQLiteDatabase.setLockingEnabled(false);

cancels using internal query locking – on the logic level of the java class (not related to locking in terms of SQLite)

SQLiteDatabase.execSQL(“PRAGMA read_uncommitted = true;”);

Allows reading data from cache. In fact, changes the level of isolation. This parameter should be set for each connection anew. If there are a number of connections then it influences only the connection that calls for this command.

SQLiteDatabase.execSQL(“PRAGMA synchronous=OFF”);

Change the writing method to the database – without “synchronization”. When activating this option the database can be damaged if the system unexpectedly fails or power supply is off. However according to the SQLite documentation some operations are executed 50 times faster if the option is not activated.

Unfortunately not all of PRAGMA is supported in Android e.g. “PRAGMA locking_mode = NORMAL” and “PRAGMA journal_mode = OFF” and some others are not supported. At the attempt to call PRAGMA data the application fails.

In the documentation for the method setLockingEnabled it is said that this method is recommended for using only in the case if you are sure that all the work with the database is done from a single thread. We should guarantee than at a time only one transaction is held. Also instead of the default transactions (exclusive transaction) the immediate transaction should be used. In the older versions of Android (below API 11) there is no option to create the immediate transaction thru the java wrapper however SQLite supports this functionality. To initialize a transaction in the immediate mode the following SQLite query should be executed directly to the database, – for example thru the method execSQL:

SQLiteDatabase.execSQL(“begin immediate transaction”);

Since the transaction is initialized by the direct query then it should be finished the same way:

SQLiteDatabase.execSQL(“commit transaction”);

Then TransactionManager is the only thing left to be implemented which will initiate and finish transactions of the required type. The purpose of TransactionManager – is to guarantee that all of the queries for changes (insert, update, delete, DDL queries) originate from the same thread.

Hope this helps the future visitors!!!

Answer 2

Not specific to SQLite:

1) Write your code to gracefully handle the situation where you get a locking conflict at the application level; even if you wrote your code so that this is 'impossible'. Use transactional re-tries (ie: SQLITE_LOCKED could be one of many codes that you interpret as "try again" or "wait and try again"), and coordinate this with application-level code. If you think about it, getting a SQLITE_LOCKED is better than simply having the attempt hang because it's locked - because you can go do something else.

2) Acquire locks. But you have to be careful if you need to acquire more than one. For each transaction at the application level, acquire all of the resources (locks) you will need in a consistent (ie: alphabetical?) order to prevent deadlocks when locks get acquired in the database. Sometimes you can ignore this if the database will reliably and quickly detect the deadlocks and throw exceptions; in other systems it may just hang without detecting the deadlock - making it absolutely necessary to take the effort to acquire the locks correctly.

Besides the facts of life with locking, you should try to design the data and in-memory structures with concurrent merging and rolling back planned in from the beginning. If you can design data such that the outcome of a data race gives a good result for all orders, then you don't have to deal with locks in that case. A good example is to increment a counter without knowing its current value, rather than reading the value and submitting a new value to update. It's similar for appending to a set (ie: adding a row, such that it doesn't matter which order the row inserts happened).

A good system is supposed to transactionally move from one valid state to the next, and you can think of exceptions (even in in-memory code) as aborting an attempt to move to the next state; with the option to ignore or retry.

Answer 3

You're fine with multithreading. The page you link lists what you cannot do while you're looping on the results of your SELECT (i.e. your select is active/pending) in the same thread.