Reading sqlite data at transaction start (Android)

Question

I need to read some data from a database, meanwhile I am loading some data into another thread with a transaction.

All of my threads for reading other tables are stopped until the transaction is completed in another thread.

I need to be able to read information from a database without worrying about another thread.

I read a lot of information about sqlite, android ... but nothing works, always my request to read parameters is blocked.

I followed these tips as @KevinGalligan says in this thread ( What are the best practices for SQLite on Android? ), Resolving locks and other issues.

1) I use only one SQLiteOpenHelper (singleton)

2) I never close the database

I tried:

start the transaction with:

database.execSQL("begin immediate transaction"); 

or

 database.beginTransactionNonExclusive(); 

instead

 database.beginTransaction(); 

It does not work, the request is blocked.

I read about WAL (database.enableWriteAheadLogging ()), but I need to support api 9.

Any other read solutions while a transaction is updating some tables? I don't care if my information is out of date, it’s more important not to block my flows.

Answer 1

I solved the problem.

I have completed the following steps.

To solve the problem of database blocking and multi-threaded database usage (based on http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection ):

1) I use only one SQLiteOpenHelper (singleton).

2) Never close the database.

To read and write without blocking my requests, I followed these steps:

Use database.enableWriteAheadLogging() (api> = 11 only), my mistake was that you should enable this mode in all connections, not only in your transaction or in your letters, so I added the following code to my openHelper class in the section "onOpen". Based on this Mozilla code on github ( https://github.com/mozilla/mozilla-central/blob/9f2b8297b99d9d28038256b4f92a5aaa941749f1/mobile/android/base/db/TabsProvider.java ).

 @SuppressLint("NewApi") @Override public void onOpen(SQLiteDatabase db) { // From Honeycomb on, it possible to run several db // commands in parallel using multiple connections. if (Build.VERSION.SDK_INT >= 11) { try{ db.enableWriteAheadLogging(); }catch(Exception e){ Log.e("onOpen", e.getMessage()); } } } 

With this solution, I solved the read lock problem when updating some tables, but other updates are also blocked. If you want to solve this last problem, as @commonsware says:

using yieldIfContendedSafely() inside your transaction, you will give other threads the opportunity to work with the database. I did not see any difference using this method with beginTransaction or beginTransactionNonExclusive .

Another interesting: What are the best practices for SQLite on Android?

Answer 2

I think you should use the concept of synchronization to avoid the problems that you are facing.

Answer 3

If your data loading job is not just a massive SQL transaction, use yieldIfContendedSafely() so that your other threads can read the database at various points. So, for example, if you do some bulk data loading and complete a transaction every 100 inserts, call yieldIfContendedSafely() on each pass of the "every 100" loop to give your other threads the ability to work with the database.

Answer 4

SQLite in android is not thread safe, the database will be locked during the operation, which means that you have to make sure that the database operation is performed in only one thread and call database.setTransactionSuccessful (); to complete the transaction while doing your work.

Answer 5

I suggest seeing the option to turn off the synchronous SQLITE (by default in Android, when one write operation is performed, other database read requests wait for the write transaction to complete) you should db.exeqSql("PRAGMA synchronous = OFF") As a result, all read operations will be performed immediately, but you will get a little outdated data from the database (you cannot see in the results of the data that transactions are written to the database)