Multiple threading questions when using sqlite transaction in Android

Question

I use sqlite transaction in Android:

SQLiteDatabase database = sqlite_helper.getWritableDatabase();

database.beginTransaction();
...
database.setTransactionSuccessful();
database.endTransaction();

My questions are :
1. Should I place endTransaction() in finally code block like this：

try {
    database.beginTransaction();
    ...
    database.setTransactionSuccessful();
}
finally {
    database.endTransaction();
}

If there are exepctions during database operations, will the database be rolled back automatically without using "finally"?

When the transaction is not ended, can other threads read or write the same database? I hear sqlite in Android is threading safe, but I are not sure with it. I guess there will be some problems during transaction. Is there an error raised if another thread writes the same database with the same connection?
I ever found this error in my app, but I don't know whether it's related to the threading safe problem:

android.database.sqlite.SQLiteMisuseException: library routine called out of sequence:

, while compiling

Does anyone help me to answer these questions? Thanks a lot!

fullahouse · Accepted Answer · 2012-10-25 07:05:17Z

5

1.you should always place endTransaction in finally block

2.transaction in SQLite is thread safe,see the doc http://www.sqlite.org/atomiccommit.html

answered Oct 25, 2012 at 7:05

fullahouse

1412 bronze badges

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

3 Comments

strange Over a year ago

The link has no mentioning of the word 'thread'. How is it thread-safe? The question is if another thread would create a new transaction OR run a new query while a transaction from another thread is open. Is it thread-safe then? In my current experience I get weird 'connection-pool' errors.

zyamys Over a year ago

Transactions are not thread-safe. There is only one SQLiteDatabase instance shared between threads, and hence the database can only be in one transaction state which will be manipulated by two threads concurrently. If one thread begins a transaction, and then a second thread attempts to begin another transaction, you will get an IllegalStateException thrown up.

Monstieur Over a year ago

The Android beginTransaction and endTransaction are thread-safe wrapper functions around the actual BEGIN and COMMIT commands using Java synchronisation, even though it internally uses a single database connection. If you're using the native SQLite API directly (unlikely in Android), it's not thread-safe.

CL. · Accepted Answer · 2012-10-25 07:05:50Z

You should always put endTransaction() into a finally block (also see the docs). Otherwise, the database would not be able to notice than an exception has happened.

The only other way to end a transaction would be to close the connection, in which case SQLite automatically rolls back any active transaction.

As long as one connection writes to the database (which means that a transaction is active), no other connections can read or write. Therefore, you should take care not to forget to end transactions.

You should never write from multiple threads; what would happen if one threads ends the transaction while the other one is still writing?

Your SQLiteMisuseException might be related, or not; that's impossible to say without seeing the code.

Phil · Accepted Answer · 2019-02-27 03:42:52Z

0

Yes, you should use the finally block. Here is a simple, THREAD SAFE method I use:

/**
 * Call for multiple DB insertion transactions.  It is thread safe and fast!
 */
private synchronized void writeTransaction(Runnable task) {
    try {
        db.beginTransaction();
        task.run();
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

the synchronized keyword locks the method with its containing object, thus making it thread safe...

answered Feb 27, 2019 at 3:42

Phil

11 bronze badge

Collectives™ on Stack Overflow

Multiple threading questions when using sqlite transaction in Android

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related