SQLite transactions

Transaction is a unit of work performed on the database. A transaction is a unit or sequence of work completed in a logical sequence. It can be completed manually by the user or automatically by some database program.

Transaction refers to one or more extensions that change the database. For example, if you are creating a record or 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 fact, you can combine many SQLite queries into a group and execute them all together as part of a transaction.

Attributes of transactions

Transaction has the following four standard attributes, usually ACID according to the acronym:

Atomicity (Atomicity ): Ensure that all operations within the unit of work complete successfully, otherwise the transaction will be terminated on failure and the previous operations will be rolled back to the previous state.
Consistency: Ensure that the database changes state correctly on successfully submitted transactions.
Isolation: Make transaction operations independent and transparent.
Durability (Durability): Ensure that the results or effects of committed transactions still exist in the event of system failure.

Transaction Control

Use the following commands to control transactions:

BEGIN TRANSACTION: Start transaction processing.
COMMIT: Save changes, or you can use the END TRANSACTION command.
ROLLBACK: Roll back the changes.

Transaction control commands are only used with the DML commands INSERT, UPDATE and DELETE. They cannot be used when creating a table or dropping a table because these operations are automatically committed in the database.

BEGIN TRANSACTION command

A transaction can be started using the BEGIN TRANSACTION command or a simple BEGIN command. Such transactions typically continue until the next COMMIT or ROLLBACK command is encountered. However, the transaction will also be rolled back when the database is closed or an error occurs. The following is the simple syntax to start a transaction:

BEGIN;

or 

BEGIN TRANSACTION;

COMMIT command

The COMMIT command is the transaction command used to save the changes called by the 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 a transaction that has not 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;

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 us start a transaction and start from The record with age = 25 is deleted 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, there are still 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 us start another transaction to delete the age = 25 records from the table, and finally we use the COMMIT command to commit all Change.

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

Check the COMPANY table, there are 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