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.

A database transaction typically consists of a sequence of read/write operations on the database. It serves the following two purposes:

It provides a method for the database operation sequence to recover from failure to a normal state, and also provides a method for the database to remain consistent even under abnormal conditions.
When multiple applications access the database concurrently, it can provide an isolation method between these applications to prevent their operations from interfering with each other.

When a transaction is submitted to the database management system (DBMS), the DBMS needs to ensure that all operations in the transaction are completed successfully and their results are permanently saved in the database. If any operation in the transaction is not completed successfully, 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 effect on the database or the execution of other transactions; all transactions appear to run independently.

Transaction Properties

A transaction has the following four standard properties, often abbreviated as ACID:

Atomicity: The transaction is executed as a whole; the operations on the database contained in it are either all executed or none are executed.
Consistency: The transaction should ensure that the database state transitions from one consistent state to another consistent state. A consistent state means that the data in the database should satisfy integrity constraints.
Isolation: When multiple transactions execute concurrently, the execution of one transaction should not affect the execution of other transactions.
Durability: Modifications made to the database by a committed transaction should be permanently saved in the database.

Examples

A person wants to buy something worth 100 yuan in a store using electronic money. This involves at least two operations:

The person's account decreases by 100 yuan.
The store's account increases by 100 yuan.

A database management system that supports transactions is to ensure that the above two operations (the entire "transaction") can both be completed or both be canceled; otherwise, there would be a situation where 100 yuan disappears or appears out of thin air.

Transaction Control

Use the following commands to control transactions:

BEGIN TRANSACTION: Start a transaction.

COMMIT: Commit the transaction, or you can use the END TRANSACTION command.

ROLLBACK: Roll back the transaction.

Transaction control commands are only used with 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 a transaction usually continues to execute until the next COMMIT or ROLLBACK command is encountered. However, transaction processing will also roll back when the database is closed or an error occurs. The following is the simple syntax for starting a transaction:

BEGIN;

或者

BEGIN TRANSACTION;

COMMIT Command

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

The syntax of the COMMIT command is as follows:

COMMIT;

或者

END TRANSACTION;

ROLLBACK Command

The ROLLBACK command is a transaction command used to undo changes that have not yet been saved to the database, i.e., to roll back the transaction.

The syntax of the ROLLBACK command is as follows:

ROLLBACK;

Example

Create the COMPANY table (Download the COMPANY SQL file), with the following data:

exampledb# 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 us start a transaction, delete the records with age = 25 from the table, and finally use the ROLLBACK command to undo all changes.

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

Check the COMPANY table, it still has 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 us start another transaction, delete the records with age = 25 from the table, and finally use the COMMIT command to commit all changes.

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

Check the COMPANY table, the records have 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)

Other Extensions

PostgreSQL Advanced