Transactions

API

If you want to execute multiple statements under one transaction, use transaction function.

val players = listOf<Player>()
database.playerQueries.transaction {
  players.forEach { player ->
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}

To return a value from a transaction, use the transactionWithResult function.

val players: List<Player> = database.playerQueries.transactionWithResult {
  database.playerQueries.selectAll().executeAsList()
}

Rollback

Transactions will roll back if an exception occurs anywhere in them. You can manually roll back a transaction anywhere inside of one, but if your transaction returns a value you will need to specify a value for the transaction to return.

database.playerQueries.transaction {
  players.forEach { player ->
    if (player.number == 0) rollback()
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}

val numberInserted: Int = database.playerQueries.transactionWithResult {
  players.forEach { player ->
    if (player.number == 0) rollback(0)
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
  players.size
}

Callbacks

You can register callbacks to occur after a transaction has completed or rolled back:

database.playerQueries.transaction {
  afterRollback { log("No players were inserted.") }
  afterCommit { log("${players.size} players were inserted.") }

  players.forEach { player ->
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}

Consistency of Large Queries

Using AndroidSqliteDriver, query results are read through a CursorWindow of limited size (2 MB by default). When a result does not fit in one window, AndroidSqliteDriver executes the statement again to fill the next one. A query executed outside a transaction can observe changes committed by another thread between windows, and return inconsistent rows from the database.

To guarantee that a large query reads a single consistent snapshot, execute it inside a transaction.

val players: List<Player> = database.playerQueries.transactionWithResult {
  database.playerQueries.selectAll().executeAsList()
}

Using a transaction means other writers wait until the transaction ends.

Setting a larger windowSizeBytes on AndroidSqliteDriver (API 28+) can avoid creating an additional window.

Info

Avoid INSERT, UPDATE or DELETE statements with a RETURNING clause whose result may not fit in one window. The statement is executed again for each additional window, so its changes are applied more than once. A transaction does not prevent this.