SQL

Transactions in SQL

💳 A Transaction in SQL is a sequence of one or more SQL statements executed as a single logical unit of work. A transaction ensures that either all operations succeed or none of them are applied, protecting the consistency and integrity of the database.

📖 What is a Transaction?

A transaction groups multiple database operations into one unit. If every statement executes successfully, the changes are permanently saved. If any operation fails, the database can undo all changes made during the transaction, returning the database to its previous consistent state.

Information

Transactions are fundamental to relational databases and are widely used in banking systems, e-commerce platforms, inventory management, reservation systems, and financial applications.

🎯 Why Use Transactions?

Transactions ensure reliable and consistent database operations, especially when multiple SQL statements depend on each other.

📌 Maintain data consistency.
📌 Prevent partial updates.
📌 Recover safely from errors.
📌 Protect data during concurrent access.
📌 Ensure business operations complete correctly.

💡 Real-World Example

Imagine transferring money from one bank account to another:

Withdraw money from Account A.
Deposit money into Account B.

If the withdrawal succeeds but the deposit fails, the database must undo the withdrawal to prevent money from disappearing. A transaction guarantees that both operations succeed together or both are rolled back.

📝 Basic Transaction Syntax

Transaction Syntax

BEGIN TRANSACTION;

-- SQL statements

COMMIT;

🔄 Transaction Lifecycle

Step	Description
BEGIN TRANSACTION	Starts a new transaction.
Execute SQL	Run one or more SQL statements.
COMMIT	Permanently save all changes.
ROLLBACK	Undo all changes made during the transaction.

💾 Commit a Transaction

A COMMIT permanently saves all successful changes.

Commit Example

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 101;

UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 102;

COMMIT;

After the COMMIT, both account balances are permanently updated.

↩️ Roll Back a Transaction

If an error occurs, ROLLBACK restores the database to its previous state.

Rollback Example

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 101;

-- An unexpected error occurs

ROLLBACK;

The withdrawal is undone because the transaction was rolled back.

📍 Savepoints

A SAVEPOINT creates a checkpoint within a transaction. Instead of rolling back the entire transaction, you can roll back only to a specific savepoint if supported by your database system.

Using a Savepoint

BEGIN TRANSACTION;

INSERT INTO Orders (...)
VALUES (...);

SAVEPOINT OrderCreated;

UPDATE Inventory
SET Quantity = Quantity - 1;

-- If needed:
ROLLBACK TO SAVEPOINT OrderCreated;

COMMIT;

Important

Savepoint syntax varies among database systems. Some databases use SAVE TRANSACTION or slightly different keywords.

🛡️ ACID Properties

Every reliable transaction follows the ACID principles.

Property	Description
Atomicity	All operations succeed together or all fail together.
Consistency	The database remains in a valid state before and after the transaction.
Isolation	Concurrent transactions do not interfere with each other.
Durability	Committed changes survive system failures.

Remember

💡 The ACID properties are the foundation of reliable transaction processing in relational database systems.

📊 Transaction Workflow

Stage	Action
1	Start the transaction.
2	Execute SQL statements.
3	If successful, execute COMMIT.
4	If an error occurs, execute ROLLBACK.

⚖️ COMMIT vs ROLLBACK

Feature	COMMIT	ROLLBACK
Purpose	Save changes.	Undo changes.
Data Modified	Permanent.	Restored to the previous state.
Used After Success	✅ Yes	❌ No
Used After Failure	❌ No	✅ Yes

💼 Real-World Applications

🏦 Bank fund transfers.
🛒 Online order processing.
🎟️ Ticket reservation systems.
📦 Inventory management.
💳 Payment processing.
🏥 Hospital and patient management systems.

🗄️ Database Compatibility

Database System	Transaction Support
MySQL	✅ Supported by transactional storage engines such as InnoDB.
PostgreSQL	✅ Full ACID-compliant transaction support.
SQL Server	✅ Comprehensive transaction management.
Oracle	✅ Full transaction support with savepoints.
SQLite	✅ Supports transactions, including savepoints.

⚠️ Common Mistakes

❌ Forgetting to commit successful transactions.
❌ Leaving transactions open for too long, causing unnecessary locking.
❌ Ignoring error handling and rollback logic.
❌ Assuming every SQL statement automatically belongs to the same explicit transaction.

Warning

Long-running transactions can hold locks for extended periods, reducing concurrency and affecting overall database performance.

⚠️ Best Practices

Best Practice

Keep transactions as short as possible, commit immediately after successful operations, roll back when errors occur, use savepoints for complex workflows, implement proper error handling, and avoid unnecessary user interaction while a transaction remains open.

🚀 Key Points to Remember

📌 A transaction groups multiple SQL statements into one logical unit.
📌 Transactions guarantee all-or-nothing execution.
📌 COMMIT permanently saves changes.
📌 ROLLBACK reverses uncommitted changes.
📌 Savepoints enable partial rollbacks in many database systems.
📌 ACID properties ensure reliable and consistent transaction processing.

>>"Transactions protect your data by ensuring every critical operation either completes successfully or leaves the database unchanged."

Summary

✅ SQL transactions provide a reliable mechanism for executing multiple database operations as a single unit of work. Through BEGIN TRANSACTION, COMMIT, and ROLLBACK, transactions maintain data integrity, prevent partial updates, and uphold the ACID principles that form the foundation of modern relational database systems.