SQLite Locking and Transaction Handling in Android

SQLite locking concept comes when we access database from multiple threads. What things should we keep in our mind while designing the database in android, let’s see.

firstly we create a helper class which extends SQLiteHelper class:

[code language=”java”]
public class DatabaseHelper extends SQLiteOpenHelper { … }
[/code]

Now lets we have multiple thread which are writing the data in database like this.

[code language=”java”]
//thread-1
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
database.insert(…..);
database.close();

//thread-2
DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
database.insert(…..);
database.close();
[/code]

Now, when we run this code, we may get following exception:

[code language=”java”]
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)
[/code]

why it actually occurred?

From SQLite FAQ:
Multiple processes can have the same database open at the same time.
But only one process can be making changes to the database at any moment in time.

It means, In multithreading environment, multiple thread can read database but single thread can write to database. i.e. in our case, we are creating many helper instance and one helper instance is responsible for creating one database connection and if we make multiple helper instance then multiple database connection we will have.

Android uses java locking mechanism for Sqlite database to keep access serialized. So, if multiple threads have one database instance, and we are using one helper instance shared between all the threads, so all of our database access code is serial which is managed internally by the SQLite database.

Now, Let’s make previous class singleton, so that at any point of time we have only one instance of helper class and database as well.

[code language=”java”]
public class DatabaseHelper extends SQLiteOpenHelper {
private static DatabaseHelper sInstance;
public static synchronized DatabaseHelper getInstance(Context context){
// Use the application context, which will ensure that you don’t accidentally leak an Activity’s context.
if (sInstance == null) {
sInstance = new DatabaseHelper(context.getApplicationContext());
}
return sInstance;
}

private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
}
[/code]

Now, we made our helper class singleton and share same helper object to every threads like this.

[code language=”java”]
//thread-1
DatabaseHelper helper = DatabaseHelper.getInstance(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
database.insert("…..");
database.close();

//thread-2
DatabaseHelper helper = DatabaseHelper.getInstance(getApplicationContext());
SQLiteDatabase database = helper.getWritableDatabase();
database.insert("…..");
database.close();
[/code]

Now, when we run this code, we may get another exception:

[code language=”java”]
java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase
[/code]

We get this exception because we call close() database in between the operation. Actually database open/close is file handling operation done inside SQLite database. When we write to database it actually flushed to disk immediately. Android implements the reference count for each operation and whenever we do anything with database, it increments that count by 1, and when that operation done successfully, it decrements the count by 1. When all operations are done, Android closes the database.

There was a method called setLockingEnabled(boolean lockingEnabled) which was deprecated from API level 16. If you set lockingEnabled false you could perform database operations with multiple thread which is not thread safe. Now this method does nothing. Do not use it. So we can make sure that if we use one helper instance shared with multiple thread and we don’t call close() on database then SQlite database handle everything, we don’t have to worry about.

SQLite Transaction:
Each SQL statement has a new transaction started for it. This is very expensive, since it requires reopening, writing to, and closing the journal file for each statement. This can be avoided by wrapping sequences of SQL statements in a single transaction.

For instance, before begin with transaction let’s see how we perform SQL operation without transaction-

[code language=”java”]
SQLiteDatabase database = helper.getWritableDatabase();

for (Address address : addressList) {
ContentValues values = new ContentValues();
values.put(fieldAddressId, address.getId());
values.put(fieldAddressName, address.getName());
database.insert(tableName, null, values);
}
[/code]

Now, there are three basic method for transaction handling in android i.e.

[code language=”java”]
database.beginTransaction();
database.setTransactionSuccessful();
database.endTransaction();
[/code]

Let’s see how the transaction handling done in android by this example:

[code language=”java”]
SQLiteDatabase db = helper.getWritableDatabase();

db.beginTransaction();

String sql = "INSERT INTO " + ADDRESS_TABLE + "(house_id,house_name)" + " VALUES(?,?)";

SQLiteStatement insert = db.compileStatement(sql);

try {
for (Address address : addressList) {
insert.bindString(1, address.getHouseId());
insert.bindString(2, address.getHouseName());
insert.execute();
insert.clearBindings();
}
} finally {
db.endTransaction();
}
db.setTransactionSuccessful();
db.endTransaction();
db.close();
[/code]