Transactions and Room

By default, SQLite treats each individual SQL statement as an individual transaction. To the extent that Room winds up generating multiple SQL statements in response to our annotations, it is Room’s responsibility to wrap those statements in a suitable transaction.

However, sometimes, you have business logic that requires a transaction, for operations that require multiple DAO functions. For example, persisting an invoice might involve inserting an Invoice and all of its InvoiceLineItem objects, and that might require more than one DAO function to achieve.

Room offers two ways of setting up app-defined transactions: the @Transaction annotation and some functions on RoomDatabase.

Using @Transaction

Your DAO can have one or more functions that have the @Transaction annotation. Whatever a @Transaction-annotated function does is wrapped in a SQLite transaction. The transaction will be committed if the @Transaction-annotated function does not throw an exception. If it does, the transaction will be rolled back.

There are two places to apply @Transaction: custom open functions on an abstract DAO class, or on @Query functions.

Custom Functions

Here, the idea is that your @Transaction-annotated function would make multiple DAO calls to other functions (e.g., ones with @Insert or @Query annotations), so that the work performed in those other functions “succeed or fail as a whole”.

Given our fictitious Invoice example, we might have something like this:

@Dao
abstract class InvoiceStore {
  @Insert
  fun _insert(invoice: Invoice)

  @Insert
  fun _insertItems(lineItems: List<InvoiceLineItem>)

  @Transaction
  open fun insert(invoice: Invoice) {
    _insert(invoice)
    _insertItems(invoice.getLineItems())
  }
}

Here, we still use an insert() function to insert an Invoice, but we use that to wrap two DAO calls to insert the Invoice metadata and insert the InvoiceLineItem objects.

Note that the function with @Transaction needs to be open. Room will generate a concrete implementation of your DAO, either extending your abstract class or implementing your interface. To make @Transaction work, Room code-generates an overriding function that wraps a call to your implementation in a transaction. However, in Kotlin, concrete functions cannot be overridden without the open keyword. Leaving that keyword off may result in strange compile error messages.

On @Query Functions

It may seem odd to have to specifically request a transaction on a @Query-annotated function. After all, the default behavior of SQLite is to have each individual SQL statement be in its own transaction.

However, there are two scenarios called out in the documentation where @Transaction would be a good idea. One is tied to @Relation, which we will cover later in the book.

The other is tied to a little-known issue with Android’s SQLite support: things get weird when the result set of a query exceeds 1MB. In that case, using the regular Android SQLiteDatabase API, the Cursor that you get back does not contain the full result set. Instead, it contains a “window” of results, and if you position the Cursor after that window, the query is re-executed to load in the next window. This can lead to inconsistencies, if the database is changed in between those two database requests to populate the window. Room, by default, will load the entire result set into your entities, quickly moving through the windows as needed, but there is still a chance that a database modification occurs while this is going on. Using @Transaction would help ensure that this is not an issue, by having the entire query — including traversing the windows — occur inside a transaction.

Using RoomDatabase

Alternatively, RoomDatabase offers the same beginTransaction(), endTransaction(), and setTransactionSuccessful() functions that you see on SQLiteDatabase, and so you use the same basic algorithm:

roomDb.beginTransaction()

try {
  // bunch of DAO operations here
  roomDb.setTransactionSuccessful()
}
finally {
  roomDb.endTransaction()
}

The advantage to this approach is that you can put the transaction logic somewhere other than the DAO, if that would be more convenient or make more sense for your particular implementation. However, it is a bit more work.