Android SQLite transactions and multi threads

Question

In my application, I call some webservices to update a database. Each webservice call is made in a specific thread, resulting in multiple threads updating the database object "at a time".

In each thread, I use transactions like that :

Thread 1 (webservice 1)
beginTransaction()
insert a row in the table 1
update a row in the table 1
endTransaction()

Thread 2 (webservice 2)
beginTransaction()
update a row in the table 2
update a row in the table 2
endTransaction()

But I struggle with one question I can't answer myself after googling it; As transactions are handled in different threads, are they distinct from each other? In other words, does the DB use a distinct "statement stack" or a common one?

From my readings, I understand transactions are in the same stack, i.e commiting the transaction in thread 1 may commit updates on table 2. For example, DB statement stack as I think it may happen:

beginTransaction() //Thread 1 begins a transaction
insert a row in the table 1
update a row in the table 1
beginTransaction() //Thread 2 begins a transaction
update a row in the table 2
update a row in the table 2
endTransaction() //Thread 2 ends a transaction
endTransaction() //Thread 1 ends a transaction

If it's true, how can I make my transactions really exclusives? Do I have to BEGIN EXCLUSIVE TRANSACTION and handle the SQLITE_BUSY error everywhere or is there something easier?

One idea is to use Singleton pattern in your DBHandler or DBadapter class. And synchronize insert statements — drulabs
– drulabs, Commented Feb 25, 2015 at 11:40

adriannieto · Accepted Answer · 2015-02-25 11:39:56Z

I encourage to read further to understand how the transaction modes work. From SQLite user guide:

Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.

https://www.sqlite.org/lang_transaction.html

The Android API provides a way to deal with those transaction modes, so choose which fits better to your application.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

"no other connection without exception will be able to write the database" What happens when they try? Does it throw an exception? Return empty results? Queue? I assume queue, but is the queue fair? Or unspecified?

Collectives™ on Stack Overflow

Android SQLite transactions and multi threads

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related