db_transaction()

Execute code within a database transaction

Wraps code execution in a database transaction with automatic commit on success and rollback on error. This ensures atomicity of multiple database operations.

Usage

db_transaction(conn, code)

Arguments

conn: Database connection
code: Expression or code block to execute within the transaction

Value

The result of the code expression

Details

The function automatically:

Begins a transaction with DBI::dbBegin()
Executes the provided code
Commits the transaction on success with DBI::dbCommit()
Rolls back the transaction on error with DBI::dbRollback()

Transactions are essential for maintaining data integrity when performing multiple related operations. If any operation fails, all changes are rolled back.

Examples

if (FALSE) {
conn <- db_connect("postgres")
# Basic transaction
db_transaction(conn, {
  DBI::dbExecute(conn, "INSERT INTO users (name, age) VALUES ('Alice', 30)")
  DBI::dbExecute(conn, "INSERT INTO users (name, age) VALUES ('Bob', 25)")
})
# Transaction with error handling - auto-rollback on error
tryCatch({
  db_transaction(conn, {
    DBI::dbExecute(conn, "INSERT INTO users (name) VALUES ('Alice')")
    stop("Something went wrong")  # This will trigger rollback
  })
}, error = function(e) {
  message("Transaction failed: ", e$message)
})
DBI::dbDisconnect(conn)
}