Chapter 6: Transactions and Concurrency
September 28, 2025 ยท View on GitHub
Understanding Transactions
A transaction is a sequence of operations performed as a single logical unit of work. Either all operations succeed, or none do.
ACID Properties
Atomicity: All or nothing execution Consistency: Database remains in valid state Isolation: Concurrent transactions don't interfere Durability: Committed changes persist
Basic Transaction Commands
-- Start transaction
START TRANSACTION;
-- or
BEGIN;
-- Execute operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Commit if successful
COMMIT;
-- Or rollback if error
ROLLBACK;
Autocommit Mode
-- Check autocommit status
SELECT @@autocommit;
-- Disable autocommit
SET autocommit = 0;
-- Enable autocommit
SET autocommit = 1;
Savepoints
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 1;
SAVEPOINT after_inventory;
UPDATE orders SET status = 'processing' WHERE id = 100;
-- Error occurs here
ROLLBACK TO SAVEPOINT after_inventory;
-- Inventory update kept, order update rolled back
COMMIT;
Isolation Levels
READ UNCOMMITTED
Lowest isolation, allows dirty reads:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
READ COMMITTED
Default for many databases, prevents dirty reads:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
REPEATABLE READ
MySQL's default, prevents dirty and non-repeatable reads:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SERIALIZABLE
Highest isolation, prevents all phenomena:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Locking
Table Locks
-- Read lock (shared)
LOCK TABLES products READ;
-- Other sessions can read, but not write
-- Write lock (exclusive)
LOCK TABLES products WRITE;
-- Other sessions cannot read or write
UNLOCK TABLES;
Row Locks (InnoDB)
-- SELECT ... FOR UPDATE (exclusive lock)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row locked until transaction ends
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- SELECT ... FOR SHARE (shared lock)
SELECT * FROM products WHERE id = 1 FOR SHARE;
Deadlocks
-- Session 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Waits for Session 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Session 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Waits for Session 1
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- DEADLOCK DETECTED!
Avoiding Deadlocks
- Access tables in same order
- Keep transactions short
- Use lower isolation levels when possible
- Index foreign keys
Optimistic vs Pessimistic Locking
Pessimistic Locking
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Lock held
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
Optimistic Locking
-- Add version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
-- Application checks version
SELECT stock, version FROM products WHERE id = 1;
-- version = 5, stock = 10
-- Update only if version unchanged
UPDATE products
SET stock = 9, version = version + 1
WHERE id = 1 AND version = 5;
-- Check affected rows to detect conflict
Best Practices
- Keep transactions short to reduce lock time
- Use appropriate isolation level for your needs
- Handle deadlocks in application code
- Index properly to minimize lock escalation
- Batch operations when possible
- Monitor long-running transactions