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.

• Samuel Peter: possible duplicate of What are the best practices for SQLite on Android?
• Boris Treukhov: you should use locking as described in stackoverflow.com/questions/12758655/…

Answer 1

[WRONG: Please see answers below]

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

• Ryan: developer.android.com/reference/android/database/sqlite/… 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... Doesn't this imply it IS thread safe by default?
• ahmet alp balkan: Hmm. It should be and now I am mistaken. Hmm.
• Marian Paździoch: "This method was deprecated in API level 16. This method now does nothing. Do not use. "
• Ivan: So now when the method is deprecated SQLiteDatabase instances are not thread safe under any circumstances?
• Boris Treukhov: Ok I reread this answer - maybe you should put this answer to actual state instead of [right][wrong][wrong] it's very confusing. The simple answer is NO. Its not thread safe - that's it. stackoverflow.com/questions/12758655/…

Answer 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.

• user25: Yes I have some errors when calling sqLiteHelper.close in onDestroy. So what should I do instead? Not calling it at all? database will self perform close operation internally when all the operation would be finish. But public SQLiteDatabase getWritableDatabase() says Make sure to call close when you no longer need the database.
• Tom Raganowicz: How do you create thread-safe singleton, so you can truly have one instance of the DB?

Answer 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

Answer 4

I don't think the answers here are accurate anymore after API 16.

TL;DR: I think that API 16 and later don't stop you from executing multiple SQL statements at the same time on different threads.

Prior to API 16, the method setLockingEnabled did exist and the javadoc clearly stated it was set to true by default. Once the method was deprecated in API 16 and set to do nothing, there is no official information in the docs about whether locking is enabled or not. But we can get some info by looking at the code: https://android.googlesource.com/platform/frameworks/base/+/refs/heads/master/core/java/android/database/sqlite/SQLiteDatabase.java

There is a field called mLock which explicitly says it's only used for global state changes and NOT for execution of SQL statements:

// Shared database state lock.
// This lock guards all of the shared state of the database, such as its
// configuration, whether it is open or closed, and so on. This lock should
// be held for as little time as possible.
//
// The lock MUST NOT be held while attempting to acquire database connections or
// while executing SQL statements on behalf of the client as it can lead to deadlock.
//
// It is ok to hold the lock while reconfiguring the connection pool or dumping
// statistics because those operations are non-reentrant and do not try to acquire
// connections that might be held by other threads.
//
// Basic rule: grab the lock, access or modify global state, release the lock, then
// do the required SQL work.
private final Object mLock = new Object();

Also, all the SQL work is done in SQL sessions and each thread has its own session (below quote is from SQLiteSession):

Session objects are not thread-safe. In fact, session objects are thread-bound. The {@link SQLiteDatabase} uses a thread-local variable to associate a session with each thread for the use of that thread alone. Consequently, each thread has its own session object and therefore its own transaction state independent of other threads.

This is different from in API 15 and earlier, where execute statements were done directly from the DB and not in a session: methods such as executeUpdateDelete in https://android.googlesource.com/platform/frameworks/base/+/refs/tags/android-4.0.4_r2.1/core/java/android/database/sqlite/SQLiteStatement.java actually acquire and release the lock themselves. This calls the lock method on the SQLiteDatabase which is where the check for mLockingEnabled is performed and then locks the mLock object. In this way, no two SQL statements can be executed at the same time on different threads.

In contrast, in modern versions of Android the synchronized (mLock) in SQLiteDatabase is only around global state changes, as indicated by the above comments - and there is no longer any lock method on SQLiteDatabase (to be called by statements etc) So I can't find any evidence that Android still ensures that two SQL statements on different threads can't be executed at the same time.

Answer 5

If you make it..

setLockingEnabled(boolean lockingEnabled) Control whether or not the SQLiteDatabase is made thread-safe by using locks around critical sections.