Transactions in SQL: BEGIN, COMMIT, and ROLLBACK Explained

---

Table of Contents

1. Introduction
2. What Is a Transaction in SQL?
3. Why Transactions Matter
4. The ACID Properties
5. Transaction Control Commands
6. BEGIN or START TRANSACTION
7. COMMIT – Finalizing Changes
8. ROLLBACK – Undoing Changes
9. Autocommit Behavior
10. Nested Transactions and Savepoints
11. Using SAVEPOINT and ROLLBACK TO
12. Real-World Example: Bank Transfers
13. Real-World Example: Inventory Management
14. Transactions in Different RDBMS
15. Isolation Levels Explained
16. Common Errors and Misunderstandings
17. Performance Considerations with Transactions
18. Best Practices for Transaction Management
19. When Not to Use Transactions
20. 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 call BEGIN; 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 ROLLBACK on 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.