Sqlite database locking with (WAL) write-ahead logging enabled

Question

My sqlite database is running into locks even though I have WAL enabled and I'm using a singleton instance of my DbHelper across the entire app, which is as follows:

public class DbHelper extends SQLiteOpenHelper { 

  private static final String mTAG = "DB_HELPER";
  private static DatabaseHelper sInstance;

  private static final String DATABASE_NAME = "database_name";
  private static final int DATABASE_VERSION = 1;

  public static synchronized DatabaseHelper getInstance(Context context) {

    if (sInstance == null) {
      sInstance = new DatabaseHelper(context.getApplicationContext());
    }
    return sInstance;
  }

  private DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }
}

For inserts, I have the following function:

public void insertToDb(String tableName, ContentValues contentValues) {
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    sqLiteDatabase.enableWriteAheadLogging();
    sqLiteDatabase.beginTransactionNonExclusive();

    sqLiteDatabase.insert(tableName, null, contentValues);
    sqLiteDatabase.setTransactionSuccessful();
    sqLiteDatabase.endTransaction();
    sqLiteDatabase.close();
}

Any idea why this might be happening?

Update: I have an asynctask from where the problem stems by throwing out an attempt to re-open an already-closed object exception. Following that, I start getting the error below in my logcat:

#################################################################
Error Code : 5 (SQLITE_BUSY)
Caused By : The database file is locked.
(database is locked (code 5))
#################################################################
' Stack Trace: 'android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)
#################################################################
Error Code : 5 (SQLITE_BUSY)
Caused By : The database file is locked.
(database is locked (code 5))
#################################################################
at android.database.sqlite.SQLiteConnection.nativeExecute(Native Method)
at android.database.sqlite.SQLiteConnection.execute(SQLiteConnection.java:679)
at android.database.sqlite.SQLiteSession.beginTransactionUnchecked(SQLiteSession.java:319)
at android.database.sqlite.SQLiteSession.beginTransaction(SQLiteSession.java:298)
at android.database.sqlite.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:510)
at android.database.sqlite.SQLiteDatabase.beginTransactionNonExclusive(SQLiteDatabase.java:445)
at com.ontheway.otw.entermarkets.helpers.database.OTWUserDb.updateOTWUserLocation(OTWUserDb.java:124)
at com.ontheway.otw.entermarkets.services.FusedLocationService.onLocationChanged(FusedLocationService.java:146)
at com.google.android.gms.internal.zzart$zzb$1.zza(Unknown Source)
at com.google.android.gms.internal.zzart$zzb$1.zzs(Unknown Source)
at com.google.android.gms.internal.zzaaz.zzb(Unknown Source)
at com.google.android.gms.internal.zzaaz$zza.handleMessage(Unknown Source)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:7325)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)
'

What makes you think that you can use a single database object from multple threads? — CL.
That's the whole purpose of the singleton, isn't it? Or each thread and background service is starting its own instance? — Zishan Danish Neno

bwt bwt · Accepted Answer · 2017-01-09T14:51:39

The SQLiteDatabase (wrapped by the SQLiteOpenHelper) is threadsafe (it uses a SQLiteConnectionPool and a SQLiteSessionfor each thread) but you should not close it until you are completely done with the database, which for most application is never. This is basically what the linked article says.

On the other hand you should make sure that transactions are always ended, even when not successful, e.g. :

SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
sqLiteDatabase.enableWriteAheadLogging();
sqLiteDatabase.beginTransactionNonExclusive();
try {
    sqLiteDatabase.insert(tableName, null, contentValues);
    sqLiteDatabase.setTransactionSuccessful();
} finally {
    sqLiteDatabase.endTransaction();
}

Sqlite database locking with (WAL) write-ahead logging enabled

2 Answers