Android threading and database locking
We used a ContentProvider
in the end. This appeared to clear up the problems.
I solved this same exception just by making sure all my database opens have closes, and (more importantly) to assure this, making the scope of each database instance local ONLY to the method that needs it. ContentProvider is a good, safe class to use when accessing a db from multiple threads, but also make sure you're using good db practices:
- Keep db instances local (no SQLiteDatabase class members!)
- call
close()
on the db in the same method in which it's opened - call
close()
on the cursors you get from the db - listen to LogCat for any complaints that SQLiteDatabse might have
Before some code, let's resume some of the approachs:
Semaphores: by far the best solution presented. It goes in the heart of the problem: resource sharing! It will treat the locking of the database access, avoiding conflicts (
database is locked
).Java synchronization: A kind of semaphore implementation, but less sofisticated. Using
synchronized
you will not easily solve some cases involving transactions.ContentProvider: implement
ContentProvider
solve the problem only for some cases (or sweep the problem under the carpet). You'll yet face the same issues. The difference is thatContentProvider
pattern will guide you to not make some commom mistakes when accessing Sqlite database. The ContentProvider docs says: "You don't need a provider to use an SQLite database if the use is entirely within your own application."Almost mandatory: keep db instances local, call
close()
on the db in the same method in which it's opened usingfinally
statements,close()
on the cursors usingfinally
statements, etc are almost mandatory to avoid problems using Sqlite.
Let's show an example of the semaphore solution presented by Moss, which I took from CL. and improoved to cover transactions.
class DataAccess { private final ReentrantReadWriteLock rwl = new ReentrantReadWriteLock(); private final Lock r = rwl.readLock(); private final Lock w = rwl.writeLock(); public Data readSomething(int id) { Cursor c = null; r.lock(); try { c = getReadableDatabase().query(...); return c.getString(0); } finally { if (c != null) c.close(); r.unlock(); } } public void changeSomething(int id, int value) { w.lock(); try { getWritableDatabase().update(...); } finally { w.unlock(); } } private void beginTransactionWithSemaphores() { getWritableDatabase().beginTransactionWithListener(new SQLiteTransactionListener() { @Override public void onBegin() { w.lock(); } @Override public void onRollback() { w.unlock(); } @Override public void onCommit() { w.unlock(); } }); }}