% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/dbWithTransaction.R,
%   R/dbWithTransaction_DBIConnection.R
\name{dbWithTransaction}
\alias{dbWithTransaction}
\alias{dbBreak}
\title{Self-contained SQL transactions}
\usage{
dbWithTransaction(conn, code, ...)

dbBreak()
}
\arguments{
\item{conn}{A \linkS4class{DBIConnection} object, as returned by
\code{\link[=dbConnect]{dbConnect()}}.}

\item{code}{An arbitrary block of R code.}

\item{...}{Other parameters passed on to methods.}
}
\value{
\code{dbWithTransaction()} returns the value of the executed code.
}
\description{
Given that \link{transactions} are implemented, this function
allows you to pass in code that is run in a transaction.
The default method of \code{dbWithTransaction()} calls \code{\link[=dbBegin]{dbBegin()}}
before executing the code,
and \code{\link[=dbCommit]{dbCommit()}} after successful completion,
or \code{\link[=dbRollback]{dbRollback()}} in case of an error.
The advantage is
that you don't have to remember to do \code{dbBegin()} and \code{dbCommit()} or
\code{dbRollback()} -- that is all taken care of.
The special function \code{dbBreak()} allows an early exit with rollback,
it can be called only inside \code{dbWithTransaction()}.

\Sexpr[results=rd,stage=render]{DBI:::methods_as_rd("dbWithTransaction")}
}
\details{
DBI implements \code{dbWithTransaction()}, backends should need to override this
generic only if they implement specialized handling.
}
\section{Failure modes}{


Failure to initiate the transaction
(e.g., if the connection is closed
or invalid
of if \code{\link[DBI:transactions]{DBI::dbBegin()}} has been called already)
gives an error.

}

\section{Specification}{


\code{dbWithTransaction()} initiates a transaction with \code{dbBegin()}, executes
the code given in the \code{code} argument, and commits the transaction with
\code{\link[DBI:transactions]{DBI::dbCommit()}}.
If the code raises an error, the transaction is instead aborted with
\code{\link[DBI:transactions]{DBI::dbRollback()}}, and the error is propagated.
If the code calls \code{dbBreak()}, execution of the code stops and the
transaction is silently aborted.
All side effects caused by the code
(such as the creation of new variables)
propagate to the calling environment.

}

\examples{
\dontshow{if (requireNamespace("RSQLite", quietly = TRUE)) withAutoprint(\{ # examplesIf}
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:
dbWithTransaction(
  con,
  {
    withdrawal <- 300
    dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
    dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
  }
)

# The code is executed as if in the current environment:
withdrawal

# The changes are committed to the database after successful execution:
dbReadTable(con, "cash")
dbReadTable(con, "account")

# Rolling back with dbBreak():
dbWithTransaction(
  con,
  {
    withdrawal <- 5000
    dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
    dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
    if (dbReadTable(con, "account")$amount < 0) {
      dbBreak()
    }
  }
)

# These changes were not committed to the database:
dbReadTable(con, "cash")
dbReadTable(con, "account")

dbDisconnect(con)
\dontshow{\}) # examplesIf}
}
