How to Avoid Concurrency Problems When Using Sqlite on Android

How can I avoid concurrency problems when using SQLite on Android?

Inserts, updates, deletes and reads are generally OK from multiple threads, but Brad's answer is not correct. You have to be careful with how you create your connections and use them. There are situations where your update calls will fail, even if your database doesn't get corrupted.

The basic answer.

The SqliteOpenHelper object holds on to one database connection. It appears to offer you a read and write connection, but it really doesn't. Call the read-only, and you'll get the write database connection regardless.

So, one helper instance, one db connection. Even if you use it from multiple threads, one connection at a time. The SqliteDatabase object uses java locks to keep access serialized. So, if 100 threads have one db instance, calls to the actual on-disk database are serialized.

So, one helper, one db connection, which is serialized in java code. One thread, 1000 threads, if you use one helper instance shared between them, all of your db access code is serial. And life is good (ish).

If you try to write to the database from actual distinct connections at the same time, one will fail. It will not wait till the first is done and then write. It will simply not write your change. Worse, if you don’t call the right version of insert/update on the SQLiteDatabase, you won’t get an exception. You’ll just get a message in your LogCat, and that will be it.

So, multiple threads? Use one helper. Period. If you KNOW only one thread will be writing, you MAY be able to use multiple connections, and your reads will be faster, but buyer beware. I haven't tested that much.

Here's a blog post with far more detail and an example app.

Android Sqlite Locking (Updated link 6/18/2012)
Android-Database-Locking-Collisions-Example by touchlab on GitHub

Gray and I are actually wrapping up an ORM tool, based off of his Ormlite, that works natively with Android database implementations, and follows the safe creation/calling structure I describe in the blog post. That should be out very soon. Take a look.

In the meantime, there is a follow up blog post:

Single SQLite connection

Also checkout the fork by 2point0 of the previously mentioned locking example:

Android-Database-Locking-Collisions-Example by 2point0 on GitHub

Concurrency issues in Android SQLite

It's not a great idea to be opening and closing the database regularly like this. Open it once on application startup, and reuse this connection throughout your app's lifetime. You don't even need to bother closing it. SQLite manages the concurrency by itself, so you don't need to worry about multiple threads either.

This will make all your "database is locked" errors go away, as well as make your code tidier and simpler.

Concurrency in SQLite database

You need to use a singleton object of DataBase object. For this purpose you can use ContentProvider. Which is a better option to achieve concurrency.

Here is link for tutorial on ContentProvider
http://www.vogella.com/articles/AndroidSQLite/article.html

You can refer the documentation also
http://developer.android.com/guide/topics/providers/content-providers.html

SQLite and concurrency

I don't think that SQLite would be a good solution for those requirements. SQLite is designed for local and lightweight use only, not to serve hundreds of requests.

I would recommend some other solution, for example MySQL or PostgreSQL, both can be scripted quite well. So, if I were you, I would put my efforts into the setup scriptings.

To avoid the flame war between SQLite believers and haters, let me draw draw your attention to the often referred SQLite When-To-Use document (I believe it is considered as a credible source). Here they state the following:

Situations Where A Client/Server RDBMS May Work Better
High Concurrency
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

I think that in the referred question involves many writes and if the OP would go for SQLite, it would result a non-scalable solution.

Android sqlite concurrency without exceptions

So long as you're using the same SQLiteDatabase object, the synchronisation is done for you.

So if you access the object via a singleton, there should be no problem. Though you may want to add some further logic if you want to implement a timeout, e.g. wait/notify or something similar.

Multithreading data writing in SQLite

After a long search i finally found a gret answer for my broblem, so anyone who want to creat e multithreading acess to db should read this first What are the best practices for SQLite on Android?

Android recycle view update sort list with concurrency issue

My solution is to hold updates in a buffer which can only fire after a time delay. Then putting the update objects into both a concurrent hashmap for easy object retrieval as well as a sortedlist for easy ui display

The api calls this method. I have another version that can run off the ui thread by simply detaching the adapter in sortedlist callbacks.

public void onResult(Object object) {
    
        if ( !currentlyProcessing && System.currentTimeMillis() > (lastBatch + delay)) {

            currentlyProcessing = true;

            runOnUiThread(new Runnable() {
                @Override
                public void run() {

                    easyChatList.beginBatchedUpdates();

                    while (batchBuilder.size() > 0){
                        performBatch(batchBuilder.remove(0));
                    }
                    easyChatList.endBatchedUpdates();

                    lastBatch = System.currentTimeMillis();
                    currentlyProcessing = false;
                }
            });
        }
}

The performbatch() handles a single update. If the object is new it puts it into a concurrent hashmap with its id as a key. Then adds it to the sortedlist. When an update occurs I pull the object from the hashmap, do the update and add it to the sortedlist. The sortedlist api then replaces the old object and updates the sort order.

edit:this does have a slight drag according to androids skipped frames warning. You can detach the adapter from the sortedlist and do updates on a worker thread but I'm not sure if that has any value as this is working alright for now.