Transactions & ACID

Understanding database transactions, ACID properties, isolation levels, locking mechanisms, and concurrency control

Transactions are fundamental to database reliability. They ensure that database operations are processed atomically, consistently, and reliably. This guide covers transaction concepts, ACID properties, and concurrency control mechanisms across PostgreSQL, MySQL, and SQL Server.

What is a Transaction?

A transaction is a sequence of database operations that are treated as a single unit of work. Either all operations succeed (commit), or none of them are applied (rollback).

-- Classic example: Bank transfer
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Credit

COMMIT;  -- Both changes applied atomically

If any statement fails, you can rollback:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Error occurs here
UPDATE accounts SET balance = balance + 100 WHERE id = 999;  -- Invalid account

ROLLBACK;  -- Neither change is applied

ACID Properties

Atomicity

All operations in a transaction succeed or none do. There's no partial completion.

-- Either both updates happen, or neither
BEGIN TRANSACTION;
    INSERT INTO orders (customer_id, total) VALUES (1, 500);
    INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 1, 2);
COMMIT;

Consistency

Transactions bring the database from one valid state to another. Constraints are always enforced.

-- Consistency prevents invalid states
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2) CHECK (balance >= 0)  -- Constraint
);

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- Would make balance negative
COMMIT;
-- Transaction fails if constraint would be violated

Isolation

Concurrent transactions don't interfere with each other. Each transaction sees a consistent snapshot.

-- Transaction A
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- Sees 1000
-- Meanwhile, Transaction B updates balance to 500
SELECT balance FROM accounts WHERE id = 1;  -- Still sees 1000 (depending on isolation level)
COMMIT;

Durability

Once committed, changes survive system failures (power outage, crash).

BEGIN TRANSACTION;
    INSERT INTO important_data (value) VALUES ('critical');
COMMIT;
-- Data is now safely written to disk/WAL
-- Survives server restart

Transaction Syntax by Database

PostgreSQL

-- Start transaction
BEGIN;
-- or
START TRANSACTION;

-- Commit
COMMIT;
-- or
END;

-- Rollback
ROLLBACK;
-- or
ABORT;

-- With savepoints
BEGIN;
    INSERT INTO orders (id) VALUES (1);
    SAVEPOINT order_created;
    INSERT INTO items (order_id) VALUES (1);
    -- Error!
    ROLLBACK TO SAVEPOINT order_created;
    -- Retry or handle error
COMMIT;

-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MySQL

-- Start transaction
START TRANSACTION;
-- or
BEGIN;

-- Commit
COMMIT;

-- Rollback
ROLLBACK;

-- Disable autocommit
SET autocommit = 0;
-- Multiple statements
COMMIT;
SET autocommit = 1;

-- With savepoints
START TRANSACTION;
    INSERT INTO orders (id) VALUES (1);
    SAVEPOINT order_created;
    INSERT INTO items (order_id) VALUES (1);
    ROLLBACK TO SAVEPOINT order_created;
COMMIT;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

SQL Server

-- Start transaction
BEGIN TRANSACTION;
-- or with name
BEGIN TRANSACTION TransferFunds;

-- Commit
COMMIT TRANSACTION;
-- or
COMMIT;

-- Rollback
ROLLBACK TRANSACTION;
-- or
ROLLBACK;

-- With savepoints
BEGIN TRANSACTION;
    INSERT INTO orders (id) VALUES (1);
    SAVE TRANSACTION order_created;
    INSERT INTO items (order_id) VALUES (1);
    ROLLBACK TRANSACTION order_created;
COMMIT;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

Isolation Levels

Isolation levels control how transactions interact with concurrent transactions.

Read Uncommitted

The lowest isolation level. Allows reading uncommitted changes from other transactions (dirty reads).

-- Transaction A
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Sees 1000 (uncommitted by Transaction B)

-- Transaction B (uncommitted)
BEGIN;
UPDATE accounts SET balance = 1000 WHERE id = 1;  -- Not committed yet
-- If Transaction B rolls back, Transaction A read invalid data!

Phenomena allowed:

❌ Dirty reads
❌ Non-repeatable reads
❌ Phantom reads

Rarely used in production. Only use when reading slightly stale data is acceptable and performance is critical.

Read Committed

Default in PostgreSQL and SQL Server. Only sees committed data, but data can change between reads.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- Transaction B commits: UPDATE accounts SET balance = 500 WHERE id = 1;

SELECT balance FROM accounts WHERE id = 1;  -- 500 (different value!)
COMMIT;

Phenomena allowed:

✅ Dirty reads prevented
❌ Non-repeatable reads
❌ Phantom reads

Repeatable Read

Default in MySQL InnoDB. Guarantees same data if you read the same row twice.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- Transaction B commits: UPDATE accounts SET balance = 500 WHERE id = 1;

SELECT balance FROM accounts WHERE id = 1;  -- Still 1000! (repeatable)
COMMIT;

Phenomena allowed:

✅ Dirty reads prevented
✅ Non-repeatable reads prevented
❌ Phantom reads (in standard SQL, but MySQL InnoDB prevents these too)

Serializable

Highest isolation. Transactions execute as if they were serial (one after another).

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Transaction A
BEGIN;
SELECT SUM(balance) FROM accounts;  -- 10000

-- Transaction B tries to: INSERT INTO accounts (id, balance) VALUES (999, 5000);
-- Transaction B is BLOCKED until Transaction A commits

SELECT SUM(balance) FROM accounts;  -- Still 10000 (no phantom rows)
COMMIT;

-- Now Transaction B can proceed

Phenomena allowed:

✅ Dirty reads prevented
✅ Non-repeatable reads prevented
✅ Phantom reads prevented

Snapshot Isolation (SQL Server / PostgreSQL)

Provides a consistent snapshot of the database at transaction start.

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Sees data as of transaction start
COMMIT;

-- PostgreSQL uses MVCC, similar to snapshot behavior
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- PostgreSQL's Repeatable Read provides snapshot isolation

Isolation Level Comparison

Level	Dirty Reads	Non-repeatable Reads	Phantoms	Performance
Read Uncommitted	❌ Yes	❌ Yes	❌ Yes	Fastest
Read Committed	✅ No	❌ Yes	❌ Yes	Fast
Repeatable Read	✅ No	✅ No	❌ Yes*	Medium
Serializable	✅ No	✅ No	✅ No	Slowest

*MySQL InnoDB prevents phantom reads at Repeatable Read level

Concurrency Phenomena

Dirty Read

Reading uncommitted data from another transaction.

-- Time 1: Transaction B starts
BEGIN;
UPDATE products SET stock = 0 WHERE id = 1;

-- Time 2: Transaction A reads (READ UNCOMMITTED)
SELECT stock FROM products WHERE id = 1;  -- 0 (dirty read!)

-- Time 3: Transaction B rolls back
ROLLBACK;

-- Transaction A's data is now invalid!

Non-Repeatable Read

Same query returns different results within a transaction.

-- Transaction A
BEGIN;
SELECT price FROM products WHERE id = 1;  -- 100

-- Transaction B commits a price change
UPDATE products SET price = 150 WHERE id = 1;
COMMIT;

-- Transaction A continues
SELECT price FROM products WHERE id = 1;  -- 150 (different!)
COMMIT;

Phantom Read

New rows appear in a repeated query.

-- Transaction A
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 10

-- Transaction B inserts a new pending order and commits

-- Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 11 (phantom!)
COMMIT;

Locking Mechanisms

Row-Level Locks

-- PostgreSQL
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;      -- Exclusive lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;       -- Shared lock
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;  -- Weaker exclusive
SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;   -- Weaker shared

-- MySQL
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SQL Server
SELECT * FROM accounts WITH (ROWLOCK, UPDLOCK) WHERE id = 1;
SELECT * FROM accounts WITH (ROWLOCK, HOLDLOCK) WHERE id = 1;

Lock Modes

Mode	Compatible With	Used For
Shared (S)	Shared	SELECT
Exclusive (X)	Nothing	UPDATE, DELETE
Update (U)	Shared	UPDATE (initial phase)
Intent	Intent	Table-level coordination

Deadlocks

Occur when two transactions wait for each other's locks.

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- Waits for row 2...

-- Transaction B
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- Waits for row 1

-- DEADLOCK! Database detects and aborts one transaction

Preventing deadlocks:

-- 1. Always lock resources in the same order
-- Good: Always lock lower ID first
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 2. Use lock timeouts
-- PostgreSQL
SET lock_timeout = '5s';

-- MySQL
SET innodb_lock_wait_timeout = 5;

-- SQL Server
SET LOCK_TIMEOUT 5000;

-- 3. Keep transactions short

Optimistic vs Pessimistic Locking

Pessimistic Locking

Lock data before reading/modifying. Prevents conflicts but reduces concurrency.

-- Lock row before update
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Row is now locked, other transactions must wait
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

Optimistic Locking

Check for conflicts at commit time using version numbers.

-- Read with version
SELECT id, name, stock, version FROM products WHERE id = 1;
-- Returns: id=1, stock=100, version=5

-- Update with version check
UPDATE products 
SET stock = 99, version = version + 1 
WHERE id = 1 AND version = 5;

-- If no rows updated, someone else changed it!
-- rows affected = 0 → retry
-- rows affected = 1 → success

Application code pattern:

-- Retry loop
DO $$
DECLARE
    current_version INT;
    rows_affected INT;
BEGIN
    LOOP
        -- Read current version
        SELECT version INTO current_version FROM products WHERE id = 1;
        
        -- Attempt update
        UPDATE products 
        SET stock = stock - 1, version = version + 1 
        WHERE id = 1 AND version = current_version;
        
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        
        EXIT WHEN rows_affected > 0;  -- Success
        -- Retry on conflict
    END LOOP;
END $$;

Advisory Locks

Application-defined locks not tied to specific rows.

PostgreSQL

-- Session-level lock (released at session end)
SELECT pg_advisory_lock(12345);  -- Acquire
SELECT pg_advisory_unlock(12345);  -- Release

-- Transaction-level lock (released at transaction end)
SELECT pg_advisory_xact_lock(12345);

-- Try lock (non-blocking)
SELECT pg_try_advisory_lock(12345);  -- Returns true/false

-- Named locks (use hash of name)
SELECT pg_advisory_lock(hashtext('process_orders'));

MySQL

-- Named locks
SELECT GET_LOCK('my_lock', 10);  -- Acquire with 10 second timeout
SELECT RELEASE_LOCK('my_lock');  -- Release

-- Check if lock is free
SELECT IS_FREE_LOCK('my_lock');  -- Returns 1 if free

-- Check if current session holds lock
SELECT IS_USED_LOCK('my_lock');  -- Returns connection_id or NULL

SQL Server

-- Application locks
EXEC sp_getapplock @Resource = 'MyResource', @LockMode = 'Exclusive';
EXEC sp_releaseapplock @Resource = 'MyResource';

-- With timeout
EXEC sp_getapplock 
    @Resource = 'MyResource', 
    @LockMode = 'Exclusive',
    @LockTimeout = 5000;

Transaction Best Practices

Keep Transactions Short

-- ❌ Bad: Long transaction
BEGIN;
SELECT * FROM large_table;  -- Slow query
-- ... processing for minutes ...
UPDATE accounts SET processed = true WHERE id = 1;
COMMIT;

-- ✅ Good: Short transaction
-- Do slow operations outside transaction
SELECT * FROM large_table;
-- ... processing ...

-- Quick transaction
BEGIN;
UPDATE accounts SET processed = true WHERE id = 1;
COMMIT;

Avoid User Interaction in Transactions

-- ❌ Bad: Waiting for user input in transaction
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Application waits for user to confirm...
-- Row is locked for minutes!
COMMIT;

-- ✅ Good: Quick in-and-out
-- Show data to user (no lock)
SELECT * FROM products WHERE id = 1;

-- User confirms, then quick transaction
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;
COMMIT;

Handle Errors Properly

-- PostgreSQL
BEGIN;
    INSERT INTO orders (id) VALUES (1);
    -- Error handling with savepoint
    SAVEPOINT before_items;
    BEGIN
        INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT before_items;
        -- Log error, continue with partial order
    END;
COMMIT;

-- SQL Server with TRY...CATCH
BEGIN TRY
    BEGIN TRANSACTION;
        INSERT INTO orders (id) VALUES (1);
        INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    -- Log error
    INSERT INTO error_log (message) VALUES (ERROR_MESSAGE());
    
    -- Re-throw
    THROW;
END CATCH;

Use Appropriate Isolation Level

-- Read-only reports: READ COMMITTED or REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT SUM(total) FROM orders WHERE date = CURRENT_DATE;
COMMIT;

-- Critical financial operations: SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Monitoring Transactions

PostgreSQL

-- View active transactions
SELECT 
    pid,
    usename,
    state,
    query,
    xact_start,
    now() - xact_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;

-- View locks
SELECT 
    pg_locks.pid,
    pg_class.relname,
    pg_locks.mode,
    pg_locks.granted
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_locks.mode != 'AccessShareLock';

MySQL

-- View active transactions
SELECT * FROM information_schema.innodb_trx;

-- View locks
SELECT * FROM performance_schema.data_locks;

-- View lock waits
SELECT * FROM performance_schema.data_lock_waits;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

SQL Server

-- View active transactions
SELECT * FROM sys.dm_tran_active_transactions;

-- View locks
SELECT 
    request_session_id,
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks;

-- Blocking queries
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked_sql.text AS blocked_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql;

Transactions & ACID

On this page