By default, SQLite is in auto-commit mode. dbBegin()
starts
a SQLite transaction and turns auto-commit off. dbCommit()
and
dbRollback()
commit and rollback the transaction, respectively and turn
auto-commit on.
DBI::dbWithTransaction()
is a convenient wrapper that makes sure that
dbCommit()
or dbRollback()
is called.
# S4 method for SQLiteConnection dbBegin(conn, .name = NULL, ..., name = NULL) # S4 method for SQLiteConnection dbCommit(conn, .name = NULL, ..., name = NULL) # S4 method for SQLiteConnection dbRollback(conn, .name = NULL, ..., name = NULL)
conn | a |
---|---|
.name | For backward compatibility, do not use. |
... | Needed for compatibility with generic. Otherwise ignored. |
name | Supply a name to use a named savepoint. This allows you to nest multiple transaction |
The corresponding generic functions DBI::dbBegin()
, DBI::dbCommit()
,
and DBI::dbRollback()
.
library(DBI) con <- dbConnect(SQLite(), ":memory:") dbWriteTable(con, "arrests", datasets::USArrests) dbGetQuery(con, "select count(*) from arrests")#> count(*) #> 1 50dbBegin(con) rs <- dbSendStatement(con, "DELETE from arrests WHERE Murder > 1") dbGetRowsAffected(rs)#> [1] 49#> count(*) #> 1 1#> [1] 50dbBegin(con) rs <- dbSendStatement(con, "DELETE FROM arrests WHERE Murder > 5") dbClearResult(rs) dbCommit(con) dbGetQuery(con, "SELECT count(*) FROM arrests")[1, ]#> [1] 16# Named savepoints can be nested -------------------------------------------- dbBegin(con, name = "a") dbBegin(con, name = "b") dbRollback(con, name = "b") dbCommit(con, name = "a") dbDisconnect(con)