SQLite Transaction
A transaction is a unit of work performed against a database. Transactions are units of work that are logically completed in a sequence, either manually by the user or automatically by some database program.
A transaction refers to one or more changes to the database. For example, if you are creating a record, updating a record, or deleting a record from a table, you are performing a transaction on that table. It is important to control transactions to ensure data integrity and handle database errors.
In practice, you can combine many SQLite queries into a set and execute all of them together as part of a transaction.
Transaction Properties
Transactions have the following four standard properties, usually abbreviated as ACID:
- Atomicity: Ensures that all operations within the unit of work are successfully completed; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
- Consistency: Ensures that the database properly changes state upon a successfully committed transaction.
- Isolation: Ensures that transaction operations are isolated and transparent to each other.
- Durability: Ensures that the results of a committed transaction persist even in the case of system failure.
Transaction Control
Use the following commands to control transactions:
- BEGIN TRANSACTION: Starts a transaction.
- COMMIT: Saves changes, or you can use the END TRANSACTION command.
- ROLLBACK: Rolls back changes made.
Transaction control commands are used only with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating or dropping tables, as these operations are automatically committed in the database.
BEGIN TRANSACTION Command
A transaction can be started using the BEGIN TRANSACTION command or simply the BEGIN command. Such transactions usually continue until the next COMMIT or ROLLBACK command. However, they will also roll back if the database is closed or an error occurs. Here is the simple syntax to start a transaction:
BEGIN;
or
BEGIN TRANSACTION;
COMMIT Command
The COMMIT command is used to save changes invoked by a transaction to the database.
The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for the COMMIT command is as follows:
COMMIT;
or
END TRANSACTION;
ROLLBACK Command
The ROLLBACK command is used to undo transactions that have not been saved to the database.
The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command.
The syntax for the ROLLBACK command is as follows:
ROLLBACK;
Example
Suppose the COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Now, let's start a transaction and delete records where age = 25 from the table, and finally, we use the ROLLBACK command to undo all changes.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;
Checking the COMPANY table, it still has the following records:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
Now, let's start another transaction, delete records where age = 25 from the table, and finally, we use the COMMIT command to commit all changes.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;
Check the COMPANY table, and the following records are present:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0