SQLite Transaction

A transaction is a unit of work performed on a database. A transaction is a unit or sequence of work completed in logical order, which can be done manually by a user or automatically by a database program.

A transaction refers to an extension that changes the database one or more times. For example, if you are creating a record, updating a record, or deleting a record from a table, then you are performing a transaction on that table. It is important to control transactions to ensure data integrity and handle database errors.

In fact, you can combine many SQLite queries into a group 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 work unit complete successfully; otherwise, the transaction terminates in the event of a failure, and previous operations are rolled back to their previous state.

  • Consistency:Ensures that the database correctly changes state upon a successfully committed transaction.

  • Isolation:Makes transaction operations independent and transparent to each other.

  • Durability:Ensures that the results or effects of a committed transaction persist even in the event of a system failure.

Transaction Control

Use the following commands to control transactions:

  • BEGIN TRANSACTION: Start transaction processing.

  • COMMIT: Save changes, or you can useEND TRANSACTIONcommand.

  • ROLLBACK: Roll back the changes made.

Transaction control commands are only used with the DML commands INSERT, UPDATE, and DELETE. They cannot be used when creating or dropping tables, because 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 to execute until the next COMMIT or ROLLBACK command is encountered. However, when the database is closed or an error occurs, the transaction processing will also be rolled back. The following is the simple syntax for starting a transaction:

BEGIN;

or 

BEGIN TRANSACTION;

COMMIT Command

The COMMIT command is a transaction command used to save the changes invoked by a transaction to the database.

The COMMIT command saves all transactions since the last COMMIT or ROLLBACK command to the database.

The syntax of the COMMIT command is as follows:

COMMIT;

or

END TRANSACTION;

ROLLBACK Command

The ROLLBACK command is a transaction command used to undo transactions that have not yet been saved to the database.

The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

The syntax of the ROLLBACK command is as follows:

ROLLBACK;

Examples

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 with age = 25 from the table. Finally, we use the ROLLBACK command to undo all changes.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

Check 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 with 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, it has the following records:

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
Other Extensions