Table of Contents for

Using SQLite

Syntax

Common Usage

BEGIN;
BEGIN EXCLUSIVE TRANSACTION;

Description

The BEGIN TRANSACTION command starts an explicit transaction. Once started, an explicit transaction will remain open until the transaction is either committed using COMMIT TRANSACTION, or rolled back using ROLLBACK TRANSACTION. Transactions are used to group multiple discrete commands (such as a sequence of INSERT commands that insert rows into cross-referenced tables) into a single logical command.

Transactions are ACID-compliant, in that they are atomic, consistent, isolated, and durable. They’re an important part of correct database design and database use. For more information, see Transaction Control Language.

All changes and modifications to a database are done within a transaction. Normally, SQLite is in autocommit mode. In this mode, each and every statement that might modify the database is automatically wrapped in its own transaction. Each command begins a transaction, executes the given command statement, and attempts to commit the changes. If any error occurs, the wrapper transaction is rolled back.

The BEGIN command turns off autocommit mode, opening a transaction and leaving it open until it is explicitly committed or rolled back. This allows multiple commands (and multiple modifications) to be packaged into a single transaction. Once a COMMIT or ROLLBACK is issued, the database connection is put back into autocommit mode.

Transactions cannot be nested. For that functionality, use SAVEPOINT. Executing a BEGIN command while the database connection is already in a transaction will result in an error, but will not change the state of the preexisting transaction.

There is a significant cost associated with committing a transaction. In autocommit mode, this cost is seen by every command. In some situations, it can be prudent to wrap several commands into a single transaction. This helps amortize the transaction cost across several statements. When doing large operations, such as bulk inserts, it is not unusual to wrap several hundred, or even a thousand or more INSERT commands into a single transaction. The only caution in doing this is that a single error can cause the whole transaction to rollback, so you need to be prepared to re-create all of the rolled back INSERT statements.

In SQLite, transactions are controlled by locks. You can specify the locking behavior you want with the modifier DEFERRED, IMMEDIATE, or EXCLUSIVE. The default mode is DEFERRED, in which no locks are acquired until they are needed. This allows the highest level of concurrency, but also means the transaction may find itself needing a lock it cannot acquire, and may require a rollback. The IMMEDIATE mode attempts to immediately acquire the reserved lock, allowing other connections to continue to read from the database, but reserving the right to elevate itself to write status at any time. Starting an EXCLUSIVE transaction will attempt to grab the exclusive lock, allowing full access to the database, but denying access by any other database connection. Higher locking levels means greater certainty that a transaction can be successfully committed at the cost of lower levels of concurrency.

For more information on SQLite’s locking and concurrency model, see http://sqlite.org/lockingv3.html.

See Also

COMMIT TRANSACTION, ROLLBACK TRANSACTION, SAVEPOINT, RELEASE SAVEPOINT