majiayu000 Transaction Management
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
Transaction Basics
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;
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;
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
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;
Locking Strategies
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;
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;
Concurrency Control
Optimistic Concurrency
PostgreSQL with Version Numbers:
-- Add version column
ALTER TABLE accounts ADD COLUMN version INT DEFAULT 1;
-- Update with version check
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;
-- Application checks affected rows
-- If 0 rows updated, version mismatch (try again)
PostgreSQL with Timestamps:
-- Add last modified timestamp
ALTER TABLE accounts ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();
-- Update with timestamp validation
UPDATE accounts
SET balance = balance - 100, updated_at = NOW()
WHERE id = 1 AND updated_at = '2024-01-15 10:00:00';
-- If no rows updated, data was modified by another transaction
Pessimistic Concurrency
PostgreSQL - Lock and Modify:
BEGIN;
-- Lock row before modification
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Safe to modify (no other transactions can update)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
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;
}
}
}
}
Distributed Transactions
Two-Phase Commit Pattern:
-- Prepare phase: acquire locks, validate
BEGIN;
SAVEPOINT prepare_phase;
-- Prepare writes on both databases
INSERT INTO account_shadow SELECT * FROM accounts WHERE id = 1;
-- Check if both databases are ready
-- If any fails, ROLLBACK TO prepare_phase
-- Commit phase: finalize
RELEASE SAVEPOINT prepare_phase;
COMMIT;
Eventual Consistency Pattern:
// Asynchronous transaction across services
async function transferAcrossServices(fromId, toId, amount) {
// 1. Debit from first service (transactional)
await service1.debit(fromId, amount);
// 2. Queue credit for second service (reliable queue)
await queue.publish({
type: 'credit',
toId,
amount,
requestId: uuid()
});
// 3. Service 2 processes asynchronously
queue.subscribe('credit', async (msg) => {
try {
await service2.credit(msg.toId, msg.amount);
await queue.ack(msg.requestId);
} catch (error) {
// Retry mechanism
await queue.retry(msg.requestId);
}
});
}
Transaction Monitoring
PostgreSQL - Active Transactions:
-- View active transactions
SELECT
pid,
usename,
application_name,
state,
query,
query_start,
xact_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;
-- View transaction locks
SELECT
l.locktype,
l.relation::regclass,
l.mode,
l.granted,
a.usename,
a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;
MySQL - Active Transactions:
-- View active transactions
SELECT *
FROM INFORMATION_SCHEMA.INNODB_TRX
ORDER BY trx_started DESC;
-- View locks
SELECT *
FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- Kill long-running transaction
KILL QUERY process_id;
KILL CONNECTION process_id;
Best Practices
✅ DO use appropriate isolation levels for use case ✅ DO keep transactions short ✅ DO commit frequently ✅ DO handle transaction errors ✅ DO use consistent lock ordering ✅ DO monitor transaction performance ✅ DO document transaction requirements
❌ DON'T hold transactions during user input ❌ DON'T use SERIALIZABLE for high-concurrency systems ❌ DON'T ignore deadlock errors ❌ DON'T lock too many rows ❌ DON'T use READ UNCOMMITTED for critical data