1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
% 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[=dbBegin]{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[=dbCommit]{dbCommit()}}.
If the code raises an error, the transaction is instead aborted with
\code{\link[=dbRollback]{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)) (if (getRversion() >= "3.4") withAutoprint else force)(\{ # 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}
}
|
