Read sqlite data when a transaction is running (Android)

Question

I need to read some data from a database meanwhile I'm loading some data in another thread with a transaction.

All my threads to read other tables are stopped until the transaction in other thread is finished.

I need to be able to read info from the database without worrying about the other thread.

I've read a lot of info about sqlite, android...but nothing is working, always my query to read parameters is blocked.

I've followed this advices as @KevinGalligan says in this thread (What are the best practices for SQLite on Android?) resolving locks and other problems.

1) I'm using only one SQLiteOpenHelper (singleton)

2) I've never closing the database

I've tried:

start the transaction with:

database.execSQL("begin immediate transaction");

or

database.beginTransactionNonExclusive();

instead

database.beginTransaction();

Not working, query blocked.

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

Any other solutions to read meanwhile a transaction is updating some tables? I don't care if my info is deprecated, it's more important to not block my threads.

score 8 · Accepted Answer · edited May 23 '17 at 11:45

I solved the problem.

I followed this steps.

To solve the problem of database locking and multi-threading use of the database (Based in http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection):

1) I'm using only one SQLiteOpenHelper (singleton).

2) Never close the database.

To be able to read and write without blocking my queries I followed this steps:

Use database.enableWriteAheadLogging() (only api>=11), my mistake was that you have to enable this mode in all connections, not only in your transaction or your writings, so I added the following code to my openHelper class in "onOpen" method. Based in this code of Mozilla in 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's 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 my problem of blocking reads while I'm updating some tables, but other updates are blocked too. If you want to solve this last issue, as @commonsware says:

using yieldIfContendedSafely() inside your transaction you will give to other threads a chance to work with the database. I haven't seen any difference using this method with beginTransaction or beginTransactionNonExclusive.

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

score 2 · Answer 2 · answered Dec 24 '14 at 12:43

2

I think you should use synchronization concept to avoid issues which you are facing.

answered Dec 24 '14 at 12:43

SAM

418
3
12

Synchronization is to avoid race conditions, etc...It will add more blocking issues to my case... I only need to read a database without worrying if I'm updating a table. – culebrins Dec 24 '14 at 12:45
in such case you will have to put your own logic. – SAM Dec 24 '14 at 12:58

score 2 · Answer 3 · answered Dec 29 '14 at 13:13

If your data-loading work is not just one massive SQL transaction, use yieldIfContendedSafely() to allow your other threads to read the database at various points. So, for example, if you are doing some sort of bulk data load, and you are doing a transaction every 100 inserts, call yieldIfContendedSafely() on every pass of the every-100-inserts loop to give your other threads a chance to work with the database.

score 0 · Answer 4 · answered Dec 24 '14 at 13:35

The SQLite in android it not thread-safe, the database will be locked when an operation is executing, that means you should make sure you database operation execute in only one thread, and invoke database.setTransactionSuccessful(); to end the transaction when your work is done.

score 0 · Answer 5 · answered Aug 24 '19 at 22:21

I suggest to see in direction turning off synchronous SQLITE option(by default in Android while one write transaction operation is running other read database queries wait finishing write transaction) You should before every connection to database tyrn of this option by db.exeqSql("PRAGMA synchronous = OFF") As a result all read operations will execute immediately but you get a little bit obsolete data from database(you cannot see in result data which running write transacion is inserting to database)

Read sqlite data when a transaction is running (Android)

5 Answers5