Transactions & Savepoints in SQL
IntermediateA transaction groups SQL statements so they all succeed or all roll back together; savepoints add nested rollback points within a transaction without aborting the whole unit of work.
Overview
Transactions implement ACID guarantees. BEGIN starts a transaction; COMMIT makes changes permanent; ROLLBACK undoes all changes since BEGIN. Most databases auto-commit each statement by default — you must explicitly BEGIN to group statements. SAVEPOINT name creates a sub-checkpoint: ROLLBACK TO SAVEPOINT name undoes changes back to that point while keeping the outer transaction alive. RELEASE SAVEPOINT drops the checkpoint. Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control how concurrent transactions see each other's changes. Deadlocks occur when two transactions each hold a lock the other needs; databases detect and abort one of them automatically.
Bank Transfer with SAVEPOINT
The classic transfer example: debit one account and credit another in a single transaction. Adding a savepoint allows partial rollback if the credit step fails while preserving the logged audit record.
-- Bank transfer: debit user 1, credit user 2
BEGIN;
-- Save a checkpoint after validation
SAVEPOINT before_transfer;
-- Debit sender (using orders table as a balance proxy here)
UPDATE users SET balance = balance - 500 WHERE id = 1;
-- Check sufficient funds
-- (In a real system you'd SELECT and check in application code)
SAVEPOINT after_debit;
-- Credit receiver
UPDATE users SET balance = balance + 500 WHERE id = 2;
-- If credit UPDATE failed (e.g. user 2 doesn't exist), roll back only the credit:
-- ROLLBACK TO SAVEPOINT after_debit;
-- Then decide whether to abort the whole transaction:
-- ROLLBACK;
-- Insert audit log regardless
INSERT INTO orders (user_id, amount, status, created_at)
VALUES (1, 500, 'transfer_debit', NOW());
COMMIT;
-- Either both balance changes are committed or neither isDeadlock Scenario Between Two Sessions
A deadlock occurs when Session A locks row 1 and waits for row 2, while Session B locks row 2 and waits for row 1. The database detects the cycle and kills one transaction with an error.
-- Session A | Session B
BEGIN; | BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1; -- A locks row 1
| UPDATE users SET balance = balance - 200 WHERE id = 2; -- B locks row 2
UPDATE users SET balance = balance + 100 WHERE id = 2; -- A waits for row 2 (B has it)
| UPDATE users SET balance = balance + 200 WHERE id = 1; -- B waits for row 1 (A has it)
-- DEADLOCK DETECTED
-- Database aborts one session (usually the shorter transaction):
-- ERROR: deadlock detected
-- DETAIL: Process 1234 waits for ShareLock on transaction 5678;
-- Process 5678 waits for ShareLock on transaction 1234.
-- Prevention: always acquire locks in the same order across all transactions
-- Both sessions should update id=1 first, then id=2Transaction Isolation Levels
Higher isolation levels prevent more read anomalies (dirty reads, non-repeatable reads, phantom reads) at the cost of more lock contention and reduced concurrency.
-- PostgreSQL: set isolation level for the current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Now this transaction sees a fully serializable snapshot:
-- no dirty reads, no non-repeatable reads, no phantom reads
-- Check an account balance and conditionally transfer
SELECT balance FROM users WHERE id = 1; -- sees a snapshot consistent across the txn
UPDATE users SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
COMMIT;
-- READ COMMITTED (default in PostgreSQL, MySQL):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Each statement within the transaction sees the latest committed data
-- (non-repeatable reads possible: same SELECT can return different rows)
-- Isolation level cheat sheet:
-- READ UNCOMMITTED → dirty reads allowed (rarely used)
-- READ COMMITTED → no dirty reads; non-repeatable reads possible
-- REPEATABLE READ → snapshot fixed at transaction start; phantoms possible in MySQL but not PG
-- SERIALIZABLE → full snapshot isolation; most restrictive, most consistentKey Points to Remember
- 1BEGIN / COMMIT / ROLLBACK form the boundaries of a transaction; without BEGIN, most databases auto-commit each statement.
- 2SAVEPOINT creates a named sub-checkpoint; ROLLBACK TO SAVEPOINT undoes to that point without aborting the outer transaction.
- 3RELEASE SAVEPOINT drops the checkpoint; it does not commit or roll back — it just removes the save point.
- 4Deadlocks are detected automatically by the database; the victim transaction receives an error and must retry.
- 5Prevent deadlocks by always acquiring locks in a consistent order and keeping transactions short.
- 6SERIALIZABLE isolation prevents all read anomalies but increases lock contention — use only when required for correctness.
Interview Questions
Sign in to ask AriaWhat is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?
Describe a deadlock scenario and how you would prevent it.
What are the four transaction isolation levels and the anomalies each prevents?
In a bank transfer, why is a transaction necessary rather than two separate UPDATE statements?
Ask Aria about Transactions & Savepoints in SQL
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.