People,
I am looking for a design template that allows the user interface thread to interact with client-side SQLite databases that can have voluminous inserts (taking 10 seconds), quick inserts and reads, and also do not block the user interface of the thread.
I would like to know if I use the optimal design pattern for this, since I recently debugged deadlock and synchronization problems, and I am not 100% sure about my final product.
Access to the database is now limited to a narrow class. Here is the pseudo code showing how I approach the record in my singleton, DataManager:
public class DataManager { private SQLiteDatabase mDb; private ArrayList<Message> mCachedMessages; public ArrayList<Message> readMessages() { return mCachedMessages; } public void writeMessage(Message m) { new WriteMessageAsyncTask().execute(m); } protected synchronized void dbWriteMessage(Message m) { this.mDb.replace(MESSAGE_TABLE_NAME, null, m.toContentValues()); } protected ArrayList<Message> dbReadMessages() {
Main characteristics:
- First: all public write operations (writeMessage) occur through AsyncTask, never on the main thread
- Next: all recording operations are synchronized and wrapped in START OPERATIONS
- Next: the read operations are not synchronized, as they should not be blocked during recording
- Finally: read results are cached on the main thread in onPostExecute
Does this mean Android best practice for writing potentially large amounts of data to a SQLite database while minimizing the impact on the user interface stream? Are there any obvious synchronization issues with the pseudo code you see above?
Update
There is a significant error in my code above and it looks like this:
DataManager.this.mDb.execSQL("BEGIN TRANSACTION;");
This line gets a lock in the database. However, this is a DEFERRED lock, so other clients can read and write until they write .
DataManager.this.dbWriteMessage(args[0]);
This line actually modifies the database. At the moment, the lock is a RESERVED lock, so other clients cannot write.
Note that a more expensive DB record occurs after the first call to dbWriteMessage. Suppose each write operation occurs in a secure synchronized method. This means that a lock is acquired in the DataManager, a write occurs, and the lock is released. If WriteAsyncMessageTask is the only author, this is normal.
Now suppose there is another task that also performs write operations but does not use a transaction (because it is a fast write). Here's what it looks like:
private class WriteSingleMessageAsyncTask extends AsyncTask<Message, Void, Message> { protected Message doInBackground(Message... args) { DataManager.this.dbWriteMessage(args[0]); return args[0]; } protected void onPostExecute(Message newMessages) { if (DataManager.this.mCachedMessages != null) DataManager.this.mCachedMessages.add(newMessages); } }
In this case, if WriteSingleMessageAsyncTask is running at the same time as WriteMessageAsyncTask, and WriteMessageAsyncTask has completed at least one record already, you can call dbWriteMessage to WriteSingleMessageAsyncTask, get a lock in the DataManager, but then block its completion write because of RESERVED lock. WriteMessageAsyncTask receives and refuses to block the DataManager, which is a problem.
Conclusion: merging transactions and locking objects at the same level can lead to deadlocks. Before starting a transaction, make sure that you have an object level lock.
Fix source class WriteMessageAsyncTask:
synchronized(DataManager.this) { DataManager.this.mDb.execSQL("BEGIN TRANSACTION;"); DataManager.this.dbWriteMessage(args[0]); // More possibly expensive DB writes DataManager.this.mDb.execSQL("COMMIT TRANSACTION;"); }
Update 2
Watch this video from Google I / O 2012: http://youtu.be/gbQb1PVjfqM?t=19m13s
It offers a design pattern using built-in exclusive transactions and then using yieldIfContendedSafely