SQLiteDatabase.endTransaction throws SQLiteDatabaseLockedException

Question

My code:

SQLiteDatabase db = ...;
db.beginTransaction();
try{
   db.update(...);

   db.setTransactionSuccessful();
}finally{
   db.endTransaction();
}

Now the problem is that endTransaction occasionally throws SQLiteDatabaseLockedException, and I don't know reason, or how to repeat same exception.

From SQLiteDatabaseLockedException I read:

Thrown if the database engine was unable to acquire the database locks it needs to do its job.

And from beginTransaction I read:

Begins a transaction in EXCLUSIVE mode.

From SQLite manual I read:

An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.

So how can DB lock not be acquired in endTransaction when I hold exclusive lock from beginTransaction? Android version where this happens is 4.0.4 (I have crash report, but not able to repeat this).

Need to say that I enabled SQLiteDatabase.enableWriteAheadLogging on the DB, maybe it matters? My app accessess DB in multiple threads.

Anyway, I'd like to get clear explanation, and make simple example that can repeat conditions repeating the problem, so that I can make real fix. Thanks.

Updated to "..." to mark that it's something already initialized. — Pointer Null, May 18 '13 at 22:21

score 0 · Answer 1 · answered May 18 '13 at 23:59

IMO your code is correct in a singular thread app, so it must be an enableWriteAheadLogging related issue. Maybe this can help:

SOURCE

... The maximum number of connections used to execute queries in parallel is dependent upon the device memory and possibly other properties.

...

Writers should use beginTransactionNonExclusive() or beginTransactionWithListenerNonExclusive(SQLiteTransactionListener) to start a transaction. Non-exclusive mode allows database file to be in readable by other threads executing queries.

...

score 0 · Answer 2 · edited May 23 '17 at 11:50

As I understand in serializable (default) mode of sqlite the locks refer not to different threads but to connections (this mode even knows nothing about threads). So if you are using the same connection (and all SqliteDatabase objects, produced by one sqliteOpenHelper instance share the same connection) across multiple threads you are totally unprotected.

Use either connection per thread or non-database synchronization locks, if you insist on multithreaded use of Sqlite. I would prefer a wrapper singleton with a single thread lock protecting its every transaction-like method. But this depends on your app specifics.

Please read this extensive answer and links in it for details and best practices for Sqlite multithreading.

SQLiteDatabase.endTransaction throws SQLiteDatabaseLockedException

2 Answers2