SQL Advanced
Transactions (COMMIT, ROLLBACK)
Ensure data consistency with atomic operations that either fully succeed or fully fail.
- COMMIT β Permanently saves all changes made during the current transaction
- ROLLBACK β Reverts all changes made during the current transaction to a previous state
- SAVEPOINT β Creates intermediate checkpoints for partial rollbacks within a transaction Without transactions, partial failures leave your database in an inconsistent state.
What Is a Transaction?
Transactions are the foundation of data integrity in relational databases. They guarantee that complex operations β such as transferring money between accounts β either complete entirely or not at all, even in the event of system failures.
-- A classic bank transfer as a single transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2002;
COMMIT;
The ACID Properties
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All operations in a transaction succeed or none do | A transfer debits and credits β or neither happens |
| Consistency | A transaction brings the database from one valid state to another | Total balance across accounts remains constant |
| Isolation | Concurrent transactions do not interfere with each other | Two transfers on the same account are serialized |
| Durability | Once committed, changes survive system failures | A committed transfer persists after a power outage |
BEGIN, COMMIT, and ROLLBACK
Every transaction starts with BEGIN and ends with either COMMIT (success) or ROLLBACK (failure).
-- Start a transaction
BEGIN TRANSACTION;
-- Perform operations
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (42, 7, 3, GETDATE());
UPDATE inventory SET stock = stock - 3 WHERE product_id = 7;
-- Verify before committing
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM inventory WHERE product_id = 7;
-- If everything looks correct
COMMIT;
-- If something went wrong
-- ROLLBACK;
-- Error handling with transactions
BEGIN TRANSACTION;
BEGIN TRY
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
INSERT INTO transfer_log (from_id, to_id, amount, transfer_date)
VALUES (1, 2, 1000, GETDATE());
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
THROW;
END CATCH;
SAVEPOINT for Partial Rollbacks
BEGIN TRANSACTION;
INSERT INTO customers (name, email) VALUES ('Alice', 'alice@example.com');
SAVEPOINT after_customer_insert;
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (SCOPE_IDENTITY(), 5, 2);
-- Oops, wrong product β roll back only the order
ROLLBACK TO SAVEPOINT after_customer_insert;
-- Customer insert is still intact β insert correct order
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (SCOPE_IDENTITY(), 8, 2);
COMMIT;
Transaction Isolation Levels
Different isolation levels balance consistency against concurrency. Higher isolation reduces anomalies but decreases performance.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | Highest |
| READ COMMITTED | Prevented | Possible | Possible | High |
| REPEATABLE READ | Prevented | Prevented | Possible | Medium |
| SERIALIZABLE | Prevented | Prevented | Prevented | Lowest |
-- Set isolation level for a session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- This transaction now runs with full isolation
SELECT * FROM products WHERE category = 'Electronics';
-- No phantom reads allowed β other transactions cannot insert
-- new Electronics rows while this transaction is active
UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
COMMIT;
-- Or set it per-transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM inventory WHERE product_id = 42;
COMMIT;
Autocommit Behavior
Most database clients operate in autocommit mode by default, where each individual statement is implicitly treated as its own transaction.
-- Autocommit (default in most clients)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- This is committed immediately
-- Explicit transaction gives you control
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Not yet committed β can still be rolled back
ROLLBACK;
-- The UPDATE is undone
Nested Transactions
SQL Server supports nested BEGIN TRANSACTION calls, but only the outermost COMMIT actually persists changes. Inner commits merely decrement the transaction nesting level.
BEGIN TRANSACTION; -- nesting level = 1
INSERT INTO logs (message) VALUES ('Step 1');
BEGIN TRANSACTION; -- nesting level = 2
INSERT INTO logs (message) VALUES ('Step 2');
COMMIT; -- nesting level = 1 (no real commit)
BEGIN TRANSACTION; -- nesting level = 2
INSERT INTO logs (message) VALUES ('Step 3');
ROLLBACK; -- undoes Step 3 only, nesting = 1
COMMIT; -- commits Step 1 and Step 2
Practical Example: Order Processing
BEGIN TRANSACTION;
-- Step 1: Create the order
INSERT INTO orders (customer_id, order_date, status)
VALUES (101, GETDATE(), 'PENDING');
DECLARE @order_id INT = SCOPE_IDENTITY();
-- Step 2: Add order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 10, 2, 29.99),
(@order_id, 15, 1, 49.99);
-- Step 3: Update inventory
UPDATE inventory SET stock = stock - 2 WHERE product_id = 10;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 15;
-- Step 4: Verify stock is not negative
IF EXISTS (SELECT 1 FROM inventory WHERE stock < 0)
BEGIN
RAISERROR('Insufficient stock', 16, 1);
ROLLBACK;
RETURN;
END
-- Step 5: Update order total
UPDATE orders
SET total = (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = @order_id)
WHERE order_id = @order_id;
COMMIT;