How to check lock status and unlock if necessary for Database on Blackberry?

Question

Since I've started developing my Blackberry app, the biggest problems I've encountered all had to do with SQLite Databases.

Right now I'm putting my app through a stress test, and when problems pop up I address them by printing out statuses to the console and taking care of things line by line. Right now (after mashing buttons on my app) I received a "Database is locked" error and I'm not sure what to do.

It seems that once the database is locked it's locked for good until it is unlocked........ my question is how can I unlock it?? First of all, how can I check to see if it's locked??

I'm sure our users won't be mashing buttons like I did, but you never know. I want to account for every possible scenario.

Thanks

EDIT: This is what happens in my application..... When I launch it starts a thread, this thread performs a cleanup on one of my tables based on how old certain pieces of data are (uses DELETE). The thread then continues to get a USER object from my DB (read only), it then uses this USER object as a parameter to call a web service. The data retrieved from the web service is INSERTED into my database. (It's a little more complex than that as a few read/write operations are performed at this time. After that, the thread fires a callback method to update my UI.

This all works fine. I can exit the app WHILE the thread is running and relaunch and a flag will prevent it from starting a new instance of the same thread (unless the other one is done of course).

Now my problem: My app's home screen is a list of buttons, when the user clicks one of these buttons another, more detailed list is loaded (this requires a READ ONLY call to the database). When I launch the app (firing the web service calling thread) and then click a button on the main screen right away, the table gets locked. (Not always, sometimes it takes 4 or 5 tries, sometimes more, sometimes less). But if I keep doing this it WILL eventually lock making it impossible to make any calls to my DB, hence no more UI (which depends on the DB).

The DB call that populates the UI on the second screen is READ ONLY, can't I have as many of these as I need?? What causes the DB to lock?? What's the difference between a DB lock and File System error (12)??

Answer 1

I seemed to have fixed the problem. I was under the impression that if a read/write connection was open then a read-only connection could be created safely.

This doesn't seem to be the case. If I have a read/write connection open then no other connections can open until that one is finished.

I basically created one read/write connection, set a flag to identify it as open, and during my read connection use the same Database object if the flag is open, or create a read only if it's closed.

So far so good.

Answer 2

Sqlite does not support concurrent modification. In practice on BlackBerry, this means you can only open the database from one part of the code at a time. To maintain this one-at-a-time access, you need to close the database when you are done with it, as @AnkitRox points out.

However you also need to guard against concurrent access. Even if your code properly closes the database, it is possible for two different threads to access the database. In that case, you will need one to wait. In Java-ME this is typically accomplished through the 'synchronized' keyword, and using the same lock object for all database access.