2

I'm trying to do a custom calendar in my app with event sync with a WebService.

Everything goes just fine (well, not really).

The Calendar Activity starts an AsyncTask in order to get current month events and store them in the database and the onPostExecute method updates the view to properly mark each day.

My problem comes when the user gets to the calendar activity and then quickly goes back to main activity several times, let's say for instance

Main Activity > Calendar Activity (Press Back button) > Main Activity > Calendar Activity (Press Back button) > ...

At this point Activity freezes due to a database lock has not been available for 30 sec. or App crashes due a SQLiteConstraintException: error code 19

EDIT: SQLiteConstraintException behaviour no longer happens

Because being updated is very important in my app, every time I re-enter the Calendar Activity I delete the database and re-populate it with server new data even tough it is the same, and at this point is where the lock is made.

EDIT:

I've changed Delete > Insert style to: Update all data (set flag column = 0) > Insert new data (set flag column = 1) > Delete (where flag column = 0)

I have already tried this solution to cancel the AsyncTask on Activity finish with no success

I am sure the problem is I don't completely understand the process of what I am doing so I am missing something really important (or basic).

Any help improving my code or just pointing me to the right direction, would be much appreciated.

Thanks in advance!

EDIT:

My application has an Aplication which is in charge of creating a single Database connection (getWriteableDatabase();) and this unique conection is reachable trough MyApp.getDatabase(); method.

Community
  • 1
  • 1
Eloi Navarro
  • 1,435
  • 1
  • 14
  • 26
  • 1
    `SQLiteConstraintException` should have nothing to do with the database lock problem. The exception is an error in your code, like inserting something with `null` where the sqlite table is defined `NOT NULL`. http://stackoverflow.com/questions/9599809/does-yieldifcontendedsafely-lose-the-benefits-of-a-transaction could be the solution for the lock problem – zapl Nov 19 '12 at 16:47
  • @zapl You were right, `SQLiteConstraintException` was thrown because of another problem and was not related to this issue. Already solved now. Thanks! – Eloi Navarro Nov 20 '12 at 15:18

2 Answers2

1

Try serializing access to your DB. Each instance of SQLiteDatabase represents a separate link to the SQLite DB. Actions executed on a single SQLiteDatabase instance are serialized, but when you mess around with multiple SQLiteDatabase instances in multiple threads, bugs like you described start to pop up.

You didn't post any code, so I'm just assuming that you get a new SQLiteDatabase instance (with .getWriteableDatabase() / .getReadableDatabase()) each time you acces the db in you AsyncTasks. Try using a single SQLiteDatabase instance and pass it to the tasks. This way you'll serialize db access and avoid AsyncTask threads racing for DB locks.

Zsombor Erdődy-Nagy
  • 16,864
  • 16
  • 76
  • 101
  • I've edited my question adding a bit more of information, maybe I'm now a bit clearer about how I handle `SQLiteDatabase` Thanks for answering! – Eloi Navarro Nov 20 '12 at 15:24
  • Check this thread: http://stackoverflow.com/questions/8200509/android-database-is-locked-by-other-thread Could it be that you manually manage transactions and somehow a transaction might fail to finish itself? – Zsombor Erdődy-Nagy Nov 20 '12 at 17:57
0

Main Activity > Calendar Activity (Press Back button) > Main Activity > Calendar Activity (Press Back button) > ...

That will likely create & start a new AsyncTask each time you you open the calendar.

Each AsyncTask will still run if you don't cancel it expicitly.

And it depends on the Android version / executor you use to execute the AsyncTask if they run in parallel or serialized. The default behavior for >= Honeycomb would be serial execution. Explained in the Order of execution section in AsyncTask documentation.

If you use serial AsyncTask you can have several of your (old) tasks queued up and it can take quite some time before your most recent task is executed. It might be a good idea to cancel tasks when you leave an Activity.

The database lock has not been available for 30 sec. warning you get indicates that you write in one thread and try to access the database from another thread at the same time. Not sure if that requires two write threads in parallel.

The problem is not using different SQLiteDatabase instances. I think you would get errors instead of that warning if you were doing that since Android can't check if some random SQLiteDatabase object has a write lock on the database.

So what can you do: I assume you use transactions since locking the database for long without transaction is pretty much impossible.

  • limit the size of your transactions to a certain amount so there is guaranteed break in between to use the database from other threads.
  • dynamically stop the transaction using db.yieldIfContendedSafely(). That will check if another thread wants to access the database and temporarily ends the transaction. Drawback: It will commit the change and you can't rollback the whole transaction.
  • experiment with SQLiteDatabase#enableWriteAheadLogging() & beginTransactionNonExclusive() as mentioned in the write-ahead-logging documentation. It reads like it could allow multithreaded access without locking problems. I have not investigated further if that works and what side-effects it has.
zapl
  • 63,179
  • 10
  • 123
  • 154
  • This was the right way to think in order to solve the problem. I was having several old queued `AsyncTask` and thus blocking my DB until all were finished, which may be in a very long time. – Eloi Navarro Nov 21 '12 at 11:17