CODEWITHSUNDEEP
androidsqlitelockingthread-safety
Manish
Manish

Reputation: 373

Is Sqlite Database instance thread safe

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.

Upvotes: 37

Views: 30041

Answers (5)

ahmet alp balkan
ahmet alp balkan

Reputation: 45214

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

Upvotes: 31

Adam Burley
Adam Burley

Reputation: 6069

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.

Upvotes: 0

Mojo Risin
Mojo Risin

Reputation: 8142

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

Upvotes: 2

Akhilesh Dhar Dubey
Akhilesh Dhar Dubey

Reputation: 2148

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.

Upvotes: 5

Plamen Nikolov
Plamen Nikolov

Reputation: 4187

If you make it..

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

Upvotes: -2

Related Questions