Database locked exception

Question

I have created a database in my application with 5 tables. my database is being updated from different threads. When i see the log i can see that there are database locked exception while opening the database if it is already open.

One of my friend suggested me to always use content provider to avoid this issue. According to him content provider manages concurrency issues on its own?

Is it a good practice to use content provider if we don't want to share data to other applications?

score 4 · Answer 1 · answered Jun 26 '12 at 18:54

I think using a read-write lock is enough in most cases.

Suppose you have written the following,

import java.util.concurrent.locks.ReadWriteLock;
import java.util.concurrent.locks.ReentrantReadWriteLock;

public class MyDatabase extends SQLiteOpenHelper
{
    private static final ReadWriteLock rwLock = new ReentrantReadWriteLock(true);

    private static void beginReadLock()
    {
        rwLock.readLock().lock();
    }

    private static void endReadLock()
    {
        rwLock.readLock().unlock();
    }

    private static void beginWriteLock()
    {
        rwLock.writeLock().lock();
    }

    private static void endWriteLock()
    {
        rwLock.writeLock().unlock();
    }

then you can do your task like the following.

    public static void doSomething()
    {
        SQLiteDatabase sldb = null;

        try
        {
            beginReadLock();

            MyDatabase mydb = new MyDatabase();
            sldb = mldb.getReadableDatabase();
            ......
        }
        catch (Exception e)
        {
            ......
        }
        finally
        {
            if (sldb != null)
            {
                try
                {
                    sldb.close();
                }
                catch (Exception e) {}
            }

            endReadLock();
        }
    }

Enclose read operations with beginReadLock() and endReadLock(). Likewise, enclose write operations with beginWriteLock() and endWriteLock().

Months ago, by the solution described above, I could solve my own database-lock issue where multiple threads were trying to read/write-open a database simultaneously.

Hey @Takahiko Kawasaki:I tried your solution but I am still facing the issue. Not sure what may be going wrong. — user2234, Sep 22 '15 at 07:05

score 1 · Answer 2 · edited May 23 '17 at 12:31

The problem is that you use several database connections to your database. Thus, several threads try to update your table simultaneously and all these threads have different connections to your database.

To avoid this problem in all your threads you need to use the same connection to the database, i.e. all your threads should use the same connection to the database (that is represented by SQLiteDabase object).

Moreover, so as there is a file block on a sqlite file you'll not improve the performance of database upgrade using several threads (it's better to use only one thread to work with database). If you want to use several threads, you should use the same connection to the database and in this case Android will manage locks.

The discussion of this problem you can find here: http://touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking and here: What are the best practices for SQLite on Android?

Database locked exception

2 Answers2

Linked