SQL Transactions: BEGIN, COMMIT, and ROLLBACK

SQL Transactions: BEGIN, COMMIT, and ROLLBACK

A transaction is a sequence of SQL operations that are executed as a single atomic unit. Either all operations succeed (COMMIT) or all are undone (ROLLBACK), ensuring data consistency.

Basic Syntax

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

If any statement fails, execute ROLLBACK to undo all changes.

ACID Properties

• Atomicity: All or nothing. Partial transactions never persist
• Consistency: Data moves from one valid state to another
• Isolation: Concurrent transactions do not interfere with each other
• Durability: Committed data survives system crashes

Savepoints

Create checkpoints within a transaction for partial rollbacks:

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 100.00);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
-- If this fails:
ROLLBACK TO SAVEPOINT order_created;
-- The order insert is preserved, only items are rolled back

COMMIT;

Isolation Levels

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes (MySQL: No)
SERIALIZABLE	No	No	No

Higher isolation levels provide more consistency but reduce concurrency and throughput.

Deadlock Prevention

1. Access tables in consistent order across all transactions
2. Keep transactions short to minimize lock duration
3. Use appropriate isolation levels (not higher than needed)
4. Retry on deadlock with exponential backoff in application code
5. Use row-level locking (SELECT ... FOR UPDATE) explicitly when needed

Error Handling Pattern

In application code, always use try/catch blocks around transaction operations and ensure ROLLBACK is called on any error. Never leave transactions open as they hold locks and block other operations.

Transactions are the foundation of data integrity in relational databases and must be used whenever multiple related operations must succeed or fail together.