PostgreSQL TRANSACTION (Transaction)

TRANSACTION (transaction) is a logical unit in the execution process of a database management system, consisting of a finite sequence of database operations.

Database transactions typically consist of a sequence of reads on the database/Write operations. Include the following two purposes:

• Provides a method for recovering from failures to a normal state for a sequence of database operations, while also providing a method for the database to maintain consistency even in abnormal states.

• When multiple applications access the database concurrently, an isolation method can be provided between these applications to prevent their operations from interfering with each other.

When a transaction is submitted to a database management system (DBMS), the DBMS needs to ensure that all operations in the transaction are successfully completed and their results are permanently stored in the database. If some operations in the transaction are not successfully completed, all operations in the transaction need to be rolled back to the state before the transaction was executed; at the same time, the transaction has no impact on the database or the execution of other transactions, and all transactions seem to be running independently.

Transaction properties

Transactions have the following four standard properties, usually abbreviated as ACID:

• Atomicity (Atomicity): A transaction is executed as a whole, and the operations contained within it are either all executed or none executed.

• Consistency (Consistency): A transaction should ensure that the state of the database changes from one consistent state to another. The meaning of consistent state is that the data in the database should satisfy integrity constraints.

• Isolation (Isolation): When multiple transactions are executed concurrently, the execution of one transaction should not affect the execution of other transactions.

• Durability (Durability): Modifications made to the database by a committed transaction should be permanently stored in the database.

Example

Someone wants to use electronic money to buy something in the store100 yuan worth of things, which include at least two operations:

• The person's account decreases 100 yuan.

• Store account increases100 yuan.

A database management system that supports transactions must ensure that both of the above operations (the entire 'transaction') can be completed, or cancelled together, otherwise 100 yuan simply disappears or appears.

Transaction control

Use the following commands to control transactions:

• BEGIN TRANSACTION: Start a transaction.

• COMMIT: Transaction confirmation, or the 'END TRANSACTION' command can be used.

• ROLLBACK: Transaction rollback.

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

BEGIN TRANSACTION command

Transactions can be initiated using the 'BEGIN TRANSACTION' command or the simple 'BEGIN' command. Such transactions usually continue to execute until they encounter the next 'COMMIT' or 'ROLLBACK' command. However, transaction processing will also roll back if the database is closed or an error occurs. The following is a simple syntax for initiating a transaction:

BEGIN;
or
BEGIN TRANSACTION;

COMMIT command

The COMMIT command is used to save the changes made by the transaction to the database, that is, to confirm the transaction.

The syntax of the COMMIT command is as follows：

COMMIT;
or
END TRANSACTION;

ROLLBACK command

The ROLLBACK command is used to undo the transaction commands that have not been saved to the database, that is, to roll back the transaction.

The syntax of the ROLLBACK command is as follows：

ROLLBACK;

Online Example

Create COMPANY table (Download COMPANY SQL file ），data content as follows：

w3codeboxdb# select　*　from COMPANY;
　id　|　name　　|　age　|　address　　　|　salary
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　|　California|　　20000
　　2　|　Allen　|　　25　|　Texas　　　　　|　　15000
　　3　|　Teddy　|　　23　|　Norway　　　　|　　20000
　　4　|　Mark　　|　　25　|　Rich-Mond　|　　65000
　　5　|　David　|　　27　|　Texas　　　　　|　　85000
　　6　|　Kim　　　|　　22　|　South-Hall|　　45000
　　7　|　James　|　　24　|　Houston　　　|　　10000
(7　rows)

Now, let's start a transaction and delete age = from the table 25 The record, finally, we use the ROLLBACK command to cancel all changes.

w3codeboxdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE =　25;
ROLLBACK;

Check the COMPANY table, there are still the following records:

　id　|　name　　|　age　|　address　　　|　salary
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　|　California|　　20000
　　2　|　Allen　|　　25　|　Texas　　　　　|　　15000
　　3　|　Teddy　|　　23　|　Norway　　　　|　　20000
　　4　|　Mark　　|　　25　|　Rich-Mond　|　　65000
　　5　|　David　|　　27　|　Texas　　　　　|　　85000
　　6　|　Kim　　　|　　22　|　South-Hall|　　45000
　　7　|　James　|　　24　|　Houston　　　|　　10000

Now, let's start another transaction and delete age = from the table 25 The record, finally we use the COMMIT command to submit all changes.

w3codeboxdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE =　25;
COMMIT;

Check the COMPANY table, the record has been deleted:

id　|　name　　|　age　|　address　　　　|　salary
----+-------+-----+------------+--------
　　1　|　Paul　　|　　32　|　California　|　　20000
　　3　|　Teddy　|　　23　|　Norway　　　　　|　　20000
　　5　|　David　|　　27　|　Texas　　　　　　|　　85000
　　6　|　Kim　　　|　　22　|　South-Hall　|　　45000
　　7　|　James　|　　24　|　Houston　　　　|　　10000
(5　rows)