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 methods. For example, persisting an invoice might involve inserting an Invoice and all of its InvoiceLineItem objects, and that might require more than one DAO method to achieve.

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

Using @Transaction

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

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

Custom Methods

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

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

@Dao
public abstract class InvoiceStore {
  @Insert
  public abstract void _insert(Invoice invoice);

  @Insert
  public abstract void insert(List<InvoiceLineItem> lineItems);

  @Transaction
  public void insert(Invoice invoice) {
    _insert(invoice);
    insert(invoice.getLineItems());
  }
}

Here, we still use an insert() method 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 you will need to use an abstract class, not an interface, as an interface cannot have arbitrary method implementations in them.

On @Query Methods

It may seem odd to have to specifically request a transaction on a @Query-annotated method. 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() methods 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.