6

Consider following: I've got Service, which writes into DB in AsyncTask. And my Activity reads data from DB(consider UI thread for simplicity). I access DB using SQLiteOpenHelper. I create single instance in Application onCreate() and then obtain it in service and activity. Is there any possibility that I would get my DB 'dead locked'? Previously, I used ContentProvider for such operations. Though, it is based on using single SQLiteOpenHelper instance, I decided to simplify my project by excluding ContentProvider.

Consider code:

public class App extends Application {

    private OpenHelper openHelper;

    @Override
    public void onCreate(){
        super.onCreate();
            openHelper=new OpenHelper();
    }

        public OpenHelper getHelper(){
            return openHelper;
        }
}

In Activity:

OpenHelper helper=(App)getApplication().getHelper();
SQLiteDatabase db=helper.getReadableDatabase();
// Do reading

And inside Serice, in separate thread:

OpenHelper helper=(App)getApplication().getHelper();
SQLiteDatabase db=helper.getWritableDatabase();
//Do writing

Would it be safe?

UPD This might be the solution, but not sure how to use it.

Improve this question

5 Answers 5

Reset to default
7

Late, late answer. You're totally fine. That's the proper way to do it, actually. See my blog post: http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection/. Dig through my profile here. Lots of examples of this. ContentProvdier is just a lot of overhead and not needed unless you're sharing data outside of your app. Transactions are good to speed things up and (obviously) improve consistency, but not needed.

Just use one SqliteOpenHelper in your app and you're safe.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

@seb Edited answer. Moved blogs several months back. Also look at sqlite locking post: touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking. They key here is Android does thread locking under the hood for you in Java-land, but ONLY on the single instance. Multiple will clobber each other. Also, in the post it says doing DB reads in the UI thread. Please note: do not do this. Ever.
@KevinGalligan I've dug through the source for SQLiteDatabase and Helper and I'm convinced you are wrong. the Database object creates an SQLiteStatement object which in turn uses an SQLiteSession, each thread will have its own SQLiteSession. It says specifically in the SQLiteSession javadoc the class is not thread safe. Curious what makes you say this, because I really want you to be right...
@seb Think what you like. You're wrong. Sorry. Have had many apps, over several years, with millions of users. It works.
@seb OK. Being less a$$holeish. The HELPER is the singleton. NOT THE DATABASE. The HELPER keeps a single DATABASE instance. See here: grepcode.com/file/repository.grepcode.com/java/ext/…
@seb If you keep a single helper, you're fine. I make no statements otherwise.
|
2

My bet: it isn't safe.

To be in safer position you should use SQL transactions. Begin with beginTransaction() or beginTransactionNonExclusive() and finish with endTransaction(). Like shown here

Improve this answer

1 Comment

beginTransactionNonExclusive() gives me error that its for above api11. but my apps min api is 10. any other way to use sqlite with IMMEDIATE mode?
1

This is my solution I created a class and a private static object to syncronize all db access

public class DBFunctions {
// ...
private static Object lockdb = new Object();


/**
 * Do something using DB
 */
public boolean doInsertRecord(final RecordBean beanRecord) {
    // ...
    boolean success = false;

    synchronized (lockdb) {
              // ...
              // 
              // here ... the access to db is in exclusive way
              // 

              // ...
      final SQLiteStatement statement = db.compileStatement(sqlQuery);

        try {
            // execute ...
            statement.execute(); 
            statement.close();

            // ok
            success = true;
        } catch (Exception e) {
            // error
            success = false;
        }
            }

       return success;
    }

}

I tryed using ASYNC task and it works fine . I hope is the right way to solve the problem.

Any other suggestions ???

Improve this answer

1 Comment

Running three AsyncTasks at the same time, all inserting many, many rows into one table. (All writing to the same table.) It appears that the tasks queue and thus no error is thrown. This solution works for me. Still I wonder if this solution is dead-lock safe. Any opinions?
0

Good question. My first thought is it wouldn't be safe. However, according to the SQLite docs, SQLite can be used in 3 modes. The default mode is "serialized" mode:

Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction

So I assume this it's compiled in serialized mode on Android.

Improve this answer

Comments

0

Just saw this while I was looking for something else. This problem looks like it would be efficiently solved by using a ContentProvider. That way both the Activity as well as the Service can use the content provider and that will take care of the Db contention issues.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.