Using Transaction

Introduction

A transaction is a collection of one or more SQL statements grouped together into a single unit of work.
The goal is to ensure atomicity in ACID compliance, meaning either all statements within the transaction succeed and are recorded to the database (COMMIT), or if a single statement fails, all previous statements are aborted and rolled back as if nothing ever happened (ROLLBACK).
Example: You transfer money at a bank. The system must perform two tasks:

Deduct money from your account.
Credit money to the recipient's account. These two statements must reside in the same transaction. If the system loses power while running the first statement and the second statement cannot execute, the system will perform a ROLLBACK to return your money, preventing you from losing money when the transaction fails.

Transaction and Lock

Regarding the internal operation of PostgreSQL, a transaction must depend on and use locks.
The relationship between them is an inseparable symbiotic relationship serving a single purpose, which is to protect data integrity.
A transaction uses locks as a tool to ensure isolation (the I in ACID). During processing within a transaction, other processes cannot intervene to manipulate or corrupt your data.

The process will be as follows:

When your transaction begins modifying a row of data, it will trigger an exclusive row lock on that row.
This lock exists throughout the lifecycle of the transaction and is only released when the transaction ends (by a COMMIT or ROLLBACK command).
Without a lock, other transactions would step in and overwrite the data that your transaction is processing, leading to corrupted and chaotic data.

How to Use

To use a Transaction, you can implement it in the following ways:

Manual usage: Use BEGIN combined with COMMIT or ROLLBACK.
Implicit Transaction: Just use statements normally.
- Postgres will automatically detect based on the statement and add BEGIN to start the transaction.
- Use the corresponding lock mode according to the level.
- If executed successfully, it will automatically COMMIT and if there is an error, it will also automatically run ROLLBACK to close the transaction and release the lock.

Nested Transactions

PostgreSQL does not actually support True Nested Transactions where multiple BEGIN commands are nested within each other.
If you use two BEGIN commands, it will ignore the second BEGIN and show WARNING: there is already a transaction in progress. When running COMMIT or ROLLBACK, only one command is needed to close the transaction.

Subtransactions

To achieve the effect of nested transactions and solve problems where a child transaction error only cancels the child, while a parent transaction error cancels everything, Postgres still has a mechanism to handle this by using a Subtransaction based on the SAVEPOINT command. The rules of operation are as follows:

If the parent is canceled, all children are canceled: If you call ROLLBACK at the top-level transaction, all internal changes (including child transactions that executed successfully earlier) are completely canceled.
If the child is canceled, the parent can survive: If an internal child transaction fails, you can choose ROLLBACK TO SAVEPOINT to abort only its part. The external parent transaction remains fully valid and can continue running, then COMMIT normally.
Child success is not everything: When a child transaction completes and calls RELEASE SAVEPOINT (similar to a child commit), the child's data is not yet actually written to the hard disk. It still has to wait for the parent transaction. Only when the parent transaction calls COMMIT will the data of both parent and child officially take effect across the entire system. Performance warning: Do not abuse nesting too many SAVEPOINT levels or writing too many EXCEPTION blocks in a function. Postgres only stores a maximum of 64 subtransactions in fast RAM memory. If you nest deeper or generate too many subtransactions within a large transaction, Postgres will have to write this information to the hard disk, causing your system speed to suffer serious non-linear performance degradation.

You can use the BEGIN ... EXCEPTION ... END block in PL/pgSQL when writing Functions or Procedures:

This error-handling block will automatically trigger a Subtransaction to catch errors without crashing the entire external transaction.
Note that using an explicit transaction like BEGIN/COMMIT/ROLLBACK is only supported in a Procedure and cannot be used in a Function.

Detail

First, let us create the tables as follows, where we will look at an example of placing an order and applying a discount coupon.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL CONSTRAINT check_stock CHECK (stock_quantity >= 0)
);

CREATE TABLE coupons (
    code VARCHAR(20) PRIMARY KEY,
    discount_amount DECIMAL(10,2) NOT NULL,
    remaining_uses INT NOT NULL CONSTRAINT check_uses CHECK (remaining_uses >= 0)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    applied_coupon_code VARCHAR(20) REFERENCES coupons(code),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    price_at_purchase DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Next is how to use a transaction and subtransaction:

BEGIN;

UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE id = 1;

INSERT INTO orders (id, customer_id, total_amount, applied_coupon_code) 
VALUES (1001, 42, 2000.00, NULL);

INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase) 
VALUES (1001, 1, 1, 2000.00);

SAVEPOINT apply_discount_subtx;
    UPDATE coupons 
    SET remaining_uses = remaining_uses - 1 
    WHERE code = 'EXPIRED_CODE';
    
    UPDATE orders 
    SET total_amount = total_amount - 50.00, applied_coupon_code = 'EXPIRED_CODE' 
    WHERE id = 1001;

In reality, during a sale promotion, the number of coupons is limited. If a user creates an order but enters an expired coupon, they are still allowed to create the order normally, it is just that when calculating the total, they will not receive the discount from that coupon.
Here, the discount coupon application part will be separated into a subtransaction, so even if an error occurs during this process, you can still ROLLBACK only that transaction back to the SAVEPOINT, rather than canceling the entire transaction.
You will see our process is:
- Begin by deducting the quantity in the products table.
- Create orders and order_items.
- Create a SAVEPOINT for the subtransaction.
- Deduct the remaining uses of the coupon and if the coupon is exhausted, an error will occur here.
- If successful, apply the discount to that order.

If an error occurs, you only need to ROLLBACK to the created SAVEPOINT:

ROLLBACK TO SAVEPOINT apply_discount_subtx;
COMMIT;

SELECT * FROM orders;

Note that there must be a command to COMMIT or ROLLBACK for the main external transaction to close the transaction and the subtransactions.
This is just an implementation example in SQL. In reality, when you use ORMs to implement logic in source code, it will convert the programming language you use into equivalent SQL code like this into transactions and subtransactions for use.
After completion, you can see that even though the subtransaction was rolled back, the order was still successfully created without a coupon code.