1

I know transactions are supposed to enforce ACID properties but w.r.t transaction in SQLite, a guy here warns about the transaction done on same connection to be visible to others:

By default — changes that are being done in a transaction on a single SQLite database connection can be visible to other transactions on that connection immediately — even before calling SQLiteDatabase.endTransaction()

which is on Medium and since no one has pointed out, seems to be authentic advice.

Now, I was reading about enableWriteAheadLogging() as a solution for concurrent Db access from official docs and found this:

This method enables parallel execution of queries from multiple threads on the same database. It does this by opening multiple connections to the database and using a different database connection for each query. The database journal mode is also changed to enable writes to proceed concurrently with reads.

When write-ahead logging is not enabled (the default), it is not possible for reads and writes to occur on the database at the same time. Before modifying the database, the writer implicitly acquires an exclusive lock on the database which prevents readers from accessing the database until the write is completed.

In contrast, when write-ahead logging is enabled (by calling this method), write operations occur in a separate log file which allows reads to proceed concurrently. While a write is in progress, readers on other threads will perceive the state of the database as it was before the write began. When the write completes, readers on other threads will then perceive the new state of the database.

Now, if you read the highlighted part above, you see that by default, without enabling write ahead logging, the default behavior prevents concurrent read/write access and blocks until current operation has completed. If you contrast this with what the gut above said, it seems that with transaction, there is no such blocking.

How can this be possible that non-transactional behavior prevents you from reading or writing to the Db but the transactional one does?

Community
  • 1
  • 1
Manish Kumar Sharma
  • 12,982
  • 9
  • 58
  • 105

2 Answers2

0

There is no "non-transactional behaviour":

No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.

What that guy says is technically wrong; there are no "other transactions on that connection". A connection can have only one active transaction at the same time.

When multiple threads share the same connection, they share the transaction. And because they are in the same transaction, they are not isolated from each other. Any thread that executes a BEGIN/COMMIT/ROLLBACK, or any other SQL statement, affects all other threads on the same connection.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • So in that case, why would you begin your own explicit "transaction" through `beginTransaction()?` – Manish Kumar Sharma Oct 22 '17 at 12:23
  • 1
    Because you want to run more than one statement? – CL. Oct 22 '17 at 12:46
  • @pulp_fiction it can substantially reduce the time taken e.g. 20,000 ms to 250ms for [What are the overheads of using AUTOINCREMENT for SQLite on Android? - second set of results v the first](https://stackoverflow.com/questions/45384919/what-are-the-overheads-of-using-autoincrement-for-sqlite-on-android/45384920#45384920) – MikeT Oct 22 '17 at 23:20
0

By default — changes that are being done in a transaction on a single SQLite database connection can be visible to other transactions on that connection immediately — even before calling SQLiteDatabase.endTransaction()

You may be READING (pun intentional) more into what has been said. i.e. The changes applied will be available i.e. they can be SEEN/READ within the connection BUT they have not been written/committed.

It's only when/if

  • a) the outer AND ALL inner/nested transactions have been marked as clean/OK by a setTransactionSucccessful AND

  • b) the endTransaction is invoked

that the changes are committed (written to disk). i.e. it is only at/during the END that data is written to disk.

MikeT
  • 51,415
  • 16
  • 49
  • 68