Android Question What is the best way to multiple access a single SQL database...

[bsnqt]

I am using SQLite and have one local database file for storing short text messages. In the .db file there are several tables. I have one Activity and one sticky Service, which is for intercepting SMS.

My issue is I need to access the database to record the new arriving message when it is intercepted by the Service. But at the same time I also need to access the db file in order to display records / refresh the user's main interface (if they are working with the app) --> Something looks like a chat application (you are viewing the main Activity while new message can come at any time and you have to record it in the background, then update it immediately in the main Activity). The situation is even worse when user wants to delete an old message (I have to delete it from the database), at the same time a new one can come (I have to access the database to record it) etc.

My app can run well, but sometimes I got unpredictable error ("locked database error"), sometimes my app reacts unpredictably, sometimes it hangs for long time...

I guess it is because the background recording of new message is not finished yet but the app try to access the database in order to refresh the interface. So you have multiple accesses at the same time.

What is the correct strategy I should do for my case? Thanks for your advice.

Regards,
bsnqt

 

[MrKim]

The from another website:
Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter).

Personally, I think I would set a flag each time you access the database for writing and deleting and have the other Routines wait until the flag is free. Also, while I haven't tested this, make sure you close your cursors after reading data. It is possible they hold a lock on the records but I am not sure.

Hope this helps
Kim

 

[DouglasNYoung]

You could put all your database read/writes in Try/Catch structures -and perform some loop until sucessful. But put a counter in the loop, and a maximum value, to ensure you don't end up in an infinite loop.

Douglas

 

[mc73]

If you just insert the new message and then get it to your activity, I can hardly understand why you get this type of error, and especially the hang event. How are you refreshing your activity?

 

[bsnqt]

Hi MrKim, DouglasNYoung, mc73,

Thanks so much for your advice and sorry for my late response.

Personally, I think I would set a flag each time you access the database for writing and deleting and have the other Routines wait until the flag is free. Also, while I haven't tested this, make sure you close your cursors after reading data. It is possible they hold a lock on the records but I am not sure.

Yes I also think about it but finally I give up as if I create the flags, then there should be many of them as I have many Subs... Pls see below.
(Yes I do close the cursor each time)

You could put all your database read/writes in Try/Catch structures -and perform some loop until successful. But put a counter in the loop, and a maximum value, to ensure you don't end up in an infinite loop.
Douglas

That is a good solution. So far I can think only about this direction...

If you just insert the new message and then get it to your activity, I can hardly understand why you get this type of error, and especially the hang event. How are you refreshing your activity?

Well, I wish I can post the whole project here for your reference. But unfortunately it is a big project and it's my company project so I can just detail here my answer: From my previous post, I did not mention clearly, but actually when a message arrives, you should do many tasks, not just a simple INSERT task to the database. For example (pseudocode):

1) Load messages to user's chat interface

Cursor1 = SQL2.ExecQuery("SELECT * FROM Messages WHERE address=+112212345 ORDER BY datetime ASC")

2) User can send out a message during his chat at any time, so you have to be able to record new sent message and then refresh the Activity to update it into the chat thread

"INSERT INTO Messages VALUES(?,?,?,?)", Array As Object(Address, Contents, DateTime.Now, id))

3) Update the notification (refresh the new messages quantity);
4) User can delete an existing message during the chat

SQL.ExecNonQuery("DELETE FROM Messages WHERE address=?",Array As Object(phonenumber))

4) A new message arrives during the chat

"INSERT INTO Messages VALUES(?,?,?,?)", Array As Object(Address, Contents, DateTime.Now, id))

5) Access to database to check if new message is a "black list" one or not; if not, so allow it to pop up to the user, if yes, will have to block it and record into a block database;
5) Update the notifications;
6) Update the new message to user interface;...

The above is only simplified task list that I have to do, in the reality I have more than that (for example, user can add his chatting partner to blacklist, he can restore the existing message to inbox, he can receive other people's new SMS etc...)

So using flags is a little bit confusing as I will need many flags.

 

[bsnqt]

How are you refreshing your activity?

In the Activity, I have one listview to display all senders threads (general list).

When the user clicks on one list item (i.e on one sender/thread), the listview will be cleared, after that I access to database and display all messages from that sender to listview (specific list):

Cursor1 = SQL2.ExecQuery("SELECT * FROM Messages WHERE address=+112212345 ORDER BY datetime ASC")

When a new SMS from that sender arrives, I just need to insert new item (message) to listview:

myListview.InsertAt (blah blah blah...)

For your information, my app used to work very well, when I use txt files to store messages, blacklist, settings, etc (each in one separate file). Now the issues happen only after I decided to switch to use SQL (store in db file) .

 

[MrKim]

You only need one flag set by whichever activity is currently writing to the DB. The others wait for the flag to clear.

Another option, separate tables, or even databases for each user based on their address. In SQLite these are very easy to create and destroy.

 

[mc73]

I see now. This at least explains the crashes.
My first advice: limit the result to say 20 records. If user needs to see more, place a "read more".
Secondly, I would check if I am closing all cursor objects when their job is done.
Thirdly, I think you should have a look at asynchronous sql provided by Erel.
Just some thoughts...

 

[bsnqt]

You only need one flag set by whichever activity is currently writing to the DB. The others wait for the flag to clear.

Another option, separate tables, or even databases for each user based on their address. In SQLite these are very easy to create and destroy.

Oh ok I see, I will look for the possibility of using flag on that way, thanks

I see now. This at least explains the crashes.
My first advice: limit the result to say 20 records. If user needs to see more, place a "read more".
Secondly, I would check if I am closing all cursor objects when their job is done.
Thirdly, I think you should have a look at asynchronous sql provided by Erel.
Just some thoughts...

Your third advice is especially interesting for me, it confirms my thought. I will look for that direction as well. Thanks a lot

Maybe I will implement combination, optimizing my codes for accessing to database, plus implementing a flag and switching to asynchronous SQL.

Thank you very much.

 

[warwound]

Could you encapsulate all database queries into a code module or class?

In that code module or class create a 'query queue'.
The code module or class executes a query from the queue and calls a callback Sub passing the result.
Then it checks if the queue is empty - if not it executes the next query.

Martin.

 

[bsnqt]

Could you encapsulate all database queries into a code module or class?

In that code module or class create a 'query queue'.
The code module or class executes a query from the queue and calls a callback Sub passing the result.
Then it checks if the queue is empty - if not it executes the next query.

Martin.

I think that should be one of the best and optimal ways to do things. I will look to this direction later on, so far my codes are still messy with all of the said issues (described in my previous posts). I will need to clean out the messy and later on I will re-code on this way, it will take more times... Many thanks warwound.

 

[eps]

Could you encapsulate all database queries into a code module or class?

In that code module or class create a 'query queue'.
The code module or class executes a query from the queue and calls a callback Sub passing the result.
Then it checks if the queue is empty - if not it executes the next query.

Martin.

Isn't this what dbutils does?

 

[bsnqt]

Yes, I think so... that is why now I am looking and checking DBUtils... So many things to learn . Thanks.