SQL Transactions - Complete Guide with Diagrams
Master SQL transactions with visual diagrams, ACID properties, isolation levels, and best practices. Learn transaction control, locking, and deadlock prevention.
SQL Transactions - Complete Guide
Table of Contents
- Introduction to Transactions
- ACID Properties
- Transaction Control Commands
- Transaction Isolation Levels
- Locking Mechanisms
- Deadlocks
- Savepoints
- Distributed Transactions
- Transaction Best Practices
- Common Use Cases
- Interview Questions
Introduction to Transactions
A transaction is a logical unit of work that contains one or more SQL statements. All statements in a transaction either complete successfully (commit) or fail together (rollback), ensuring data consistency.
What is a Transaction?
A transaction is a sequence of database operations that are treated as a single unit. It ensures that either all operations succeed or none of them do, maintaining database integrity.
Transaction Lifecycle
┌──────────────────────────────────────────────────────────┐
│ TRANSACTION LIFECYCLE │
├──────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ │
│ │ BEGIN │ Start transaction │
│ │ TRANSACTION │ │
│ └──────┬──────┘ │
│ │ │
│ ▼ │
│ ┌─────────────┐ │
│ │ Execute │ Run SQL statements │
│ │ Statements │ (INSERT, UPDATE, DELETE) │
│ └──────┬──────┘ │
│ │ │
│ ▼ │
│ ┌────────┐ │
│ │ Error? │ │
│ └───┬─┬──┘ │
│ │ │ │
│ No │ │ Yes │
│ │ │ │
│ ▼ ▼ │
│ ┌────────┐ ┌──────────┐ │
│ │ COMMIT │ │ ROLLBACK │ │
│ └────┬───┘ └────┬─────┘ │
│ │ │ │
│ ▼ ▼ │
│ Transaction Transaction │
│ Complete Cancelled │
│ │
└──────────────────────────────────────────────────────────┘
Transaction States
┌──────────────────────────────────────────────────────────┐
│ TRANSACTION STATES │
├──────────────────────────────────────────────────────────┤
│ │
│ Active ──────▶ Partially Committed ──────▶ Committed │
│ │ │
│ │ │
│ └──────────────────▶ Failed ──────▶ Aborted │
│ │
│ States Explained: │
│ • Active: Transaction is executing │
│ • Partially Committed: Final statement executed │
│ • Committed: Transaction successful, changes permanent │
│ • Failed: Error occurred, cannot proceed │
│ • Aborted: Transaction rolled back, database restored │
│ │
└──────────────────────────────────────────────────────────┘
ACID Properties
ACID is an acronym for the four key properties that guarantee reliable transaction processing.
ACID Overview
┌──────────────────────────────────────────────────────────┐
│ ACID PROPERTIES │
├──────────────────────────────────────────────────────────┤
│ │
│ A - Atomicity │
│ All or nothing - transaction fully completes │
│ or fully fails │
│ │
│ C - Consistency │
│ Database moves from one valid state to another │
│ All constraints and rules are maintained │
│ │
│ I - Isolation │
│ Concurrent transactions don't interfere │
│ Each transaction appears to execute alone │
│ │
│ D - Durability │
│ Committed changes are permanent │
│ Survive system failures │
│ │
└──────────────────────────────────────────────────────────┘
1. Atomicity
┌──────────────────────────────────────────────────────────┐
│ ATOMICITY │
├──────────────────────────────────────────────────────────┤
│ │
│ Bank Transfer Example: │
│ │
│ BEGIN TRANSACTION │
│ ┌─────────────────────────────────┐ │
│ │ Debit $100 from Account A │ ✓ │
│ └─────────────────────────────────┘ │
│ ┌─────────────────────────────────┐ │
│ │ Credit $100 to Account B │ ✗ (Error!) │
│ └─────────────────────────────────┘ │
│ ROLLBACK (Both operations cancelled) │
│ │
│ Result: Either both succeed or both fail │
│ No partial updates allowed │
│ │
└──────────────────────────────────────────────────────────┘
2. Consistency
┌──────────────────────────────────────────────────────────┐
│ CONSISTENCY │
├──────────────────────────────────────────────────────────┤
│ │
│ Before Transaction: │
│ Account A: $1000 │
│ Account B: $500 │
│ Total: $1500 │
│ │
│ Transaction: │
│ Transfer $200 from A to B │
│ │
│ After Transaction: │
│ Account A: $800 │
│ Account B: $700 │
│ Total: $1500 ✓ (Consistency maintained) │
│ │
│ All constraints satisfied: │
│ • Balance >= 0 │
│ • Total money unchanged │
│ • Foreign keys valid │
│ │
└──────────────────────────────────────────────────────────┘
3. Isolation
┌──────────────────────────────────────────────────────────┐
│ ISOLATION │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction 1 Transaction 2 │
│ ───────────── ───────────── │
│ │
│ BEGIN BEGIN │
│ READ balance READ balance │
│ (sees $1000) (sees $1000) │
│ UPDATE -$100 UPDATE -$200 │
│ COMMIT COMMIT │
│ │
│ Without Isolation: │
│ Final balance: $800 (Lost update!) │
│ │
│ With Isolation: │
│ Transaction 2 waits for Transaction 1 │
│ Final balance: $700 ✓ (Correct) │
│ │
└──────────────────────────────────────────────────────────┘
4. Durability
┌──────────────────────────────────────────────────────────┐
│ DURABILITY │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction Flow: │
│ │
│ 1. Execute UPDATE │
│ 2. Write to transaction log │
│ 3. COMMIT │
│ 4. Write to disk (eventually) │
│ │
│ ⚡ System Crash! │
│ │
│ 5. System Restart │
│ 6. Recovery process reads transaction log │
│ 7. Committed changes restored ✓ │
│ │
│ Durability ensures: │
│ • Committed data survives crashes │
│ • Transaction log protects changes │
│ • Recovery mechanisms restore data │
│ │
└──────────────────────────────────────────────────────────┘
Transaction Control Commands
Basic Transaction Commands
-- Start a transaction
BEGIN TRANSACTION; -- SQL Server, PostgreSQL
START TRANSACTION; -- MySQL
BEGIN; -- PostgreSQL, MySQL
-- Commit changes
COMMIT;
-- Rollback changes
ROLLBACK;
-- Set autocommit
SET AUTOCOMMIT = 0; -- MySQL (disable)
SET AUTOCOMMIT = 1; -- MySQL (enable)
Transaction Example
-- Bank transfer example
BEGIN TRANSACTION;
-- Debit from source account
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
-- Check if sufficient funds
IF (SELECT balance FROM accounts WHERE account_id = 1) < 0
BEGIN
ROLLBACK;
PRINT 'Insufficient funds';
RETURN;
END
-- Credit to destination account
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
-- Commit if all successful
COMMIT;
Transaction Control Flow
┌──────────────────────────────────────────────────────────┐
│ TRANSACTION CONTROL FLOW │
├──────────────────────────────────────────────────────────┤
│ │
│ BEGIN TRANSACTION │
│ │ │
│ ▼ │
│ ┌──────────────┐ │
│ │ Statement 1 │ ──────────┐ │
│ └──────────────┘ │ │
│ │ │ │
│ ▼ │ │
│ ┌──────────────┐ │ │
│ │ Statement 2 │ ──────────┤ │
│ └──────────────┘ │ │
│ │ │ │
│ ▼ │ │
│ ┌──────────────┐ │ │
│ │ Statement 3 │ ──────────┤ │
│ └──────────────┘ │ │
│ │ │ │
│ ▼ ▼ │
│ Success? Any Error? │
│ │ │ │
│ Yes Yes │
│ │ │ │
│ ▼ ▼ │
│ COMMIT ROLLBACK │
│ │ │ │
│ ▼ ▼ │
│ Changes Changes │
│ Permanent Discarded │
│ │
└──────────────────────────────────────────────────────────┘
Transaction Isolation Levels
Isolation levels control how transactions interact with each other and what anomalies they prevent.
Isolation Levels Overview
┌──────────────────────────────────────────────────────────┐
│ TRANSACTION ISOLATION LEVELS │
├──────────────────────────────────────────────────────────┤
│ │
│ Level Dirty Non-Repeatable Phantom │
│ Read Read Read │
│ ───────────────── ───── ────────────── ─────── │
│ READ UNCOMMITTED Yes Yes Yes │
│ READ COMMITTED No Yes Yes │
│ REPEATABLE READ No No Yes │
│ SERIALIZABLE No No No │
│ │
│ ↑ Performance │
│ ↓ Data Consistency │
│ │
└──────────────────────────────────────────────────────────┘
1. READ UNCOMMITTED
┌──────────────────────────────────────────────────────────┐
│ READ UNCOMMITTED │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction 1 Transaction 2 │
│ ───────────── ───────────── │
│ │
│ BEGIN │
│ UPDATE balance = 500 │
│ BEGIN │
│ SELECT balance │
│ (reads 500) ← Dirty Read! │
│ ROLLBACK │
│ (balance back to 1000) │
│ Uses 500 (Wrong!) │
│ COMMIT │
│ │
│ Problem: Reads uncommitted data that may be rolled back │
│ Use Case: Reporting where accuracy isn't critical │
│ │
└──────────────────────────────────────────────────────────┘
2. READ COMMITTED
┌──────────────────────────────────────────────────────────┐
│ READ COMMITTED │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction 1 Transaction 2 │
│ ───────────── ───────────── │
│ │
│ BEGIN │
│ SELECT balance BEGIN │
│ (reads 1000) SELECT balance │
│ (reads 1000) │
│ UPDATE balance = 500 │
│ COMMIT │
│ SELECT balance │
│ (reads 500) ← Non-repeatable! │
│ COMMIT │
│ │
│ Prevents: Dirty reads │
│ Allows: Non-repeatable reads, phantom reads │
│ Use Case: Most common default level │
│ │
└──────────────────────────────────────────────────────────┘
3. REPEATABLE READ
┌──────────────────────────────────────────────────────────┐
│ REPEATABLE READ │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction 1 Transaction 2 │
│ ───────────── ───────────── │
│ │
│ BEGIN │
│ SELECT * FROM orders BEGIN │
│ WHERE status='pending' UPDATE orders │
│ (finds 5 rows) SET status='shipped' │
│ COMMIT │
│ SELECT * FROM orders │
│ WHERE status='pending' │
│ (still finds 5 rows) ✓ │
│ │
│ But new INSERT: │
│ INSERT INTO orders │
│ (status='pending') │
│ SELECT COUNT(*) │
│ (finds 6 rows) ← Phantom Read! │
│ │
│ Prevents: Dirty reads, non-repeatable reads │
│ Allows: Phantom reads │
│ │
└──────────────────────────────────────────────────────────┘
4. SERIALIZABLE
┌──────────────────────────────────────────────────────────┐
│ SERIALIZABLE │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction 1 Transaction 2 │
│ ───────────── ───────────── │
│ │
│ BEGIN │
│ SELECT * FROM orders │
│ WHERE status='pending' │
│ BEGIN │
│ INSERT INTO orders │
│ (status='pending') │
│ ⏸ WAITS... │
│ (Transaction 1 holds range lock) │
│ COMMIT │
│ ▶ CONTINUES │
│ COMMIT │
│ │
│ Prevents: All anomalies (dirty, non-repeatable, phantom)│
│ Cost: Lowest concurrency, highest locking │
│ Use Case: Critical financial transactions │
│ │
└──────────────────────────────────────────────────────────┘
Setting Isolation Levels
-- SQL Server
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Check current isolation level
-- SQL Server
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable Read'
WHEN 4 THEN 'Serializable'
END AS isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
-- MySQL
SELECT @@transaction_isolation;
-- PostgreSQL
SHOW transaction_isolation;
Locking Mechanisms
Locks prevent concurrent transactions from interfering with each other.
Lock Types
┌──────────────────────────────────────────────────────────┐
│ LOCK TYPES │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. Shared Lock (S) │
│ • Read lock │
│ • Multiple transactions can hold │
│ • Prevents writes, allows reads │
│ │
│ 2. Exclusive Lock (X) │
│ • Write lock │
│ • Only one transaction can hold │
│ • Prevents both reads and writes │
│ │
│ 3. Update Lock (U) │
│ • Prevents deadlocks │
│ • Converts to exclusive when updating │
│ • SQL Server specific │
│ │
│ 4. Intent Locks (IS, IX, IU) │
│ • Indicate intention to lock at lower level │
│ • Improve locking efficiency │
│ │
└──────────────────────────────────────────────────────────┘
Lock Compatibility Matrix
┌──────────────────────────────────────────────────────────┐
│ LOCK COMPATIBILITY MATRIX │
├──────────────────────────────────────────────────────────┤
│ │
│ Shared (S) Exclusive (X) Update (U) │
│ ───────── ────────── ─────────── ──────── │
│ Shared (S) ✓ ✗ ✓ │
│ Exclusive (X) ✗ ✗ ✗ │
│ Update (U) ✓ ✗ ✗ │
│ │
│ ✓ = Compatible (can coexist) │
│ ✗ = Incompatible (must wait) │
│ │
└──────────────────────────────────────────────────────────┘
Locking Example
-- Explicit locking (SQL Server)
BEGIN TRANSACTION;
-- Shared lock
SELECT * FROM accounts WITH (HOLDLOCK)
WHERE account_id = 1;
-- Exclusive lock
SELECT * FROM accounts WITH (XLOCK)
WHERE account_id = 1;
-- Update lock
SELECT * FROM accounts WITH (UPDLOCK)
WHERE account_id = 1;
COMMIT;
-- MySQL locking
BEGIN;
-- Shared lock
SELECT * FROM accounts WHERE account_id = 1 LOCK IN SHARE MODE;
-- Exclusive lock
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
COMMIT;
Lock Escalation
┌──────────────────────────────────────────────────────────┐
│ LOCK ESCALATION │
├──────────────────────────────────────────────────────────┤
│ │
│ Row Locks (many) │
│ │ │
│ ▼ │
│ Page Locks (fewer) │
│ │ │
│ ▼ │
│ Table Lock (one) │
│ │
│ Escalation occurs when: │
│ • Too many row locks (threshold exceeded) │
│ • Memory pressure │
│ • Improves performance but reduces concurrency │
│ │
└──────────────────────────────────────────────────────────┘
Deadlocks
A deadlock occurs when two or more transactions are waiting for each other to release locks.
Deadlock Scenario
┌──────────────────────────────────────────────────────────┐
│ DEADLOCK SCENARIO │
├──────────────────────────────────────────────────────────┤
│ │
│ Transaction 1 Transaction 2 │
│ ───────────── ───────────── │
│ │
│ BEGIN BEGIN │
│ LOCK Table A LOCK Table B │
│ (holds lock on A) (holds lock on B) │
│ │ │ │
│ ▼ ▼ │
│ Request lock on B Request lock on A │
│ ⏸ WAITS... ⏸ WAITS... │
│ │ │ │
│ └──────────┬───────────┘ │
│ │ │
│ ▼ │
│ DEADLOCK! │
│ │ │
│ ▼ │
│ Database detects deadlock │
│ Chooses victim (usually newer transaction) │
│ Rolls back victim │
│ Other transaction proceeds │
│ │
└──────────────────────────────────────────────────────────┘
Deadlock Prevention
-- 1. Access resources in same order
-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- Transaction 2 (same order)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 2;
COMMIT;
-- 2. Keep transactions short
BEGIN TRANSACTION;
-- Do minimal work
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;
-- 3. Use appropriate isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 4. Use timeout
SET LOCK_TIMEOUT 5000; -- 5 seconds
-- 5. Handle deadlock in application
BEGIN TRY
BEGIN TRANSACTION;
-- Transaction logic
COMMIT;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 -- Deadlock
BEGIN
ROLLBACK;
-- Retry logic
END
END CATCH;
Detecting Deadlocks
-- SQL Server: View deadlock information
SELECT
session_id,
blocking_session_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
-- MySQL: Show processlist
SHOW PROCESSLIST;
-- PostgreSQL: Check for locks
SELECT
pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
Savepoints
Savepoints allow partial rollback within a transaction.
Savepoint Concept
┌──────────────────────────────────────────────────────────┐
│ SAVEPOINT CONCEPT │
├──────────────────────────────────────────────────────────┤
│ │
│ BEGIN TRANSACTION │
│ │ │
│ ▼ │
│ Statement 1 ✓ │
│ │ │
│ ▼ │
│ SAVEPOINT sp1 ←─────────┐ │
│ │ │ │
│ ▼ │ │
│ Statement 2 ✓ │ │
│ │ │ │
│ ▼ │ │
│ SAVEPOINT sp2 ←─────┐ │ │
│ │ │ │ │
│ ▼ │ │ │
│ Statement 3 ✗ │ │ │
│ │ │ │ │
│ ▼ │ │ │
│ ROLLBACK TO sp2 ────┘ │ │
│ (Statement 3 undone) │ │
│ │ │ │
│ ▼ │ │
│ Statement 4 ✗ │ │
│ │ │ │
│ ▼ │ │
│ ROLLBACK TO sp1 ────────┘ │
│ (Statements 2,4 undone) │
│ │ │
│ ▼ │
│ COMMIT │
│ (Only Statement 1 committed) │
│ │
└──────────────────────────────────────────────────────────┘
Savepoint Examples
-- SQL Server
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVE TRANSACTION sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 101);
-- Error occurs
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION sp1; -- Rollback to savepoint
PRINT 'Order items rolled back';
END
COMMIT TRANSACTION;
-- PostgreSQL
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 101);
-- Rollback to savepoint
ROLLBACK TO SAVEPOINT sp1;
-- Release savepoint
RELEASE SAVEPOINT sp1;
COMMIT;
-- MySQL
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 101);
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
Distributed Transactions
Distributed transactions span multiple databases or servers.
Two-Phase Commit (2PC)
┌──────────────────────────────────────────────────────────┐
│ TWO-PHASE COMMIT PROTOCOL │
├──────────────────────────────────────────────────────────┤
│ │
│ Phase 1: Prepare │
│ ──────────────────── │
│ │
│ Coordinator │
│ │ │
│ ├──────▶ Participant 1: Can you commit? │
│ │ Response: Yes/No │
│ │ │
│ ├──────▶ Participant 2: Can you commit? │
│ │ Response: Yes/No │
│ │ │
│ └──────▶ Participant 3: Can you commit? │
│ Response: Yes/No │
│ │
│ Phase 2: Commit/Abort │
│ ────────────────────── │
│ │
│ If all Yes: │
│ Coordinator │
│ │ │
│ ├──────▶ Participant 1: COMMIT │
│ ├──────▶ Participant 2: COMMIT │
│ └──────▶ Participant 3: COMMIT │
│ │
│ If any No: │
│ Coordinator │
│ │ │
│ ├──────▶ Participant 1: ROLLBACK │
│ ├──────▶ Participant 2: ROLLBACK │
│ └──────▶ Participant 3: ROLLBACK │
│ │
└──────────────────────────────────────────────────────────┘
Distributed Transaction Example
-- SQL Server: Distributed transaction
BEGIN DISTRIBUTED TRANSACTION;
-- Update on Server 1
UPDATE Server1.Database1.dbo.accounts
SET balance = balance - 100
WHERE account_id = 1;
-- Update on Server 2
UPDATE Server2.Database2.dbo.accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT TRANSACTION;
-- PostgreSQL: Two-phase commit
BEGIN;
-- Prepare transaction
PREPARE TRANSACTION 'trans_id_123';
-- On another connection, commit or rollback
COMMIT PREPARED 'trans_id_123';
-- OR
ROLLBACK PREPARED 'trans_id_123';
Transaction Best Practices
Best Practices Checklist
┌──────────────────────────────────────────────────────────┐
│ TRANSACTION BEST PRACTICES │
├──────────────────────────────────────────────────────────┤
│ │
│ ✓ Keep Transactions Short │
│ • Minimize time locks are held │
│ • Reduce contention │
│ • Improve concurrency │
│ │
│ ✓ Access Resources in Same Order │
│ • Prevents deadlocks │
│ • Consistent locking pattern │
│ │
│ ✓ Use Appropriate Isolation Level │
│ • Balance consistency vs performance │
│ • READ COMMITTED for most cases │
│ • SERIALIZABLE only when necessary │
│ │
│ ✓ Handle Errors Properly │
│ • Always use TRY-CATCH │
│ • Rollback on errors │
│ • Log failures │
│ │
│ ✓ Avoid User Interaction │
│ • Don't wait for user input │
│ • Complete quickly │
│ │
│ ✓ Use Savepoints for Complex Logic │
│ • Partial rollback capability │
│ • Better error recovery │
│ │
│ ✗ Don't Hold Locks Unnecessarily │
│ ✗ Don't Nest Transactions Deeply │
│ ✗ Don't Mix DDL and DML in Same Transaction │
│ │
└──────────────────────────────────────────────────────────┘
Transaction Template
-- SQL Server transaction template
BEGIN TRY
BEGIN TRANSACTION;
-- Your SQL statements here
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Commit if successful
COMMIT TRANSACTION;
PRINT 'Transaction completed successfully';
END TRY
BEGIN CATCH
-- Rollback on error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Log error
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
Common Use Cases
1. Bank Transfer
-- Complete bank transfer with validation
BEGIN TRANSACTION;
DECLARE @source_balance DECIMAL(10,2);
DECLARE @transfer_amount DECIMAL(10,2) = 100.00;
-- Check source account balance
SELECT @source_balance = balance
FROM accounts WITH (UPDLOCK)
WHERE account_id = 1;
-- Validate sufficient funds
IF @source_balance < @transfer_amount
BEGIN
ROLLBACK;
RAISERROR('Insufficient funds', 16, 1);
RETURN;
END
-- Debit source account
UPDATE accounts
SET balance = balance - @transfer_amount
WHERE account_id = 1;
-- Credit destination account
UPDATE accounts
SET balance = balance + @transfer_amount
WHERE account_id = 2;
-- Log transaction
INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
VALUES (1, 2, @transfer_amount, GETDATE());
COMMIT;
2. Order Processing
-- Process order with inventory check
BEGIN TRANSACTION;
-- Create order
INSERT INTO orders (customer_id, order_date, status)
VALUES (123, GETDATE(), 'pending');
DECLARE @order_id INT = SCOPE_IDENTITY();
-- Add order items and update inventory
INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT @order_id, product_id, quantity, price
FROM cart_items
WHERE customer_id = 123;
-- Update inventory
UPDATE products
SET stock_quantity = stock_quantity - ci.quantity
FROM products p
INNER JOIN cart_items ci ON p.product_id = ci.product_id
WHERE ci.customer_id = 123;
-- Check for negative inventory
IF EXISTS (SELECT 1 FROM products WHERE stock_quantity < 0)
BEGIN
ROLLBACK;
RAISERROR('Insufficient inventory', 16, 1);
RETURN;
END
-- Clear cart
DELETE FROM cart_items WHERE customer_id = 123;
COMMIT;
3. Batch Processing
-- Batch update with savepoints
BEGIN TRANSACTION;
DECLARE @batch_size INT = 1000;
DECLARE @processed INT = 0;
WHILE EXISTS (SELECT 1 FROM staging_table WHERE processed = 0)
BEGIN
-- Create savepoint
SAVE TRANSACTION batch_savepoint;
BEGIN TRY
-- Process batch
UPDATE TOP (@batch_size) staging_table
SET processed = 1,
processed_date = GETDATE()
WHERE processed = 0;
SET @processed = @processed + @@ROWCOUNT;
-- Commit batch
IF @processed % 10000 = 0
BEGIN
COMMIT;
BEGIN TRANSACTION;
END
END TRY
BEGIN CATCH
-- Rollback batch on error
ROLLBACK TRANSACTION batch_savepoint;
-- Log error
INSERT INTO error_log (error_message, error_date)
VALUES (ERROR_MESSAGE(), GETDATE());
END CATCH
END
COMMIT;
Interview Questions
Basic Questions
Q1: What is a transaction?
A transaction is a logical unit of work that contains one or more SQL statements. It ensures that either all operations succeed (commit) or all fail (rollback), maintaining data consistency.
Q2: What are ACID properties?
- Atomicity: All or nothing execution
- Consistency: Database moves from one valid state to another
- Isolation: Concurrent transactions don't interfere
- Durability: Committed changes are permanent
Q3: What's the difference between COMMIT and ROLLBACK?
- COMMIT: Makes all changes permanent and ends the transaction
- ROLLBACK: Discards all changes and ends the transaction
Intermediate Questions
Q4: What are transaction isolation levels?
Four standard isolation levels:
- READ UNCOMMITTED: Lowest isolation, allows dirty reads
- READ COMMITTED: Prevents dirty reads (most common default)
- REPEATABLE READ: Prevents dirty and non-repeatable reads
- SERIALIZABLE: Highest isolation, prevents all anomalies
Q5: What is a deadlock?
A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency. The database detects this and rolls back one transaction (the victim) to break the deadlock.
Q6: How do you prevent deadlocks?
- Access resources in the same order
- Keep transactions short
- Use appropriate isolation levels
- Use lock timeouts
- Handle deadlock errors in application code
Advanced Questions
Q7: Explain the difference between pessimistic and optimistic locking.
Pessimistic Locking:
- Locks data when reading
- Prevents others from modifying
- Use: High contention scenarios
Optimistic Locking:
- No locks when reading
- Check for changes before updating
- Use: Low contention scenarios
-- Pessimistic
SELECT * FROM accounts WITH (UPDLOCK) WHERE account_id = 1;
-- Optimistic
UPDATE accounts
SET balance = 500, version = version + 1
WHERE account_id = 1 AND version = @original_version;
Q8: What is two-phase locking?
Two-phase locking is a concurrency control protocol:
- Growing Phase: Acquire locks, cannot release
- Shrinking Phase: Release locks, cannot acquire
Ensures serializability but can lead to deadlocks.
Q9: How do distributed transactions work?
Distributed transactions use the Two-Phase Commit (2PC) protocol:
- Prepare Phase: All participants vote to commit or abort
- Commit Phase: If all vote yes, commit; otherwise, abort
Ensures atomicity across multiple databases.
Q10: What's the difference between savepoints and nested transactions?
Savepoints:
- Markers within a transaction
- Allow partial rollback
- Supported by most databases
Nested Transactions:
- Transactions within transactions
- Limited support (SQL Server)
- Inner transaction rollback affects outer
Summary
Transactions are fundamental to maintaining data integrity in databases. Key takeaways:
- ACID Properties: Ensure reliable transaction processing
- Isolation Levels: Balance consistency vs performance
- Locking: Prevents concurrent access issues
- Deadlocks: Understand causes and prevention
- Best Practices: Keep transactions short, handle errors, use appropriate isolation
Master transactions to build robust, reliable database applications that maintain data consistency even under concurrent access and system failures.