How to manage a Sqlite connection between multiple simultaneous threads?

Question

So I’m facing some problems with my Sqlite connection, where I’m not finding a skilled solution.

Setting

My scenario is this::

• I synchronize the data of my application with a legacy system, through a Webservice;
• This synchronization is done through a Thread that checks what’s new in the legacy database and starts several other Threads to get the data and save it in its local base (Sqlite);
• For each entity that is found new data in the legacy database a new Thread is opened, (each entity has an Endpoint, where it is accessed, downloaded, and saved the data, and then finalized the Thread). Ex: if there are new clients in the legacy database, a request is started that will fetch all new clients, and saves them in my client table in Sqlite, this would run in a Thread;

Problem

The problem is that even if I use AsyncTask.SERIAL_EXECUTOR that only runs one Thread at a time, between an opening and another connection ends up generating problem Lock, where I cannot get the Current reference of the connection and when trying to create a new one it accuses that db is Locked.

I believe my problem is not even with threads running simultaneously, but in my class connection management, which I am currently using is:

public abstract class GConexaoAdapter {

private final DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;
protected final Context ctx;

public GConexaoAdapter(Context ctx, String dbName, int dbVersion) {
    this.mDbHelper = new DatabaseHelper(ctx, dbName, dbVersion);
    this.ctx = ctx;
}

private void open() {
    try {
        synchronized (ctx) {
            synchronized (mDbHelper) {
                if (mDb == null) {
                    mDb = mDbHelper.getWritableDatabase();
                } else {
                    synchronized (mDb) {
                        mDb = mDbHelper.getWritableDatabase();
                    }
                }
            }
        }
    } catch (IllegalStateException e) {
        e.printStackTrace();
        if (mDb == null || !mDb.isOpen())
            mDb = ctx.openOrCreateDatabase(mDbHelper.getDatabaseName(), 0,
                    null);
        Log.i("TRATAR", "tratar");
    }
}

protected SQLiteDatabase getConexao() {
    synchronized (ctx) {
        synchronized (mDbHelper) {
            if (mDb == null) {
                this.open();
            } else if (!mDb.isOpen()) {
                synchronized (mDb) {
                    if (!mDb.isOpen()) {
                        this.open();
                    }
                }
            }
            return mDb;
        }
    }
}

protected void closeConexao() {
    synchronized (ctx) {
        synchronized (mDbHelper) {
            if (mDb != null && mDb.isOpen()) {
                synchronized (mDb) {
                    if (mDb.isOpen()) {
                        if (mDb.inTransaction()) {
                            mDb.endTransaction();
                        }
                        mDb.close();
                    }
                }
            }
        }
    }
}

private static class DatabaseHelper extends SQLiteOpenHelper {

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if (!db.isReadOnly()) {
            // db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    DatabaseHelper(Context context, String dbNome, int dbVersion) {
        super(context, dbNome, null, dbVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}
}

I don’t think I need all the synchronized that I have, is that I have tried several solutions I have found and none brought me the expected result.

Question?

I believe there’s even a "Cake recipe" for this type of connection management. I may not be the only one who needs a solution like this.

So how would I manage to effectively, effectively manage my connection to the Sqlite database?

Very important remark:

I would like to manage the Sqlite connection between multiple simultaneous threads, however without causing Locks that end up generating exceptions and violating data integrity!

Answer 1

Synchronizing the opening, reading/writing and closing methods of the database individually does not solve an essential problem that is the need for access to the database to be atomic.

It would make your life easier if instead of using this lot of synchronized within this class (synchronize in a Context ??) you used within the method doInBackground() of their AsyncTasks, wrapping within a block synchronized an atomic operation in the bank composed of open(), read or record, close():

doInBackground() {

    //Traz os dados do webservice;

    synchronized(objetoLock) {
        open();
        //lê ou grava no banco;
        close();
    }
}

I also suggest you use as lock blocks synchronized an object that lasts throughout the entire application, I would use an object to the exclusive part for this, but you can use an object related to the database (for example the instance of GConexaoAdapter) as long as it lasts long enough in your application to be used for all bank operations that occur. And it doesn’t cost to emphasize, the synchronization has to be done always in the same object, otherwise gives concurrent access problem the same way.

Answer 2

So after some more time of research, and following some paths pointed out by you, I came to a "Cake Recipe*¹" as I was looking for.

I’ll show you the solution that met my needs:

I followed the tips pointed out in this link, making some modifications to my needs

public class DatabaseManager {
    private AtomicInteger mOpenCounter = new AtomicInteger();

private static DatabaseManager instance;
private static SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase mDatabase;

    private DatabaseManager() {
        super();
    }

private static synchronized void initializeInstance(SQLiteOpenHelper helper) {
    if (instance == null) {
        instance = new DatabaseManager();
        mDatabaseHelper = helper;
    }
}

public static synchronized DatabaseManager getInstance(Context context,
        String dbName, int dbVersion) {
    if (instance == null) {
        initializeInstance(new DatabaseHelper(context, dbName, dbVersion));
        return instance;
    }

    return instance;
}

public synchronized SQLiteDatabase openDatabase() {
    if (mOpenCounter.incrementAndGet() == 1) {
        // Opening new database
        mDatabase = mDatabaseHelper.getWritableDatabase();
    }
    return mDatabase;
}

public synchronized void closeDatabase() {
    if (mOpenCounter.decrementAndGet() == 0) {
        // Closing database
        mDatabase.close();

    }
}
}

And in my old class I made some modifications that only apply to adapt my old class the new class connection management:

public abstract class GConexaoAdapter {

protected final Context ctx;
private final String dbName;
private final int dbVersion;

public GConexaoAdapter(Context ctx, String dbName, int dbVersion) {
    this.dbName = dbName;
    this.dbVersion = dbVersion;
    this.ctx = ctx;
}

protected SQLiteDatabase getConexao() {
    return DatabaseManager.getInstance(ctx, dbName, dbVersion)
            .openDatabase();
}

protected void closeConexao() {
    DatabaseManager.getInstance(ctx, dbName, dbVersion).closeDatabase();
}
}

That second class is not necessary you can use the DatabaseManager directly in that way:

private long create(ContentValues valores) {
    try {
        DatabaseManager.getInstance(ctx, "db", 1).openDatabase()
                .beginTransaction();
        long result = DatabaseManager.getInstance(ctx, "db", 1).openDatabase().insert(nomeTabela, null, valores);
        DatabaseManager.getInstance(ctx, "db", 1).openDatabase().setTransactionSuccessful();
        return result;
    } finally {
        DatabaseManager.getInstance(ctx, "db", 1).openDatabase().endTransaction();
        DatabaseManager.getInstance(ctx, "db", 1).closeDatabase();
    }
}

Or adapt it to your way of working.

*¹It is not a cake recipe as actually expected by the question, but for my case in particular and I believe for others as well, it will be very useful, and can be easily adapted to your environment, if you are working in a modular and organized way.

Note: This solution manages the connection of Sqlite in thread-safe

Answer 3

I strongly recommend using an external queue (There are several solutions ready for this) for continuous insertion... and separating a single thread for consuming function... will not generate lock and will ensure the chronological integrity of the data.. only will not be synchronous with the reading of the data in the original database..

Answer 4

Start with the default "who opens the connection is who closes it", I’m sorry I don’t know if there is a cool name for this pattern... :-)

Implementing the Datasource interface helps you define how you will work with the single connection in the database.

I think this might be a way... :-)

Answer 5

I don’t know much about android, but we have a desktop application that also uses Sqlite and the solution for using it in several threads was to create a class to be a Pool of connections and this pool had only 1 connection for writing and 2 for reading