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.

possible duplicate of [What are the best practices for SQLite on Android?](http://stackoverflow.com/questions/2493331/what-are-the-best-practices-for-sqlite-on-android) — Samuel Peter, Aug 09 '15 at 16:32
you should use locking as described in https://stackoverflow.com/questions/12758655/sqlitedatabase-multi-thread-locking-pattern — Boris Treukhov, Jan 04 '18 at 23:28

score 30 · Accepted Answer · edited Dec 17 '22 at 07:26

30

[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:

edited Dec 17 '22 at 07:26

Al Mamun

944
9
27

answered Jul 13 '11 at 07:05

ahmet alp balkan

42,679
38
138
214

11

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#setLockingEnabled%28boolean%29 `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? – Ryan Aug 21 '11 at 21:22
1

Hmm. It should be and now I am mistaken. Hmm. – ahmet alp balkan Aug 22 '11 at 06:03
3

"This method was deprecated in API level 16. This method now does nothing. Do not use. " – Marian Paździoch Apr 18 '16 at 06:48
1

So now when the method is deprecated SQLiteDatabase instances are not thread safe under any circumstances? – Ivan Apr 24 '17 at 16:14
@Ivan See https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#isDbLockedByCurrentThread(): `...threads may block if they cannot acquire a database connection to perform a particular operation.` – Kenny Worden Aug 14 '17 at 17:22
1

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. https://stackoverflow.com/questions/12758655/sqlitedatabase-multi-thread-locking-pattern – Boris Treukhov Jan 04 '18 at 23:25
@BorisTreukhov If the default setting was thread safe, and the method to change the setting now does nothing, then it ought to be thread safe. But I think the correct answer is that the docs are unclear. We'd need some other reference to definitively state that it is or isn't. (Of course, when in doubt, assume not.) – StackOverthrow Apr 24 '18 at 18:48
@TTK recently after applying java RX I faced the situation at https://stackoverflow.com/a/37562940/241986 so even though everything was properly covered with reader/writer lock - it turned out it can't work reliably even with multiple readers, so the single global lock for readers and writers is the only solution by now :-) Anyway if there is no reference, the evidence is that it not thread safe at all even if you open and close database(and cursors and etc) in try-finally block every time. – Boris Treukhov Apr 24 '18 at 20:08
@TTK Btw if you open source code of `getReadableDatabase()` and then `getDatabaseLocked()` and see the comment `// Darn! The user closed the database by calling mDatabase.close().` you will see that the mDatabase variable is not even a volatile and it will become evident that entire `SQLiteOpenHelper` instance should be at least thread confined. – Boris Treukhov Apr 24 '18 at 20:13

score 5 · Answer 2 · answered Apr 19 '15 at 16:57

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.

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.__ — user25, Jan 31 '19 at 11:15
How do you create thread-safe singleton, so you can truly have one instance of the DB? — Tom Raganowicz, Jul 11 '22 at 09:21

score 2 · Answer 3 · edited Feb 20 '17 at 09:27

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

score 0 · Answer 4 · answered Mar 09 '22 at 15:11

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.

score -2 · Answer 5 · answered Jul 13 '11 at 07:05

-2

If you make it..

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

answered Jul 13 '11 at 07:05

Plamen Nikolov

4,177
3
23
24

Is Sqlite Database instance thread safe

5 Answers5

Linked