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 ROLLBACK statement.
The 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 SAVEPOINT and RELEASE commands.
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.
Syntax
SQLite 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 COMMIT.
BEGIN [TRANSACTION]; --SQLite statement END [TRANSACTION];
SQLite 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];
SQLite 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];
Examples
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 INSERT statement. UPDATE and 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;