SQLite database, multithreading, lock and sync account on Android

Question

I am trying to get a template that is not subject to multi-threaded access to my sqlite database. Besides what turns me on, I can’t reproduce the problem.

I have an application that uses a database, as well as Android and Android accounts to synchronize the data of my application. I assume that when they occur simultaneously, it falls. I get a lot of errors, such as:

* android.database.sqlite.SQLiteDatabaseLockedException: database is locked * android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5) * android.database.sqlite.SQLiteDatabaseLockedException: error code 5: database is locked * android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode * android.database.sqlite.SQLiteDiskIOException: disk I/O error (code 778) * android.database.sqlite.SQLiteException: Failed to change locale for db '/data/data/net.bicou.redmine/databases/redmine.db' to 'en_US'. \n Caused by: android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5) 

Perhaps not all of them are associated with the same root cause, but I lost it.

I have:

• DbAdapter abstract base class that extends with subclasses that want to manage a separate table
• a class that manages an SQLite database called DbManager that contains Lock

Users currently have a version of DbManager that is not a single. I plan to make DbManager singleton, so that all threads share the same object. This should not be a problem, because, as I understand / saw, background synchronization and the application have the same process.

Here are the 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 with singleton version } public synchronized DbAdapter open() throws SQLException { if (mDb != null) { return this; } mDbManager = new DbManager(mContext); // currently in production mDbManager = DbManager.instance(mContext); // currently investigating this singleton solution try { mDb = mDbManager.getWritableDatabase(); } catch (final SQLException e) { Le("Unable to open DB, trying again in 1 second", e); try { Thread.sleep(1000); } catch (final InterruptedException e1) { Le("Could not wait 1 second " + e1); } mDb = mDbManager.getWritableDatabase();// This may crash } return this; } public synchronized void close() { mDbManager.close(); mDbManager = null; mDb = null; } } 

The class that should process the database table extends the DbAdapter and implements methods such as select , insert , delete , etc.

Here's the database 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(); // Currently in prod public DbManager(final Context context) { super(context, DB_FILE, null, DB_VERSION); mContext = context; } // singleton version will make this constructor private and 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) { for (final String sql : statements) { try { db.execSQL(sql); } catch (final Exception e) { Le("Unable to create table: " + sql, e); } } } } 

---

OK, now questions.

• Is my lock set correctly? I'm really new to this, I don't know if ReentrantLock good choice, and if I lock / unlock at the right time
• Is my synchronized method applied correctly? I mean, I put the synchronized around methods that I don't want to interrupt in parallel threads. It is right? Can you advise my synchronized use?
• How can I reproduce the problem? I created a test that uses 3 threads that do simultaneous read / write access to the database and use some Thread.sleep to ensure that the db open / close from each thread overlaps, but it does not crash. It really annoys me, I don’t think that there are many people who have a problem, so I don’t know how to reproduce.
• Is my technical choice of DbAdapter + DbManager good idea? Is there a better sample?
• Can DbManager make DbManager singleton?

Answer 1

To access multiple streams, it is recommended that you use the singleton pattern.

Thus, consecutive calls to the same database will be serialized sequentially.

However, it is not difficult to have some NullPointerException in the inserts. So, to expand the logic of " Thread.sleep ", you can use this 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); } } }