SQLite database, multithreading, Locks and account sync on android -

i'm trying pattern doesn't fail multithreaded access sqlite database. also, driving me nuts can't reproduce issue.

i have app uses db, android accounts , android sync sync app's data. guess when 2 happen same time, crashes. i'm getting lot of errors like:

 * android.database.sqlite.sqlitedatabaselockedexception: database locked  * android.database.sqlite.sqlitedatabaselockedexception: database locked (code 5)  * android.database.sqlite.sqlitedatabaselockedexception: error code 5: database locked  * android.database.sqlite.sqlitedatabaselockedexception: database locked (code 5): , while compiling: pragma journal_mode  * android.database.sqlite.sqlitediskioexception: disk i/o error (code 778)  * android.database.sqlite.sqliteexception: failed change locale db '/data/data/net.bicou.redmine/databases/redmine.db' 'en_us'. \n caused by: android.database.sqlite.sqlitedatabaselockedexception: database locked (code 5) 

maybe not of them related same root cause, i'm kind of lost.

what have is:

• an abstract base class, dbadapter, extended subclasses want manage single table
• a class manages sqlite database, called dbmanager, contains lock

right users have version of dbmanager not singleton. i'm planning make dbmanager singleton, threads share same object. shouldn't problem, because far have understood/seen, background sync , app share same process.

here classes (only relevant parts):

public abstract class dbadapter {     context mcontext;     protected dbmanager mdbmanager;     sqlitedatabase mdb;      public static final string key_rowid = "_id";      public dbadapter(final context ctx) {         mcontext = ctx;     }      public dbadapter(final dbadapter other) {         mcontext = other.mcontext;         mdb = other.mdb;         mdbmanager = other.mdbmanager; // removed singleton version     }      public synchronized dbadapter open() throws sqlexception {         if (mdb != null) {             return this;         }          mdbmanager = new dbmanager(mcontext); // in production         mdbmanager = dbmanager.instance(mcontext); // investigating singleton solution         try {             mdb = mdbmanager.getwritabledatabase();         } catch (final sqlexception e) {             l.e("unable open db, trying again in 1 second", e);             try {                 thread.sleep(1000);             } catch (final interruptedexception e1) {                 l.e("could not wait 1 second " + e1);             }             mdb = mdbmanager.getwritabledatabase();// may crash         }          return this;     }      public synchronized void close() {         mdbmanager.close();         mdbmanager = null;         mdb = null;     } } 

a class needs handle database table extend dbadapter, , implement methods such select, insert, delete, etc.

here's db manager:

public class dbmanager extends sqliteopenhelper {     private static final string db_file = "db";     private static final int db_version = 15;     context mcontext;     lock mlock = new reentrantlock();      // in prod     public dbmanager(final context context) {         super(context, db_file, null, db_version);         mcontext = context;     }      // singleton version make constructor private , add:     private static dbmanager minstance;     public static synchronized dbmanager instance(context context) {         if (instance == null) {             instance = new dbmanager(context);         }         return instance;     }      @override     public sqlitedatabase getwritabledatabase() {         mlock.lock();         return super.getwritabledatabase();     }      @override     public void close() {         super.close();         mlock.unlock();     }      @override     public void oncreate(final sqlitedatabase db) {         // ...     }      @override     public void onupgrade(final sqlitedatabase db, final int oldversion, final int newversion) {         // ...     }      private void createtables(final sqlitedatabase db, final string[] statements) {         (final string sql : statements) {             try {                 db.execsql(sql);             } catch (final exception e) {                 l.e("unable create table: " + sql, e);             }         }     } } 

---

ok, now, questions.

1. is lock implemented? i'm new this, don't know if reentrantlock choice, , if i'm locking/unlocking @ right moment
2. are synchronized method implemented? mean, have placed synchronized keyword around methods don't want interrupted concurrent threads. right? can advice on synchronized use?
3. how can reproduce issue? have created test uses 3 threads make concurrent read/write access db, , use thread.sleep ensure db open/close each thread overlap, doesn't crash. bugging me, don't think there lot of people have issue, don't know how reproduce.
4. is dbadapter + dbmanager technical choice idea? there better pattern?
5. is idea make dbmanager singleton?

for multiple threads accessing, advisable use singleton pattern.

such way, successive calls same database seamlessly serialised.

however, it's not impossible have nullpointerexceptions on inserts. so, expand "thread.sleep" logic, use code:

@override public sqlitedatabase getwritabledatabase() {     while (true) {         try {             return super.getwritabledatabase();         } catch (sqlitedatabaselockedexception e) {             system.err.println(e);         }          try {             thread.sleep(500);         } catch (interruptedexception e) {             system.err.println(e);         }     } }