SQL Transactions and ACID — Atomicity, Consistency, Isolation, Durability

A database transaction is a unit of work that either completes entirely (COMMIT) or is undone entirely (ROLLBACK). ACID properties guarantee that transactions behave reliably.

Format your SQL with the SQL Formatter.

ACID properties explained

Atomicity — All operations succeed or all are undone:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Credit
COMMIT;
-- If the second UPDATE fails, the first is automatically rolled back

Consistency — Transaction leaves the database in a valid state:

-- Constraint prevents negative balance:
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- If the debit would cause a negative balance, the transaction fails
-- and neither update is applied

Isolation — Concurrent transactions don’t interfere:

-- Session A sees a consistent snapshot; Session B's uncommitted changes
-- don't bleed into Session A's transaction (default: READ COMMITTED)

Durability — Committed data survives crashes:

-- After COMMIT, the data is written to disk (write-ahead log)
-- A crash after COMMIT won't lose the data

Basic transaction syntax

-- PostgreSQL / MySQL / SQLite:
BEGIN;
-- ... SQL statements ...
COMMIT;   -- Save all changes

BEGIN;
-- ... SQL statements ...
ROLLBACK; -- Undo all changes

-- Savepoints (nested rollback):
BEGIN;
INSERT INTO orders (user_id, total) VALUES (123, 99.99);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 456);
-- If this fails:
ROLLBACK TO before_items;  -- Only undoes the order_items insert
COMMIT;                     -- Orders insert still committed

Transaction in Node.js (pg)

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');

    // Check sufficient balance:
    const { rows } = await client.query(
      'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
      [fromId]
    );
    
    if (rows[0].balance < amount) {
      throw new Error('Insufficient funds');
    }

    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    
    await client.query('COMMIT');
    console.log('Transfer complete');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Isolation levels

-- READ UNCOMMITTED: reads dirty data (not supported by PostgreSQL, use READ COMMITTED)
-- READ COMMITTED: default for PostgreSQL and MySQL — no dirty reads
-- REPEATABLE READ: same query always returns same rows within transaction
-- SERIALIZABLE: strictest — transactions appear to execute serially

-- Set isolation level:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- or:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Row-level locking

-- FOR UPDATE: lock selected rows for update (prevents concurrent modifications):
BEGIN;
SELECT * FROM inventory WHERE product_id = 123 FOR UPDATE;
-- Other transactions trying to update product_id=123 will wait
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
COMMIT;

-- FOR SHARE: allow reads but prevent updates:
SELECT * FROM products WHERE id = 456 FOR SHARE;

-- SKIP LOCKED: skip rows already locked (for queue processing):
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Deadlock prevention

-- Deadlock occurs when:
-- Transaction A locks row 1, wants row 2
-- Transaction B locks row 2, wants row 1
-- → Both wait forever

-- Prevent by always acquiring locks in the same order:
-- ✅ Both transactions: lock account 1 first, then account 2
BEGIN;
SELECT * FROM accounts WHERE id = LEAST($1, $2) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST($1, $2) FOR UPDATE;
COMMIT;

-- PostgreSQL detects deadlocks automatically and aborts one transaction:
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890
-- → retry the failed transaction

Related tools

• SQL Formatter — format SQL with transactions
• SQL Indexes Guide — improve transaction performance
• SQL CTEs — organize complex transaction queries