Is Sqlite Database instance thread safe

Question

I have a database with some tables. I want to update the tables using multiple threads. I will use same instance of SQLiteDatabase in all threads.

Please suggest if this approach is correct. Is Sqlite database threadsafe? Can two different threads update same table for different set of values at same time.

Solution 1

[WRONG:] No, it is not thread-safe by default. You shoud use locking-related SQLiteHelper methods to provide thread safety.

[EDIT]: SQLiteDatabase class provides a locking mechanism by default (see comments) and if you are running on multithread, you don't have to consider changing anything to have thread-safety.

Search for 'thread' in this document: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

And read more on:

• http://www.touchtech.co/blog/android-sqlite-locking/
• Android threading and database locking
• Android -- SQLite + SharedPreferences, 2 Threads Simultaneous Read/Write?
• https://groups.google.com/forum/#!topic/android-developers/s3blUf7CRhU

Solution 2

The Android uses java locking mechanism to keep SQLite database access serialized. So, if multiple thread have one db instance, it always calls to the database in serialized manner and of course database is thread-safe.

If we confirm that we are using database from single thread we had option to set database internal locking disable by calling setLockingEnable(false) but this method got deprecated from API level 16 and no longer in use. if you see the implementation of this method in SQLiteDatabase class, you will find nothing written there i.e. empty method.

public void setLockingEnabled (boolean lockingEnabled)

This method now does nothing. Do not use.

One thing which we should take care of that is we should make one instance of your helper class(i.e. by making it singleton) and share same instance to multiple thread and do not call close() on database in between operation, otherwise you may get following exception:

java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase

So, do not call database.close() in between accessing the database, database will self perform close operation internally when all the operation would be finish.

Solution 3

You can control if you database is thread safe or not by setLockingEnabled.

Control whether or not the SQLiteDatabase is made thread-safe by using locks around critical sections. This is pretty expensive, so if you know that your DB will only be used by a single thread then you should set this to false. The default is true

So i think this answers your question.

The method setLockingEnabled is depreciated in API level 16