PostgreSQL TRANSACTION (Transaction)

A TRANSACTION (transaction) is a logical unit of work within a database management system, consisting of a finite sequence of database operations.

Database transactions typically include a sequence of read/write operations on the database. They serve two main purposes:

• To provide a method for recovering from failures and returning the database to a normal state, while ensuring consistency even in abnormal states.
• To provide an isolation method between multiple applications concurrently accessing the database, preventing their operations from interfering with each other.

When a transaction is submitted to the database management system (DBMS), the DBMS must ensure that all operations within the transaction are successfully completed and their results are permanently saved in the database. If any operation within the transaction fails, all operations within the transaction must be rolled back to the state before the transaction was executed. Additionally, the transaction should have no impact on the execution of the database or other transactions, making them appear to run independently.

Transaction Attributes

Transactions have four standard attributes, commonly abbreviated as ACID:

• Atomicity: The transaction is executed as a whole; the operations within it are either all performed or none are performed.
• Consistency: The transaction ensures that the database transitions from one consistent state to another. A consistent state means that the data in the database meets integrity constraints.
• Isolation: When multiple transactions are executed concurrently, the execution of one transaction should not affect the execution of others.
• Durability: Modifications made to the database by a committed transaction should be permanently stored in the database.

Example

Someone wants to buy a $100 item using electronic currency at a store, which involves at least two operations:

• Deduct $100 from the person's account.
• Add $100 to the store's account.

A transaction-supporting database management system ensures that both operations (the entire "transaction") are completed together or canceled together, to prevent situations where $100 disappears or appears without justification.

Transaction Control

Use the following commands to control transactions:

• BEGIN TRANSACTION: Start a transaction.
• COMMIT: Confirm the transaction, or use the END TRANSACTION command.
• ROLLBACK: Roll back the transaction.

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

BEGIN TRANSACTION Command

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

BEGIN;

or

BEGIN TRANSACTION;

COMMIT Command

The COMMIT command is used to save the changes called by a transaction to the database, confirming the transaction.

The syntax for the COMMIT command is:

COMMIT;

or

END TRANSACTION;

ROLLBACK Command

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

The syntax for the ROLLBACK command is:

ROLLBACK;

Example

Create the COMPANY table (download the COMPANY SQL file from here), with the following data:

tutorialprodb# 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, delete records where age = 25 from the table, and then use the ROLLBACK command to undo all changes.

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

Check the COMPANY table, and you will still find the following records:

id | name  | age | address   | salary

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, delete records where age = 25 from the table, and finally commit all changes using the COMMIT command.

tutorialprodb=# 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)