Small. Fast. Reliable.
Choose any three.
About Documentation Download License Support Purchase

SQLite Requirement Matrix Details
lang_transaction.html

Index Summary Markup Original

R-49287-50618-11209-64711-03879-11452-32913-52634 tcl slt th3 src

Canonical usage: syntax/begin-stmt.html

/* IMP: R-49287-50618 */
# EVIDENCE-OF: R-49287-50618 -- syntax diagram begin-stmt

R-55215-14389-64693-58336-41647-22667-56465-11834 tcl slt th3 src

Canonical usage: syntax/commit-stmt.html

/* IMP: R-55215-14389 */
# EVIDENCE-OF: R-55215-14389 -- syntax diagram commit-stmt

R-51224-47395-31837-56912-21829-24020-30039-65275 tcl slt th3 src

Canonical usage: syntax/rollback-stmt.html

/* IMP: R-51224-47395 */
# EVIDENCE-OF: R-51224-47395 -- syntax diagram rollback-stmt

R-45327-47888-54359-38403-07061-57780-61820-17405 tcl slt th3 src

No changes can be made to the database except within a transaction.

/* IMP: R-45327-47888 */
# EVIDENCE-OF: R-45327-47888 No changes can be made to the database
# except within a transaction.

R-41155-30776-45495-01405-38282-55666-24776-00512 tcl slt th3 src

Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect.

/* IMP: R-41155-30776 */
# EVIDENCE-OF: R-41155-30776 Any command that changes the database
# (basically, any SQL command other than SELECT) will automatically
# start a transaction if one is not already in effect.

R-04527-41458-36687-16089-60868-43636-56199-14424 tcl slt th3 src

Automatically started transactions are committed when the last query finishes.

/* IMP: R-04527-41458 */
# EVIDENCE-OF: R-04527-41458 Automatically started transactions are
# committed when the last query finishes.

R-36570-50350-22690-62620-40069-12741-59912-39682 tcl slt th3 src

Transactions can be started manually using the BEGIN command.

/* IMP: R-36570-50350 */
# EVIDENCE-OF: R-36570-50350 Transactions can be started manually using
# the BEGIN command.

R-29897-28851-11131-17538-15635-12313-50588-15022 tcl slt th3 src

Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified.

/* IMP: R-29897-28851 */
# EVIDENCE-OF: R-29897-28851 Such transactions usually persist until the
# next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK
# if the database is closed or if an error occurs and the ROLLBACK
# conflict resolution algorithm is specified.

R-11129-23371-40000-09444-50753-04787-03077-31414 tcl slt th3 src

END TRANSACTION is an alias for COMMIT.

/* IMP: R-11129-23371 */
# EVIDENCE-OF: R-11129-23371 END TRANSACTION is an alias for COMMIT.

R-02854-44844-25004-52722-20972-43816-38252-26758 tcl slt th3 src

Transactions created using BEGIN...COMMIT do not nest.

/* IMP: R-02854-44844 */
# EVIDENCE-OF: R-02854-44844 Transactions created using BEGIN...COMMIT
# do not nest.

R-50442-34254-35455-58700-58965-62142-32020-28485 tcl slt th3 src

For nested transactions, use the SAVEPOINT and RELEASE commands.

/* IMP: R-50442-34254 */
# EVIDENCE-OF: R-50442-34254 For nested transactions, use the SAVEPOINT
# and RELEASE commands.

R-11576-11990-36701-63648-24289-51988-09315-26987 tcl slt th3 src

An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the transaction was started by SAVEPOINT or a prior BEGIN.

/* IMP: R-11576-11990 */
# EVIDENCE-OF: R-11576-11990 An attempt to invoke the BEGIN command
# within a transaction will fail with an error, regardless of whether
# the transaction was started by SAVEPOINT or a prior BEGIN.

R-58433-37187-52219-35155-40508-43189-02693-26384 tcl slt th3 src

The COMMIT command and the ROLLBACK command without the TO clause work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.

/* IMP: R-58433-37187 */
# EVIDENCE-OF: R-58433-37187 The COMMIT command and the ROLLBACK command
# without the TO clause work the same on SAVEPOINT transactions as they
# do with transactions started by BEGIN.

R-22212-63356-49544-38225-38690-44409-41066-08853 tcl slt th3 src

Transactions can be deferred, immediate, or exclusive.

/* IMP: R-22212-63356 */
# EVIDENCE-OF: R-22212-63356 Transactions can be deferred, immediate, or
# exclusive.

R-41163-31141-41973-37775-18210-20451-39077-27354 tcl slt th3 src

The default transaction behavior is deferred.

/* IMP: R-41163-31141 */
# EVIDENCE-OF: R-41163-31141 The default transaction behavior is
# deferred.

R-47211-28729-25816-05279-05097-21932-34938-10483 tcl slt th3 src

Deferred means that no locks are acquired on the database until the database is first accessed.

/* IMP: R-47211-28729 */
# EVIDENCE-OF: R-47211-28729 Deferred means that no locks are acquired
# on the database until the database is first accessed.

R-24949-48633-12038-32784-60403-63994-11753-48428 tcl slt th3 src

Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem.

/* IMP: R-24949-48633 */
# EVIDENCE-OF: R-24949-48633 Thus with a deferred transaction, the BEGIN
# statement itself does nothing to the filesystem.

R-09261-30775-60628-01529-14091-08694-38549-11160 tcl slt th3 src

Locks are not acquired until the first read or write operation.

/* IMP: R-09261-30775 */
# EVIDENCE-OF: R-09261-30775 Locks are not acquired until the first read
# or write operation.

R-45406-28279-14866-09757-51891-25484-53799-50932 tcl slt th3 src

The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock.

/* IMP: R-45406-28279 */
# EVIDENCE-OF: R-45406-28279 The first read operation against a database
# creates a SHARED lock and the first write operation creates a RESERVED
# lock.

R-25508-47517-53800-52553-37426-36808-15124-36675 tcl slt th3 src

Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed.

/* IMP: R-25508-47517 */
# EVIDENCE-OF: R-25508-47517 Because the acquisition of locks is
# deferred until they are needed, it is possible that another thread or
# process could create a separate transaction and write to the database
# after the BEGIN on the current thread has executed.

R-32369-26304-55789-00588-64019-11696-17983-36545 tcl slt th3 src

If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used.

/* IMP: R-32369-26304 */
# EVIDENCE-OF: R-32369-26304 If the transaction is immediate, then
# RESERVED locks are acquired on all databases as soon as the BEGIN
# command is executed, without waiting for the database to be used.

R-50920-12644-05555-11139-45005-54781-24239-28471 tcl slt th3 src

After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.

/* IMP: R-50920-12644 */
# EVIDENCE-OF: R-50920-12644 After a BEGIN IMMEDIATE, no other database
# connection will be able to write to the database or do a BEGIN
# IMMEDIATE or BEGIN EXCLUSIVE.

R-28051-59567-56645-15588-46628-55433-20920-32934 tcl slt th3 src

Other processes can continue to read from the database, however.

/* IMP: R-28051-59567 */
# EVIDENCE-OF: R-28051-59567 Other processes can continue to read from
# the database, however.

R-26176-60647-43928-09816-19858-42177-19549-07263 tcl slt th3 src

An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases.

/* IMP: R-26176-60647 */
# EVIDENCE-OF: R-26176-60647 An exclusive transaction causes EXCLUSIVE
# locks to be acquired on all databases.

R-13825-09904-57638-18756-48748-29351-18570-35078 tcl slt th3 src

After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.

/* IMP: R-13825-09904 */
# EVIDENCE-OF: R-13825-09904 After a BEGIN EXCLUSIVE, no other database
# connection except for read_uncommitted connections will be able to
# read the database and no other connection without exception will be
# able to write the database until the transaction is complete.

R-38061-31091-04513-48389-43218-44173-18039-63201 tcl slt th3 src

An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.

/* IMP: R-38061-31091 */
# EVIDENCE-OF: R-38061-31091 An implicit transaction (a transaction that
# is started automatically, not a transaction started by BEGIN) is
# committed automatically when the last active statement finishes. A
# statement finishes when its prepared statement is reset or finalized.
# An open sqlite3_blob used for incremental BLOB I/O counts as an
# unfinished statement. The sqlite3_blob finishes when it is closed.

R-11684-02844-26168-40557-28007-15193-14492-18686 tcl slt th3 src

The explicit COMMIT command runs immediately, even if there are pending SELECT statements.

/* IMP: R-11684-02844 */
# EVIDENCE-OF: R-11684-02844 The explicit COMMIT command runs
# immediately, even if there are pending SELECT statements.

R-25108-07802-40508-32376-19441-31217-21410-63803 tcl slt th3 src

However, if there are pending write operations, the COMMIT command will fail with an error code SQLITE_BUSY.

/* IMP: R-25108-07802 */
# EVIDENCE-OF: R-25108-07802 However, if there are pending write
# operations, the COMMIT command will fail with an error code
# SQLITE_BUSY.

R-59194-45393-22492-52918-12483-50598-53010-59853 tcl slt th3 src

An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated.

/* IMP: R-59194-45393 */
# EVIDENCE-OF: R-59194-45393 An attempt to execute COMMIT might also
# result in an SQLITE_BUSY return code if an another thread or process
# has a shared lock on the database that prevented the database from
# being updated.

R-55708-49653-17675-07799-20555-47879-27152-48993 tcl slt th3 src

When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.

/* IMP: R-55708-49653 */
# EVIDENCE-OF: R-55708-49653 When COMMIT fails in this way, the
# transaction remains active and the COMMIT can be retried later after
# the reader has had a chance to clear.

R-41051-26626-32657-27004-34667-60114-13297-41837 tcl slt th3 src

In more recent versions of SQLite, the ROLLBACK will proceed and pending statements will often be aborted, causing them to return an SQLITE_ABORT or SQLITE_ABORT_ROLLBACK error.

/* IMP: R-41051-26626 */
# EVIDENCE-OF: R-41051-26626 In more recent versions of SQLite, the
# ROLLBACK will proceed and pending statements will often be aborted,
# causing them to return an SQLITE_ABORT or SQLITE_ABORT_ROLLBACK error.

R-03042-35027-57621-08562-39860-20110-45272-44124 tcl slt th3 src

In SQLite version 3.

/* IMP: R-03042-35027 */
# EVIDENCE-OF: R-03042-35027 In SQLite version 3.

R-26708-58601-30431-52488-58911-20234-49790-51615 tcl slt th3 src

If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that can cause an automatic rollback include:

/* IMP: R-26708-58601 */
# EVIDENCE-OF: R-26708-58601 If certain kinds of errors occur within a
# transaction, the transaction may or may not be rolled back
# automatically. The errors that can cause an automatic rollback
# include: SQLITE_FULL: database or disk full SQLITE_IOERR: disk I/O
# error SQLITE_BUSY: database in use by another process SQLITE_NOMEM:
# out or memory

R-15522-56042-47189-16162-53355-39499-48752-21999 tcl slt th3 src

For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction.

/* IMP: R-15522-56042 */
# EVIDENCE-OF: R-15522-56042 For all of these errors, SQLite attempts to
# undo just the one statement it was working on and leave changes from
# prior statements within the same transaction intact and continue with
# the transaction.

R-57255-33789-60728-42833-30002-01027-59947-48122 tcl slt th3 src

However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction.

/* IMP: R-57255-33789 */
# EVIDENCE-OF: R-57255-33789 However, depending on the statement being
# evaluated and the point at which the error occurs, it might be
# necessary for SQLite to rollback and cancel the entire transaction.

R-24408-11841-16708-30331-61955-39086-27640-21297 tcl slt th3 src

An application can tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.

/* IMP: R-24408-11841 */
# EVIDENCE-OF: R-24408-11841 An application can tell which course of
# action SQLite took by using the sqlite3_get_autocommit() C-language
# interface.

R-39822-14939-20605-05288-51207-58465-58259-08970 tcl slt th3 src

If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.

/* IMP: R-39822-14939 */
# EVIDENCE-OF: R-39822-14939 If the transaction has already been rolled
# back automatically by the error response, then the ROLLBACK command
# will fail with an error, but no harm is caused by this.