Table of Contents
- Introduction
- What Is a Transaction in SQL?
- Why Transactions Matter
- The ACID Properties
- Transaction Control Commands
BEGINorSTART TRANSACTIONCOMMIT– Finalizing ChangesROLLBACK– Undoing Changes- Autocommit Behavior
- Nested Transactions and Savepoints
- Using
SAVEPOINTandROLLBACK TO - Real-World Example: Bank Transfers
- Real-World Example: Inventory Management
- Transactions in Different RDBMS
- Isolation Levels Explained
- Common Errors and Misunderstandings
- Performance Considerations with Transactions
- Best Practices for Transaction Management
- When Not to Use Transactions
- Summary and What’s Next
1. Introduction
Transactions are the backbone of reliable database operations. They ensure that a series of SQL operations either complete entirely or not at all — preventing partial updates and ensuring consistency.
2. What Is a Transaction in SQL?
A transaction is a group of one or more SQL statements executed as a single unit. It guarantees that either all changes are applied or none are.
3. Why Transactions Matter
- Prevents data corruption
- Ensures consistency in business logic
- Handles failures gracefully
- Supports safe concurrent access
4. The ACID Properties
| Property | Description |
|---|---|
| Atomicity | All or nothing – either full success or full undo |
| Consistency | Database remains in a valid state |
| Isolation | Transactions don’t interfere with each other |
| Durability | Committed changes persist even after crashes |
5. Transaction Control Commands
| Command | Description |
|---|---|
BEGIN / START TRANSACTION | Starts a new transaction |
COMMIT | Saves all changes made in the transaction |
ROLLBACK | Undoes all changes made since last BEGIN |
6. BEGIN or START TRANSACTION
Starts a new transaction block:
sqlCopyEditBEGIN; -- or START TRANSACTION;
After this point, changes are not visible to others until COMMIT.
7. COMMIT – Finalizing Changes
sqlCopyEditCOMMIT;
This makes all changes permanent and visible to others. Once committed, changes cannot be rolled back.
8. ROLLBACK – Undoing Changes
sqlCopyEditROLLBACK;
Reverts all changes made since the transaction began. Useful for error handling or unexpected input.
9. Autocommit Behavior
Most RDBMSs have autocommit enabled by default:
- Every individual SQL statement is treated as a transaction
- Use
SET autocommit = 0;(MySQL) or explicitly callBEGIN;to override
10. Nested Transactions and Savepoints
Some databases support savepoints to simulate nested transactions.
sqlCopyEditBEGIN;
SAVEPOINT sp1;
-- Do something
ROLLBACK TO sp1; -- Rolls back only to the savepoint
COMMIT;
Useful in large or conditional operations.
11. Using SAVEPOINT and ROLLBACK TO
sqlCopyEditSAVEPOINT before_update;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Something went wrong
ROLLBACK TO before_update;
-- Continue other safe actions
COMMIT;
12. Real-World Example: Bank Transfers
sqlCopyEditBEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
If one update fails, use ROLLBACK to prevent inconsistent funds transfer.
13. Real-World Example: Inventory Management
sqlCopyEditBEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 101;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (999, 101, 1);
COMMIT;
Prevents deducting stock if insert fails.
14. Transactions in Different RDBMS
| RDBMS | Transaction Command |
|---|---|
| MySQL | START TRANSACTION, COMMIT, ROLLBACK |
| PostgreSQL | BEGIN, COMMIT, ROLLBACK |
| SQL Server | BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN |
| Oracle | Implicit transactions; uses COMMIT / ROLLBACK |
15. Isolation Levels Explained
Control how concurrent transactions interact:
| Level | Description |
|---|---|
| READ UNCOMMITTED | Can read uncommitted data (dirty reads) |
| READ COMMITTED | Can only read committed data |
| REPEATABLE READ | Ensures same rows can’t change mid-transaction |
| SERIALIZABLE | Highest isolation; fully serial execution |
sqlCopyEditSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
16. Common Errors and Misunderstandings
| Mistake | Issue |
|---|---|
Forgetting COMMIT | Locks held, changes lost on disconnect |
Using ROLLBACK too late | May not undo already committed changes |
Nested BEGINs | Not supported in most RDBMS without savepoints |
17. Performance Considerations with Transactions
- Keep transactions short
- Long transactions hold locks and reduce concurrency
- Use appropriate isolation level to avoid blocking
- Don’t open transactions without eventually committing or rolling back
18. Best Practices for Transaction Management
- Always handle exceptions — use
ROLLBACKon error - Use transactions for units of work that must succeed together
- Wrap logic in stored procedures when needed
- Avoid user interaction inside a transaction block
- Test rollback paths during development
19. When Not to Use Transactions
- For read-only SELECTs (unless in complex consistency scenarios)
- For autonomous logging/auditing actions that must always persist
- For non-critical operations that can fail independently
20. Summary and What’s Next
Transactions are fundamental to reliable and consistent SQL applications. By using BEGIN, COMMIT, and ROLLBACK wisely, you ensure that your application logic maintains data integrity — even in the face of failures or concurrency.
