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)

Arguments

conn

a SQLiteConnection object, produced by DBI::dbConnect()

.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

See also

The corresponding generic functions DBI::dbBegin(), DBI::dbCommit(), and DBI::dbRollback().

Examples

library(DBI) con <- dbConnect(SQLite(), ":memory:") dbWriteTable(con, "arrests", datasets::USArrests) dbGetQuery(con, "select count(*) from arrests")
#> count(*) #> 1 50
dbBegin(con) rs <- dbSendStatement(con, "DELETE from arrests WHERE Murder > 1") dbGetRowsAffected(rs)
#> [1] 49
dbClearResult(rs) dbGetQuery(con, "select count(*) from arrests")
#> count(*) #> 1 1
dbRollback(con) dbGetQuery(con, "select count(*) from arrests")[1, ]
#> [1] 50
dbBegin(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)