Transaction Management

Transaction Management

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

Overview

Implement robust transaction management with ACID compliance, concurrency control, and error handling. Covers isolation levels, locking strategies, and deadlock resolution.

When to Use

• ACID transaction implementation
• Concurrent data modification handling
• Isolation level selection
• Deadlock prevention and resolution
• Transaction timeout configuration
• Distributed transaction coordination
• Financial transaction safety

Quick Start

Simple Transaction:

-- Start transaction
BEGIN;

-- Multiple statements
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit changes
COMMIT;

-- Or rollback
ROLLBACK;

Reference Guides

Detailed implementations in the references/ directory:

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values

Deadlock Prevention

PostgreSQL - Deadlock Detection:

-- PostgreSQL automatically detects deadlocks
-- Kills one transaction and raises error

-- Example deadlock scenario
-- Transaction 1: Lock A, then try Lock B
-- Transaction 2: Lock B, then try Lock A
-- Result: One transaction rolled back with deadlock error

-- Retry logic
DO $$
DECLARE
  retry_count INT := 0;
BEGIN
  LOOP
    BEGIN
      BEGIN;
      UPDATE accounts SET balance = balance - 100 WHERE id = 1;
      UPDATE accounts SET balance = balance + 100 WHERE id = 2;
      COMMIT;
      EXIT;
    EXCEPTION WHEN deadlocked_table THEN
      ROLLBACK;
      retry_count := retry_count + 1;
      IF retry_count > 3 THEN
        RAISE;
      END IF;
      -- Wait before retry
      PERFORM pg_sleep(0.1);
    END;
  END LOOP;
END $$;

MySQL - Deadlock Prevention:

-- Prevent deadlock by consistent lock ordering
-- Always lock in same order: table1 id=1, then table2 id=2

START TRANSACTION;
-- Always lock account 1 first, then account 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- Safe order prevents deadlock
COMMIT;

Deadlock Recovery Handling:

// Application-level deadlock retry (Node.js)
async function transferMoney(fromId, toId, amount, retries = 3) {
  for (let i = 0; i < retries; i++) {
    try {
      await db.query("BEGIN");
      await db.query(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 FOR UPDATE",
        [amount, fromId],
      );
      await db.query(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2 FOR UPDATE",
        [amount, toId],
      );
      await db.query("COMMIT");
      return { success: true };
    } catch (error) {
      if (error.code === "40P01") {
        // Deadlock detected
        await db.query("ROLLBACK");
        if (i === retries - 1) throw error;
        // Exponential backoff
        await new Promise((r) => setTimeout(r, 100 * Math.pow(2, i)));
      } else {
        throw error;
      }
    }
  }
}

MySQL Isolation Levels

MySQL Isolation Levels

MySQL Isolation Level Configuration:

-- Check current isolation level
SHOW VARIABLES LIKE 'transaction_isolation';

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set for all new connections
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set for specific transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- Statements
COMMIT;

Isolation Level Comparison:

-- READ UNCOMMITTED (dirty reads possible)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED (repeatable reads, phantom reads possible)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ (phantom reads possible, MySQL default)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE (no anomalies)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MySQL Locking

MySQL Locking

Row-Level Locking:

-- Implicit locking on UPDATE/DELETE
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Row is locked until transaction ends
COMMIT;

-- SELECT FOR UPDATE: explicit lock
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Exclusive lock acquired
UPDATE accounts SET balance = 100 WHERE id = 1;
COMMIT;

-- SELECT FOR SHARE: read lock
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Shared lock (blocks FOR UPDATE)
COMMIT;

Gap Locking (InnoDB):

-- InnoDB locks gaps between rows
START TRANSACTION;
-- Locks rows and gaps where id between 1 and 100
SELECT * FROM products WHERE id BETWEEN 1 AND 100 FOR UPDATE;
-- Prevents phantom rows in range
COMMIT;

MySQL Transactions

MySQL Transactions

MySQL Transaction:

-- Start transaction
START TRANSACTION;

-- Or
BEGIN;

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

-- Commit
COMMIT;

-- Or rollback
ROLLBACK;

MySQL Savepoints:

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (123, 99.99);
SAVEPOINT after_insert;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456;

-- If inventory check fails
IF (SELECT quantity FROM inventory WHERE product_id = 456) < 0 THEN
  ROLLBACK TO after_insert;
END IF;

COMMIT;

PostgreSQL Explicit Locking

PostgreSQL Explicit Locking

Row-Level Locks:

-- FOR UPDATE: exclusive lock for update
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions cannot UPDATE/DELETE/SELECT FOR UPDATE this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE: shared lock
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Other transactions can SELECT FOR SHARE but not FOR UPDATE
COMMIT;

-- FOR UPDATE NOWAIT: error if locked instead of waiting
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
EXCEPTION WHEN OTHERS THEN
  -- Row is locked
END;
COMMIT;

Table-Level Locks:

-- Exclusive table lock
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- No other transactions can access table

-- Share lock
LOCK TABLE accounts IN SHARE MODE;
-- Other transactions can read but not write

-- Exclusive for user access
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

PostgreSQL Isolation Levels

PostgreSQL Isolation Levels

Read Uncommitted (not fully implemented):

-- PostgreSQL treats as READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN;
-- Can read uncommitted changes from other transactions
SELECT COUNT(*) FROM orders WHERE user_id = 123;
COMMIT;

Read Committed (Default):

-- Default PostgreSQL isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
-- Read committed data only
-- Allows phantom reads and non-repeatable reads
SELECT * FROM accounts WHERE id = 1;

-- May see different data if other transactions modify rows
SELECT * FROM accounts WHERE id = 1;
COMMIT;

Repeatable Read:

-- Higher isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
-- Snapshot of data at transaction start
SELECT COUNT(*) as count_1 FROM orders;

-- Other transaction inserts order
-- Will still see same count
SELECT COUNT(*) as count_2 FROM orders;
COMMIT;

Serializable:

-- Highest isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
-- Transactions execute as if serially
-- Prevents all anomalies (serialization failures may occur)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- May fail with serialization_failure error

PostgreSQL Transactions

PostgreSQL Transactions

Simple Transaction:

-- Start transaction
BEGIN;

-- Multiple statements
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit changes
COMMIT;

-- Or rollback
ROLLBACK;

Transaction with Error Handling:

BEGIN;

-- Savepoint for partial rollback
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;

-- If error detected
IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
  ROLLBACK TO sp1;
  -- Handle negative balance
END IF;

COMMIT;