SQL - Transactions (BEGIN, COMMIT, ROLLBACK)
A transaction represents a logical unit of work containing one or more SQL statements. The ACID properties (Atomicity, Consistency, Isolation, Durability) define transaction behavior. Without...
Key Insights
- Transactions ensure data consistency by grouping multiple SQL operations into atomic units that either complete entirely or fail without partial changes
- The isolation level you choose directly impacts concurrency performance and determines which anomalies (dirty reads, non-repeatable reads, phantom reads) can occur
- Proper savepoint usage enables partial rollbacks within transactions, allowing recovery from errors without discarding all work
Understanding Transaction Fundamentals
A transaction represents a logical unit of work containing one or more SQL statements. The ACID properties (Atomicity, Consistency, Isolation, Durability) define transaction behavior. Without transactions, a bank transfer could debit one account but fail to credit another, leaving data in an inconsistent state.
-- Basic transaction structure
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
Most databases operate in autocommit mode by default, where each statement executes as its own transaction. Explicit transaction control disables this behavior:
-- PostgreSQL/MySQL
SET autocommit = 0;
-- SQL Server
SET IMPLICIT_TRANSACTIONS ON;
Transaction Control Statements
The three primary transaction control commands are BEGIN, COMMIT, and ROLLBACK. Syntax varies slightly across database systems:
-- PostgreSQL
BEGIN;
-- or
START TRANSACTION;
-- SQL Server
BEGIN TRANSACTION;
-- or
BEGIN TRAN;
-- MySQL
START TRANSACTION;
When you execute COMMIT, all changes become permanent and visible to other transactions:
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (100, 250.00);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LAST_INSERT_ID(), 5, 2);
COMMIT; -- Both inserts are now permanent
ROLLBACK discards all changes made during the transaction:
BEGIN TRANSACTION;
DELETE FROM customers WHERE customer_id = 100;
-- Realize the mistake
ROLLBACK; -- Customer record remains intact
Practical Transaction Patterns
Multi-Table Updates
When updating related tables, transactions ensure referential integrity:
BEGIN TRANSACTION;
-- Create invoice
INSERT INTO invoices (customer_id, invoice_date, total)
VALUES (500, CURRENT_DATE, 1500.00);
DECLARE @invoice_id INT = SCOPE_IDENTITY();
-- Add line items
INSERT INTO invoice_items (invoice_id, product_id, quantity, price)
VALUES
(@invoice_id, 10, 5, 100.00),
(@invoice_id, 15, 10, 100.00);
-- Update inventory
UPDATE products SET stock_quantity = stock_quantity - 5 WHERE product_id = 10;
UPDATE products SET stock_quantity = stock_quantity - 10 WHERE product_id = 15;
-- Verify sufficient stock
IF EXISTS (SELECT 1 FROM products WHERE product_id IN (10, 15) AND stock_quantity < 0)
BEGIN
ROLLBACK;
RAISERROR('Insufficient stock', 16, 1);
END
ELSE
BEGIN
COMMIT;
END
Conditional Transactions
Implement business logic that determines whether to commit or rollback:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 100;
-- Check for overdraft
DECLARE @new_balance DECIMAL(10,2);
SELECT @new_balance = balance FROM accounts WHERE account_id = 100;
IF @new_balance < -1000
BEGIN
ROLLBACK;
SELECT 'Transaction denied: Overdraft limit exceeded' AS message;
END
ELSE
BEGIN
COMMIT;
SELECT 'Transaction successful' AS message;
END
Savepoints for Partial Rollbacks
Savepoints create markers within a transaction, enabling rollback to specific points without abandoning the entire transaction:
BEGIN TRANSACTION;
INSERT INTO audit_log (action, timestamp)
VALUES ('Process started', CURRENT_TIMESTAMP);
SAVE TRANSACTION step1;
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 100;
SAVE TRANSACTION step2;
-- This operation might fail
BEGIN TRY
INSERT INTO shipments (product_id, quantity, destination)
VALUES (100, 10, 'Invalid Address Format!@#$');
END TRY
BEGIN CATCH
-- Rollback only the shipment insert
ROLLBACK TRANSACTION step2;
-- Log the error but continue
INSERT INTO error_log (error_message, timestamp)
VALUES (ERROR_MESSAGE(), CURRENT_TIMESTAMP);
END CATCH;
-- Commit the inventory update and audit log
COMMIT;
PostgreSQL syntax differs slightly:
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (100, 500);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1000, 50, 100);
-- Rollback just the order_items insert
ROLLBACK TO SAVEPOINT before_items;
-- Different items instead
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1000, 51, 50);
COMMIT;
Isolation Levels and Concurrency
Isolation levels control how transactions interact with concurrent operations. Each level prevents specific anomalies:
-- SQL Server
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Allows dirty reads, highest concurrency
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Prevents dirty reads (default in most databases)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Prevents dirty and non-repeatable reads
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Prevents all anomalies, lowest concurrency
Demonstration of isolation level impact:
-- Session 1: READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Returns 1000
-- Wait here...
SELECT balance FROM accounts WHERE account_id = 1; -- May return different value
COMMIT;
-- Session 2: Concurrent update
BEGIN TRANSACTION;
UPDATE accounts SET balance = 500 WHERE account_id = 1;
COMMIT;
With REPEATABLE READ, the second SELECT in Session 1 would return 1000, not 500:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Returns 1000
-- Session 2 updates and commits
SELECT balance FROM accounts WHERE account_id = 1; -- Still returns 1000
COMMIT;
Deadlock Handling
Deadlocks occur when transactions wait for each other’s locks. Databases automatically detect and resolve these by rolling back one transaction:
-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Waiting to lock account 2...
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- Transaction 2 (concurrent)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
-- Waiting to lock account 1... DEADLOCK!
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
COMMIT;
Implement retry logic for deadlock victims:
DECLARE @retries INT = 3;
DECLARE @success BIT = 0;
WHILE @retries > 0 AND @success = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- Access resources in consistent order to prevent deadlocks
UPDATE accounts SET balance = balance - 100
WHERE account_id = CASE WHEN 1 < 2 THEN 1 ELSE 2 END;
UPDATE accounts SET balance = balance + 100
WHERE account_id = CASE WHEN 1 < 2 THEN 2 ELSE 1 END;
COMMIT;
SET @success = 1;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 -- Deadlock
BEGIN
ROLLBACK;
SET @retries = @retries - 1;
WAITFOR DELAY '00:00:00.100'; -- Brief pause before retry
END
ELSE
BEGIN
ROLLBACK;
THROW;
END
END CATCH
END;
Transaction Best Practices
Keep transactions short to minimize lock duration:
-- Bad: Long-running transaction
BEGIN TRANSACTION;
SELECT * FROM large_table; -- Expensive query
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;
-- Good: Minimal transaction scope
SELECT * FROM large_table; -- Outside transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;
Always include error handling to prevent orphaned transactions:
BEGIN TRANSACTION;
BEGIN TRY
-- Your operations here
INSERT INTO orders (customer_id, total) VALUES (100, 500);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
-- Log or re-throw error
THROW;
END CATCH;
Use explicit locking hints when necessary to prevent specific concurrency issues:
BEGIN TRANSACTION;
-- Acquire update lock immediately
SELECT balance
FROM accounts WITH (UPDLOCK, ROWLOCK)
WHERE account_id = 1;
-- Perform calculation
UPDATE accounts
SET balance = balance * 1.05
WHERE account_id = 1;
COMMIT;
Transactions form the foundation of reliable data manipulation in SQL. Master these patterns to build systems that maintain consistency under concurrent access while optimizing for performance through appropriate isolation levels and transaction scope management.