Begin/commit/rollback SQL transactions

A transaction encapsulates several SQL statements in an atomic unit. It is initiated with dbBegin() and either made persistent with dbCommit() or undone with dbRollback(). In any case, the DBMS guarantees that either all or none of the statements have a permanent effect. This helps ensuring consistency of write operations to multiple tables.

Methods in other packages

This documentation page describes the generics. Refer to the documentation pages linked below for the documentation for the methods that are implemented in various backend packages.

• adbi::dbBegin("AdbiConnection")

• adbi::dbCommit("AdbiConnection")

• adbi::dbRollback("AdbiConnection")

• bigrquery::dbBegin("BigQueryConnection")

• bigrquery::dbCommit("BigQueryConnection")

• bigrquery::dbRollback("BigQueryConnection")

• DatabaseConnector::dbBegin("DatabaseConnectorConnection")

• DatabaseConnector::dbCommit("DatabaseConnectorConnection")

• DatabaseConnector::dbRollback("DatabaseConnectorConnection")

• dittodb::dbBegin("DBIMockConnection")

• dittodb::dbCommit("DBIMockConnection")

• dittodb::dbRollback("DBIMockConnection")

• duckdb::dbBegin("duckdb_connection")

• duckdb::dbCommit("duckdb_connection")

• duckdb::dbRollback("duckdb_connection")

• odbc::dbBegin("OdbcConnection")

• odbc::dbCommit("OdbcConnection")

• odbc::dbRollback("OdbcConnection")

• pool::dbBegin("Pool")

• pool::dbCommit("Pool")

• pool::dbRollback("Pool")

• RJDBC::dbBegin("JDBCConnection")

• RJDBC::dbCommit("JDBCConnection")

• RJDBC::dbRollback("JDBCConnection")

• RMariaDB::dbBegin("MariaDBConnection")

• RMariaDB::dbCommit("MariaDBConnection")

• RMariaDB::dbRollback("MariaDBConnection")

• RMySQL::dbBegin("MySQLConnection")

• RMySQL::dbCommit("MySQLConnection")

• RMySQL::dbRollback("MySQLConnection")

• RPostgres::dbBegin("PqConnection")

• RPostgres::dbCommit("PqConnection")

• RPostgres::dbRollback("PqConnection")

• RPostgreSQL::dbBegin("PostgreSQLConnection")

• RPostgreSQL::dbCommit("PostgreSQLConnection")

• RPostgreSQL::dbRollback("PostgreSQLConnection")

• RSQLite::dbBegin("SQLiteConnection")

• RSQLite::dbCommit("SQLiteConnection")

• RSQLite::dbRollback("SQLiteConnection")

• sparklyr::dbBegin("spark_connection")

• sparklyr::dbCommit("spark_connection")

• sparklyr::dbRollback("spark_connection")

Usage

dbBegin(conn, ...)

dbCommit(conn, ...)

dbRollback(conn, ...)

Arguments

conn

A DBIConnection object, as returned by dbConnect().

...

Other parameters passed on to methods.

Value

dbBegin(), dbCommit() and dbRollback() return TRUE, invisibly.

Details

Not all database engines implement transaction management, in which case these methods should not be implemented for the specific DBIConnection subclass.

Failure modes

The implementations are expected to raise an error in case of failure, but this is not tested. In any way, all generics throw an error with a closed or invalid connection. In addition, a call to dbCommit() or dbRollback() without a prior call to dbBegin() raises an error. Nested transactions are not supported by DBI, an attempt to call dbBegin() twice yields an error.

Specification

Actual support for transactions may vary between backends. A transaction is initiated by a call to dbBegin() and committed by a call to dbCommit(). Data written in a transaction must persist after the transaction is committed. For example, a record that is missing when the transaction is started but is created during the transaction must exist both during and after the transaction, and also in a new connection.

A transaction can also be aborted with dbRollback(). All data written in such a transaction must be removed after the transaction is rolled back. For example, a record that is missing when the transaction is started but is created during the transaction must not exist anymore after the rollback.

Disconnection from a connection with an open transaction effectively rolls back the transaction. All data written in such a transaction must be removed after the transaction is rolled back.

The behavior is not specified if other arguments are passed to these functions. In particular, RSQLite issues named transactions with support for nesting if the name argument is set.

The transaction isolation level is not specified by DBI.

See also

Self-contained transactions: dbWithTransaction()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "cash", data.frame(amount = 100))
dbWriteTable(con, "account", data.frame(amount = 2000))

# All operations are carried out as logical unit:
dbBegin(con)
withdrawal <- 300
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
#> [1] 1
dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
#> [1] 1
dbCommit(con)

dbReadTable(con, "cash")
#>   amount
#> 1    400
dbReadTable(con, "account")
#>   amount
#> 1   1700

# Rolling back after detecting negative value on account:
dbBegin(con)
withdrawal <- 5000
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
#> [1] 1
dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
#> [1] 1
if (dbReadTable(con, "account")$amount >= 0) {
  dbCommit(con)
} else {
  dbRollback(con)
}

dbReadTable(con, "cash")
#>   amount
#> 1    400
dbReadTable(con, "account")
#>   amount
#> 1   1700

dbDisconnect(con)