1.34. SQLite transaction (Transaction)

A Transaction is a unit of work that performs work on a database. A Transaction is a unit or sequence of work that is completed in a logical order, either manually by the user or automatically by a database program.

A Transaction is an extension of one or more change databases. 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.

1.34.1. Properties of the transaction ¶

A transaction (Transaction) has the following four standard attributes, usually abbreviated to ACID according to its initials:

原子性（Atomicity）： Ensure that all operations within the work unit are completed successfully, otherwise, the transaction will be terminated in the event of a failure, and the previous operation will be rolled back to the previous state.
一致性（Consistency)： Ensure that the database changes state correctly on successfully committed transactions.
隔离性（Isolation）： Make transaction operations independent and transparent.
持久性（Durability）： Ensure that the results or effects of committed transactions still exist in the event of a system failure.

1.34.2. Transaction control ¶

Use the following command to control the transaction:

BEGIN TRANSACTION Start transaction processing
COMMIT Save your changes, or you can use the END TRANSACTION Orders.
ROLLBACK Roll back the changes you made

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

1.34.3. BEGIN TRANSACTION command ¶

A transaction (Transaction) can be started using the BEGIN TRANSACTION command or a simple BEGIN command. Such transactions usually continue until the next COMMIT or ROLLBACK command is encountered. However, when the database is shut down or an error occurs, the transaction is also rolled back. The following is a simple syntax for starting a transaction:

BEGIN;

or

BEGIN TRANSACTION;

1.34.4. COMMIT command ¶

COMMIT A command is a transaction command that saves changes invoked by a transaction to the database.

COMMIT The order has been changed since the last time COMMIT Or ROLLBACK All transactions since the command are saved to the database.

COMMIT The syntax of the command is as follows:

COMMIT;

or

END TRANSACTION;

1.34.5. ROLLBACK command ¶

ROLLBACK Commands are transaction commands that are used to undo transactions that have not been saved to the database.

ROLLBACK Command can only be used to undo since last issued COMMIT Or ROLLBACK Transactions since the order.

ROLLBACK The syntax of the command is as follows:

ROLLBACK;

1.34.6. Example ¶

Hypothetical COMPANY The table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
         Paul        32          California  20000.0
         Allen       25          Texas       15000.0
         Teddy       23          Norway      20000.0
         Mark        25          Rich-Mond   65000.0
         David       27          Texas       85000.0
         Kim         22          South-Hall  45000.0
         James       24          Houston     10000.0

Now, let’s start a transaction and delete the record 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 COMPANY Table, there are still the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
         Paul        32          California  20000.0
         Allen       25          Texas       15000.0
         Teddy       23          Norway      20000.0
         Mark        25          Rich-Mond   65000.0
         David       27          Texas       85000.0
         Kim         22          South-Hall  45000.0
         James       24          Houston     10000.0

Now, let’s start another transaction, delete the record 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 COMPANY Table with the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
         Paul        32          California  20000.0
         Teddy       23          Norway      20000.0
         David       27          Texas       85000.0
         Kim         22          South-Hall  45000.0
         James       24          Houston     10000.0