-
Notifications
You must be signed in to change notification settings - Fork 75
/
transactions.R
54 lines (54 loc) · 1.94 KB
/
transactions.R
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
#' 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.
#'
#' Not all database engines implement transaction management, in which case
#' these methods should not be implemented for the specific
#' [DBIConnection-class] subclass.
#'
#' @template methods
#' @templateVar method_name transactions
#'
#' @inherit DBItest::spec_transaction_begin_commit_rollback return
#' @inheritSection DBItest::spec_transaction_begin_commit_rollback Failure modes
#' @inheritSection DBItest::spec_transaction_begin_commit_rollback Specification
#'
#' @inheritParams dbGetQuery
#' @seealso Self-contained transactions: [dbWithTransaction()]
#' @examplesIf requireNamespace("RSQLite", quietly = TRUE)
#' 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))
#' dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
#' dbCommit(con)
#'
#' dbReadTable(con, "cash")
#' dbReadTable(con, "account")
#'
#' # Rolling back after detecting negative value on account:
#' dbBegin(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) {
#' dbCommit(con)
#' } else {
#' dbRollback(con)
#' }
#'
#' dbReadTable(con, "cash")
#' dbReadTable(con, "account")
#'
#' dbDisconnect(con)
NULL