*** DRAFT ***
About Documentation Download License Support Purchase

SQLite Requirement Matrix Details
lang_transaction.html

Index Summary Markup Original

R-44835-24883-49938-43446-56074-23376-41868-21974 tcl slt th3 src

No reads or writes occur except within a transaction.

/* IMP: R-44835-24883 */
# EVIDENCE-OF: R-44835-24883 No reads or writes occur except within a
# transaction.

R-55258-32329-20179-17327-29048-20222-48155-64370 tcl slt th3 src

Any command that accesses the database (basically, any SQL command, except a few PRAGMA statements) will automatically start a transaction if one is not already in effect.

/* IMP: R-55258-32329 */
# EVIDENCE-OF: R-55258-32329 Any command that accesses the database
# (basically, any SQL command, except a few PRAGMA statements) will
# automatically start a transaction if one is not already in effect.

R-62157-11346-38405-02312-46550-08471-09631-26487 tcl slt th3 src

Automatically started transactions are committed when the last SQL statement finishes.

/* IMP: R-62157-11346 */
# EVIDENCE-OF: R-62157-11346 Automatically started transactions are
# committed when the last SQL statement 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-43433-49136-61285-59919-49849-25550-47556-02276 tcl slt th3 src

Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE.

/* IMP: R-43433-49136 */
# EVIDENCE-OF: R-43433-49136 Transactions can be DEFERRED, IMMEDIATE, or
# EXCLUSIVE.

R-52668-48601-43859-11032-33191-58876-15768-53065 tcl slt th3 src

The default transaction behavior is DEFERRED.

/* IMP: R-52668-48601 */
# EVIDENCE-OF: R-52668-48601 The default transaction behavior is
# DEFERRED.

R-46931-43245-47331-55287-00929-39790-16947-25937 tcl slt th3 src

DEFERRED means that the transaction does not actually start until the database is first accessed.

/* IMP: R-46931-43245 */
# EVIDENCE-OF: R-46931-43245 DEFERRED means that the transaction does
# not actually start until the database is first accessed.

R-11180-04229-25067-24122-45632-49442-37161-45101 tcl slt th3 src

Internally, the BEGIN DEFERRED statement merely sets a flag on the database connection that turns off the automatic commit that would normally occur when the last statement finishes.

/* IMP: R-11180-04229 */
# EVIDENCE-OF: R-11180-04229 Internally, the BEGIN DEFERRED statement
# merely sets a flag on the database connection that turns off the
# automatic commit that would normally occur when the last statement
# finishes.

R-55381-29093-22266-09255-42316-49532-06927-42379 tcl slt th3 src

IMMEDIATE cause the database connection to start a new write immediately, without waiting for a write statement.

/* IMP: R-55381-29093 */
# EVIDENCE-OF: R-55381-29093 IMMEDIATE cause the database connection to
# start a new write immediately, without waiting for a write statement.

R-29924-24161-31191-57990-51475-61688-39790-55251 tcl slt th3 src

EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately.

/* IMP: R-29924-24161 */
# EVIDENCE-OF: R-29924-24161 EXCLUSIVE is similar to IMMEDIATE in that a
# write transaction is started immediately.

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-40892-13487-22632-16999-05016-52970-57179-01588 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 an open read connection.

/* IMP: R-40892-13487 */
# EVIDENCE-OF: R-40892-13487 An attempt to execute COMMIT might also
# result in an SQLITE_BUSY return code if an another thread or process
# has an open read connection.

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-01531-37780-56063-13317-41294-63476-04645-64168 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-01531-37780 */
# EVIDENCE-OF: R-01531-37780 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 of 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.