[Sqlite] --) Understanding of embedded database transactions and instance operations

A SQLite Transaction (Transaction) is a unit of work that executes a database. A Transaction is a unit or sequence of work completed in a logical order. It can be completed manually or automatically by a database program. Transaction refers to the extension of one or more modified databases.

A SQLite Transaction (Transaction) is a unit of work that executes a database. A Transaction is a unit or sequence of work completed in a logical order. It can be completed manually or automatically by a database program. Transaction refers to the extension of one or more modified databases.

SQLite Transaction (Transaction)

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

Transaction refers to the extension of one or more modified databases. For example, if you are creating a record, updating a record, or deleting a record from the table, you are executing a transaction on the 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 put all these together for execution as part of the transaction.

1. Transaction attributes

A Transaction has the following four standard attributes:

· Atomicity: ensures that all operations in the work unit are successfully completed. Otherwise, the transaction will be terminated when a fault occurs, and the previous operations will be rolled back to the previous state.

· Consistency: ensures that the database changes its status correctly in successfully committed transactions.

· Isolation: Makes transaction operations independent and transparent.

· Durability: ensure that the results or effects of committed transactions still exist in the case of a system failure.

2. Transaction Control

Use the following command to control transactions:

· Begin transaction: Start TRANSACTION processing.

· COMMIT: Save the changes, or use the end transaction command.

· ROLLBACK: roll back the changes.

The transaction control command is only used with the DML command INSERT, UPDATE, and DELETE. They cannot be used when creating or deleting tables because these operations are automatically submitted in the database.

3. begin transaction command

Transactions can be started using the BEGIN Transaction command or simple BEGIN command. Such transactions are usually executed until the next COMMIT or ROLLBACK command is run. However, when the database is closed or an error occurs, the transaction will also be rolled back. The following is a simple syntax for starting a transaction:

BEGIN;

or 

BEGIN TRANSACTION;

4. COMMIT command

The COMMIT command is used to save the transaction call changes to the database.

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

The COMMIT command syntax is as follows:

COMMIT;

or

END TRANSACTION;

5. 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 cancel transactions since the last time the COMMIT or ROLLBACK command was issued.

The ROLLBACK command syntax is as follows:

ROLLBACK;

6. instance operations

[Root @ localhost sqlite-autoconf-3080403] # sqlite3 tim. db

SQLite version 3.8.4.3 16:53:12

Enter ". help" for usage hints.

Sqlite> create table company (id int not null, name varchar (20), age int, address varchar (20), salary decimal (7, 2 ));

Now, let's start a transaction and delete the records with age = 25 from the table. Finally, we use the ROLLBACK command to undo all the changes and start the transaction first, delete the record with AGE 25 and query the COMPANY table. This record is not found, as shown in:

Roll back again and query the COMPANY table again. The record with AGE 25 is restored.

Now, if you start a transaction, delete the record with AGE 25, and execute COMMIT to submit the changes, you will not see the deleted record, as shown below:

Roll back again and query the COMPANY table again. The record with AGE 25 is restored.

Now, if you start a transaction, delete the record with AGE 25, and execute COMMIT to submit the changes, you will not see the deleted record, as shown below:

Reference Article address: http://www.w3cschool.cc/sqlite/sqlite-transaction.html