SQL 68 🛢️ Database Transactions and ACID Properties
A transaction is a group of SQL statements that execute as a single unit. Either all of the statements succeed and their changes are permanently written, or one of them fails and every change is undone as if nothing happened. There is no partial state: the database moves from one consistent state to another, and the intermediate states are invisible to the other sessions. This all-or-nothing property is what separates a transaction from a sequence of statements. A bank transfer that debits one account and credits another must be a transaction; if the debit succeeds and the credit fails, the money disappears. The transaction is the guarantee that the two statements are one operation .
The ACID properties are the formal description of what a transaction guarantees. Atomicity means all-or-nothing: the transaction’s statements either all take effect or none do. Consistency means the database moves from one valid state to another, respecting the constraints, the foreign keys, and the triggers. Isolation means the concurrent transactions do not see each other’s intermediate states; the degree of isolation is determined by the isolation level. Durability means the committed changes survive a crash, a power loss, or a restart, because they are written to the write-ahead log before the transaction is acknowledged .
The isolation is the property with the most nuance. The SQL standard defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each level prevents a different set of the concurrency anomalies: the dirty read, the non-repeatable read, the phantom read, and the serialization anomaly. The higher the level, the fewer the anomalies and the more the locking and the lower the concurrency. PostgreSQL’s default is READ COMMITTED, which prevents the dirty reads but allows the non-repeatable reads and the phantom reads. The SERIALIZABLE level is the strictest, and it can abort the transactions that cannot be serialized, requiring the application to retry them .
This chapter covers three areas. First, why transactions exist — the all-or-nothing problem and the concurrency problem. Second, the ACID properties — the atomicity, the consistency, the isolation, and the durability, and how the database implements each one. Third, the isolation levels and the anomalies — the four levels, the four anomalies, and the patterns for the BEGIN, the COMMIT, the ROLLBACK, the SAVEPOINT, and the retry logic. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the transaction lifecycle.
Key point: A transaction is a group of SQL statements that execute as a single unit. The ACID properties are the guarantees: atomicity (all-or-nothing), consistency (the valid state to the valid state), isolation (the concurrent transactions do not interfere), and durability (the committed changes survive the crash). The BEGIN starts the transaction, the COMMIT writes the changes, and the ROLLBACK undoes them. The isolation levels control the concurrency anomalies, and the SERIALIZABLE level can require the application to retry the aborted transactions.
Why transactions exist
The all-or-nothing problem. A business operation is often composed of several statements. The transfer debits one account and credits another. The order inserts the order row, the order items, and decrements the inventory. The user registration inserts the user, the profile, and the default settings. If one statement fails after the others have succeeded, the database is left in a state that violates the business rules. The transaction groups the statements, so the failure of one undoes all of them .
The concurrency problem. The database serves many sessions at once. Two sessions read and write the same rows, and the interleaving of their operations can produce the results that neither would produce alone. The classic example: two sessions read the same balance, each adds 100, and each writes back the result. The final balance is the original plus 100, not plus 200, because the second write overwrites the first. The isolation property controls the interleaving, and the isolation levels determine how strictly .
The crash problem. The database can crash at any moment: the power loss, the kernel panic, the hardware failure. The committed transactions must survive the crash, and the uncommitted transactions must be rolled back when the database recovers. The write-ahead log is the mechanism: the changes are written to the log before the transaction is committed, and the recovery replays the log to restore the committed state and undo the uncommitted .
The abstraction problem. The application programmer should not have to think about the concurrency and the recovery. The transaction is the abstraction: the programmer marks the beginning and the end, and the database handles the locking, the logging, and the recovery. The programmer writes the business logic, and the database provides the ACID guarantees .
The consistency problem. The constraints, the foreign keys, and the triggers must hold at the transaction boundaries. A transaction that violates the constraint fails, and the failure rolls back the whole transaction. The consistency is the property that the database is always in a valid state from the business perspective .
The trade-off. The transactions have the cost. The locking reduces the concurrency, the logging adds the I/O, and the strict isolation can cause the aborts and the retries. The higher the isolation level, the stronger the guarantee and the lower the concurrency. The trade-off is between the correctness and the performance, and the choice of the isolation level is the way to balance them.
a. The ACID properties
Atomicity. The transaction is indivisible. The BEGIN starts it, and the COMMIT makes all the changes permanent, or the ROLLBACK undoes all of them. If the session crashes in the middle, the recovery undoes the uncommitted changes. The savepoint allows the partial rollback within the transaction.
Consistency. The transaction takes the database from one valid state to another. The constraints, the foreign keys, the unique indexes, and the triggers must hold at the COMMIT. A transaction that would violate the constraint fails, and the failure rolls back the whole transaction.
Isolation. The concurrent transactions do not see each other’s uncommitted changes. The degree of isolation is determined by the isolation level. The READ UNCOMMITTED allows the dirty reads, and the SERIALIZABLE prevents the serialization anomalies. The isolation is implemented with the locking or the multiversion concurrency control (the MVCC).
Durability. The committed changes survive the crash. The write-ahead log records the changes before the transaction is committed, and the recovery replays the log after the crash. The fsync flushes the log to the disk, and the synchronous_commit controls whether the commit waits for the flush.
The implementation. The database implements the ACID with the combination of the logging, the locking, the MVCC, and the recovery. The write-ahead log is for the atomicity and the durability. The locks and the MVCC are for the isolation. The constraints and the triggers are for the consistency.
b. The transaction control
The BEGIN and the COMMIT. The BEGIN starts the transaction, and the COMMIT writes the changes.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;The ROLLBACK. The ROLLBACK undoes the changes since the BEGIN (or the last savepoint).
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- The error occurs, or the check fails.
ROLLBACK;The SAVEPOINT. The SAVEPOINT marks a point within the transaction, and the ROLLBACK TO SAVEPOINT undoes the changes since the savepoint without undoing the whole transaction.
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error occurs.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted, the order_items is undone.
COMMIT;The RELEASE SAVEPOINT removes the savepoint, and the changes since the savepoint become part of the transaction.
The SET TRANSACTION. The SET TRANSACTION ISOLATION LEVEL sets the isolation level for the current transaction.
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- the statements
COMMIT;The SET SESSION CHARACTERISTICS sets the default for the session, and the SET TRANSACTION sets it for the current transaction only.
The implicit transactions. In the autocommit mode, each statement is its own transaction. The BEGIN disables the autocommit until the COMMIT or the ROLLBACK. The PostgreSQL, the MySQL, and the Oracle have the autocommit on by default, and the SQL standard does not define the autocommit.
c. The isolation levels and the anomalies
The anomalies. The four concurrency anomalies are:
- The dirty read: the transaction reads the uncommitted data of another transaction. If the other transaction rolls back, the read data is invalid.
- The non-repeatable read: the transaction reads the same row twice and gets the different values, because another transaction modified the row between the reads.
- The phantom read: the transaction runs the same query twice and gets the different sets of rows, because another transaction inserted or deleted the rows between the queries.
- The serialization anomaly: the result of the concurrent transactions is inconsistent with any serial execution of them.
The isolation levels. The SQL standard defines the four levels and the anomalies they prevent:
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible |
READ COMMITTED | Not possible | Possible | Possible |
REPEATABLE READ | Not possible | Not possible | Possible (the standard) |
SERIALIZABLE | Not possible | Not possible | Not possible |
The READ UNCOMMITTED is the weakest, and the SERIALIZABLE is the strongest. The PostgreSQL’s READ UNCOMMITTED behaves like the READ COMMITTED, because the MVCC does not allow the dirty reads.
The PostgreSQL specifics. The PostgreSQL’s REPEATABLE READ also prevents the phantom reads, so it is stronger than the standard requires. The SERIALIZABLE uses the Serializable Snapshot Isolation (the SSI), which detects the serialization anomalies and aborts one of the transactions. The aborted transaction must be retried by the application.
The isolation and the locking. The SERIALIZABLE and the REPEATABLE READ in the PostgreSQL use the MVCC, which does not block the readers. The SELECT FOR UPDATE and the SELECT FOR SHARE take the explicit locks, which block the concurrent writers.
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- The row is locked until the commit.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;The retry logic. The SERIALIZABLE transactions can be aborted with the serialization failure (the SQLSTATE 40001). The application must catch the error and retry the transaction.
BEGIN;
-- the statements
COMMIT;
-- The error: ERROR: could not serialize access due to concurrent updateThe application catches the 40001 and retries the whole transaction. The retry is the requirement of the SERIALIZABLE level.
Complete Example Session
-- ============================================
-- PART 1: THE BASE TABLES
-- ============================================
CREATE TABLE accounts (
id INT PRIMARY KEY,
name TEXT NOT NULL,
balance NUMERIC(10, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 500);
-- ============================================
-- PART 2: THE BASIC TRANSACTION
-- ============================================
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SELECT * FROM accounts;
-- ============================================
-- PART 3: THE ROLLBACK
-- ============================================
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- The check fails, or the error occurs.
ROLLBACK;
SELECT * FROM accounts; -- unchanged
-- ============================================
-- PART 4: THE SAVEPOINT
-- ============================================
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted.
COMMIT;
-- ============================================
-- PART 5: THE CONSTRAINT AND THE ROLLBACK
-- ============================================
BEGIN;
UPDATE accounts SET balance = balance - 2000 WHERE id = 1;
-- ERROR: new row for relation "accounts" violates check constraint
-- "accounts_balance_check"
ROLLBACK;
-- The transaction is aborted, and the changes are undone.
-- ============================================
-- PART 6: THE ISOLATION LEVEL
-- ============================================
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- the statements
COMMIT;
-- ============================================
-- PART 7: THE FOR UPDATE LOCK
-- ============================================
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- The row is locked.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- ============================================
-- PART 8: THE SERIALIZATION FAILURE
-- ============================================
-- The session 1:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1;
-- The session 2:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1;
-- The session 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- The session 2:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
-- The application retries the session 2's transaction.
-- ============================================
-- PART 9: THE ISOLATION LEVELS COMPARISON
-- ============================================
-- The READ COMMITTED (the PostgreSQL default):
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- The other session commits a change.
SELECT balance FROM accounts WHERE id = 1; -- the different value
COMMIT;
-- The REPEATABLE READ:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- The other session commits a change.
SELECT balance FROM accounts WHERE id = 1; -- the same value
COMMIT;
-- ============================================
-- PART 10: THE COMPLETE TRANSFER
-- ============================================
CREATE OR REPLACE FUNCTION transfer(
p_from INT,
p_to INT,
p_amount NUMERIC
) RETURNS void AS $$
DECLARE
v_balance NUMERIC;
BEGIN
-- Lock the rows in the consistent order to prevent the deadlocks.
IF p_from < p_to THEN
SELECT balance INTO v_balance FROM accounts WHERE id = p_from FOR UPDATE;
SELECT balance FROM accounts WHERE id = p_to FOR UPDATE;
ELSE
SELECT balance INTO v_balance FROM accounts WHERE id = p_to FOR UPDATE;
SELECT balance FROM accounts WHERE id = p_from FOR UPDATE;
END IF;
-- Check the balance.
IF v_balance < p_amount THEN
RAISE EXCEPTION 'Insufficient funds: the balance is %', v_balance;
END IF;
-- The transfer.
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT transfer(1, 2, 100);
COMMIT;
SELECT * FROM accounts;The ten parts show the base tables, the basic transaction, the rollback, the savepoint, the constraint and the rollback, the isolation level, the FOR UPDATE lock, the serialization failure, the isolation levels comparison, and the complete transfer.
Quick Reference
The Transaction Control
| Statement | Purpose |
|---|---|
BEGIN | Start the transaction |
COMMIT | Write the changes |
ROLLBACK | Undo the changes |
SAVEPOINT name | Mark a point |
ROLLBACK TO SAVEPOINT name | Undo to the point |
RELEASE SAVEPOINT name | Remove the point |
SET TRANSACTION ISOLATION LEVEL | Set the level |
The ACID Properties
| Property | Meaning |
|---|---|
| Atomicity | The all-or-nothing |
| Consistency | The valid state to the valid state |
| Isolation | The concurrent transactions do not interfere |
| Durability | The committed changes survive the crash |
The Isolation Levels
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible |
READ COMMITTED | No | Possible | Possible |
REPEATABLE READ | No | No | No (the PostgreSQL) |
SERIALIZABLE | No | No | No |
The Isolation Anomalies
| Anomaly | Meaning |
|---|---|
| The dirty read | The uncommitted data of another transaction |
| The non-repeatable read | The same row, the different values |
| The phantom read | The same query, the different rows |
| The serialization anomaly | The inconsistent with any serial order |
The PostgreSQL Defaults
| Setting | Default |
|---|---|
| The isolation level | READ COMMITTED |
| The autocommit | On |
The synchronous_commit | On |
The statement_timeout | 0 (no timeout) |
Best Practices
✅ Do This:
-- Use the BEGIN and the COMMIT for the multi-statement operations
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- ✅
-- Use the ROLLBACK on the error
BEGIN;
-- the statements
ROLLBACK; -- ✅
-- Use the SAVEPOINT for the partial rollback
SAVEPOINT after_order; -- ✅
-- Use the FOR UPDATE for the row locks
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- ✅
-- Use the SERIALIZABLE for the critical transactions
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- ✅
-- Use the consistent lock order to prevent the deadlocks
-- Lock the lower id first -- ✅
-- Retry the serialization failures
-- Catch the 40001 and retry -- ✅❌ Don’t Do This:
-- Don't leave the transaction open
BEGIN;
-- the statements
-- the COMMIT is missing // ❌
-- Don't use the SERIALIZABLE without the retry
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- the error 40001, no retry // ❌
-- Don't lock the rows in the inconsistent order
-- The deadlock // ❌
-- Don't use the SELECT without the FOR UPDATE for the read-modify-write
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = ... WHERE id = 1; // ❌
-- Don't hold the transaction open during the user interaction
BEGIN;
-- the user input
-- the long lock // ❌
-- Don't mix the DDL and the DML without the transaction
-- The partial schema change // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| The transaction is left open | The missing COMMIT | Always commit or rollback |
| The serialization failure | The SERIALIZABLE | Retry the transaction |
| The deadlock | The inconsistent lock order | Lock in the consistent order |
| The non-repeatable read | The READ COMMITTED | Use the REPEATABLE READ |
| The phantom read | The READ COMMITTED | Use the REPEATABLE READ or the SERIALIZABLE |
| The long lock | The open transaction | Commit as soon as possible |
| The lost update | The read-modify-write without the lock | Use the FOR UPDATE |
Real-World Examples
1. The Basic Transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;2. The Rollback
BEGIN;
-- the statements
ROLLBACK;3. The Savepoint
SAVEPOINT after_order;
ROLLBACK TO SAVEPOINT after_order;4. The Isolation Level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;5. The Row Lock
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;6. The Transfer Function
SELECT transfer(1, 2, 100);7. The Check Constraint
balance NUMERIC CHECK (balance >= 0)8. The Serialization Retry
-- The catch the 40001 and retry9. The Consistent Lock Order
-- Lock the lower id first10. The ACID Guarantee
BEGIN;
-- the atomicity, the consistency, the isolation, the durability
COMMIT;Visual
The Transaction Lifecycle
┌──────────────────────────────────────────────────────────────┐
│ THE TRANSACTION LIFECYCLE │
│ │
│ BEGIN │
│ │ │
│ │ The transaction starts. │
│ ▼ │
│ The statements │
│ │ │
│ ├── UPDATE accounts SET balance = balance - 100 │
│ ├── UPDATE accounts SET balance = balance + 100 │
│ │ │
│ ├── The error occurs → ROLLBACK │
│ │ The changes are undone. │
│ │ │
│ └── The success → COMMIT │
│ The changes are written. │
│ │ │
│ ▼ │
│ The database is in the new state. │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The uncommitted changes are invisible to the other │ │
│ │ sessions. The COMMIT makes them visible. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The ACID Properties
┌──────────────────────────────────────────────────────────────┐
│ THE ACID PROPERTIES │
│ │
│ ATOMICITY: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The all-or-nothing. │ │
│ │ The COMMIT writes all the changes. │ │
│ │ The ROLLBACK undoes all the changes. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ CONSISTENCY: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The database moves from the valid state to the │ │
│ │ valid state. │ │
│ │ The constraints, the foreign keys, the triggers. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ ISOLATION: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The concurrent transactions do not see each other's │ │
│ │ uncommitted changes. │ │
│ │ The isolation level determines the strictness. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ DURABILITY: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The committed changes survive the crash. │ │
│ │ The write-ahead log records the changes. │ │
│ │ The recovery replays the log. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Isolation Levels
┌──────────────────────────────────────────────────────────────┐
│ THE ISOLATION LEVELS │
│ │
│ READ UNCOMMITTED: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The dirty read is possible. │ │
│ │ The weakest level. │ │
│ │ The PostgreSQL treats it as the READ COMMITTED. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ READ COMMITTED (the PostgreSQL default): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The dirty read is prevented. │ │
│ │ The non-repeatable read and the phantom read are │ │
│ │ possible. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ REPEATABLE READ: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The dirty read and the non-repeatable read are │ │
│ │ prevented. │ │
│ │ The PostgreSQL also prevents the phantom read. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ SERIALIZABLE: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ All the anomalies are prevented. │ │
│ │ The serialization failures cause the aborts. │ │
│ │ The application must retry. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Lost Update
┌──────────────────────────────────────────────────────────────┐
│ THE LOST UPDATE │
│ │
│ WITHOUT the lock: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The session 1: │ │
│ │ SELECT balance FROM accounts WHERE id = 1; -- 1000│ │
│ │ │ │
│ │ The session 2: │ │
│ │ SELECT balance FROM accounts WHERE id = 1; -- 1000│ │
│ │ │ │
│ │ The session 1: │ │
│ │ UPDATE accounts SET balance = 1100 WHERE id = 1; │ │
│ │ COMMIT; │ │
│ │ │ │
│ │ The session 2: │ │
│ │ UPDATE accounts SET balance = 1100 WHERE id = 1; │ │
│ │ COMMIT; │ │
│ │ │ │
│ │ The result: 1100, not 1200. │ │
│ │ The session 1's update is LOST. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ WITH the FOR UPDATE lock: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The session 1: │ │
│ │ SELECT ... FOR UPDATE; -- the row is locked │ │
│ │ UPDATE accounts SET balance = 1100 WHERE id = 1; │ │
│ │ COMMIT; │ │
│ │ │ │
│ │ The session 2: │ │
│ │ SELECT ... FOR UPDATE; -- WAITS for the lock │ │
│ │ -- The session 1 commits. │ │
│ │ -- The session 2 reads the NEW value (1100). │ │
│ │ UPDATE accounts SET balance = 1200 WHERE id = 1; │ │
│ │ COMMIT; │ │
│ │ │ │
│ │ The result: 1200. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Transaction | A group of statements as a single unit |
BEGIN | Start the transaction |
COMMIT | Write the changes |
ROLLBACK | Undo the changes |
SAVEPOINT | The partial rollback point |
| Atomicity | The all-or-nothing |
| Consistency | The valid state to the valid state |
| Isolation | The concurrent transactions do not interfere |
| Durability | The committed changes survive the crash |
READ COMMITTED | The PostgreSQL default |
SERIALIZABLE | The strictest; requires the retries |
FOR UPDATE | The explicit row lock |
Key takeaways:
- A transaction is a group of SQL statements that execute as a single unit. The
BEGINstarts it, theCOMMITwrites the changes, and theROLLBACKundoes them. The intermediate states are invisible to the other sessions . - The ACID properties are the guarantees. The atomicity is the all-or-nothing, the consistency is the valid state to the valid state, the isolation is the concurrent transactions do not interfere, and the durability is the committed changes survive the crash .
- The isolation levels control the concurrency anomalies. The
READ UNCOMMITTEDallows the dirty reads, theREAD COMMITTEDprevents them but allows the non-repeatable and the phantom reads, theREPEATABLE READprevents them all (in the PostgreSQL), and theSERIALIZABLEprevents the serialization anomalies . - The
READ COMMITTEDis the PostgreSQL default. It prevents the dirty reads but allows the non-repeatable reads and the phantom reads. TheREPEATABLE READand theSERIALIZABLEare the stricter levels for the cases where the consistency matters more than the concurrency . - The
SERIALIZABLElevel can abort the transactions. The aborts are the serialization failures (the SQLSTATE40001), and the application must catch them and retry the transaction. The retry is the requirement of theSERIALIZABLElevel . - The
FOR UPDATEtakes the explicit row lock. It prevents the lost update by locking the row until the commit. The concurrent session that tries to lock the same row waits, and it reads the updated value after the first session commits . - The consistent lock order prevents the deadlocks. The two transactions that lock the rows in the opposite order can deadlock. The fix is to lock the rows in the consistent order, such as the ascending id .
- The savepoint allows the partial rollback. The
SAVEPOINTmarks a point within the transaction, and theROLLBACK TO SAVEPOINTundoes the changes since the point without undoing the whole transaction .
Remember: A transaction is the all-or-nothing unit, and the ACID properties are its guarantees. The BEGIN starts it, the COMMIT writes the changes, and the ROLLBACK undoes them. The isolation levels control the concurrency anomalies, and the SERIALIZABLE level requires the retries. The FOR UPDATE takes the row lock, and the consistent lock order prevents the deadlocks. The result is the reliable multi-statement operations, which is the foundation of the data integrity.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!