SQLite is a relational database management system (RDBMS), which means that it uses a transaction-based model to ensure data consistency.
Any SQL command (select, insert, update, delete, create, drop, …) that accesses the SQLite database (except for a few PRAGMA statements) will automatically start a
TRANSACTION. Automatically started transactions are committed when the last SQL statement completes.
In SQLite, a transaction is started with the
BEGIN TRANSACTION statement, and then one or more SQL statements are executed. Finally, the transaction is ended with either a
COMMIT or a
COMMIT statement saves all the changes that have been made during the transaction, while the
ROLLBACK statement undoes all the changes.
SQLite uses what is called an
ACID (Atomicity, Consistency, Isolation, and Durability) approach to transactions. This means that each transaction is treated as a single unit of work, and either all the changes are made or none of them are.
This ensures that the database remains consistent even if there are power failures or other unexpected events. SQLite also supports nested transactions, which means that you can have one transaction inside another.
Transactions created using BEGIN…COMMIT are not nested. For nested transactions, use the
Nested transactions are useful if you want to make sure that a group of changes are all made, or none of them are. For example, if you’re transferring money from one bank account to another, you might want to start a transaction, make the changes to both accounts, and then commit the transaction.
If an error occurs during the transaction, you can rollback the changes and try again. SQLite’s nested transactions make this easy to do.
BEGIN TRANSACTION starts the transaction, inside which SQL instructions are written, then the transaction is closed with the command
END TRANSACTION. In the SQLite database END TRANSACTION is an alias for
BEGIN [TRANSACTION]; --SQLite statement END [TRANSACTION];
COMMIT saves the changes made by the executed SQL statements, but also closes the transaction. As a result, there is no need to write the END TRANSACTION command.
BEGIN [TRANSACTION]; --SQLite statement COMMIT [TRANSACTION];
ROLLBACK cancels changes made by SQL statements in a transaction. The most used is ROLLBACK, but you can use
ROLLBACK TRANSACTION or
ROLLBACK TO SAVEPOINT.
BEGIN [TRANSACTION]; --SQLite statement ROLLBACK [TRANSACTION] [TO SAVEPOINT savepoint_name];
In the first example, the transaction is started with
BEGIN, then the test1 table is created using the
CREATE TABLE definition, after creating the table, three rows are added to the table using the
DELETE are also used inside the transaction. The transaction is completed with COMMIT.
begin; create table test1(a int, b text); insert into test1(a,b)values(1,'a'); insert into test1(a,b)values(2,'b'); insert into test1(a,b)values(3,'c'); update test1 set b = 'x' where a=2; delete from test1 where a=3; commit;
The second example shows how to use
ROLLBACK, start with
BEGIN, then insert and immediately ROLLBACK, which cancels the addition of the row in the table.
begin; insert into test1(a,b)values(4,'d'); rollback;