🔄 Transaction Processing in DBMS

---

🔰 What is Transaction Processing?

Transaction Processing refers to managing multiple operations on a database as a single, logical unit of work, called a transaction. The goal is to ensure data consistency, integrity, and reliability even in cases of failures (system crash, power loss, etc.).

---

🧾 What is a Transaction?

A transaction is a sequence of one or more SQL operations (e.g., INSERT, UPDATE, DELETE) executed as a single unit to perform a specific task.

Example:

BEGIN TRANSACTION
UPDATE Accounts SET balance = balance - 500 WHERE account_id = 'A101';
UPDATE Accounts SET balance = balance + 500 WHERE account_id = 'A102';
COMMIT;

This transaction transfers ₹500 from A101 to A102. It should either complete entirely or not at all.

---

✅ Properties of Transactions (ACID)

To ensure reliable transaction processing, every transaction must follow the ACID properties:

Property	Description
A - Atomicity	All operations in a transaction are treated as one unit: either all succeed or all fail.
C - Consistency	A transaction brings the database from one valid state to another valid state.
I - Isolation	Transactions operate independently. Intermediate states are not visible to other transactions.
D - Durability	Once a transaction is committed, its changes are permanent, even if the system crashes.

---

🔄 States of a Transaction

A transaction goes through several states:

1. Active – Transaction is executing.
2. Partially Committed – Final operation is executed.
3. Committed – All changes are saved to the database.
4. Failed – A problem occurs; transaction cannot proceed.
5. Aborted – Changes are rolled back.

(If you’d like this as a local image or in notes, let me know)

---

🔐 Concurrency Control in Transaction Processing

When multiple transactions execute concurrently, issues can occur such as:

🔁 Common Problems:

Problem	Description
Lost Update	Two transactions overwrite each other’s updates.
Dirty Read	A transaction reads data from another uncommitted transaction.
Non-repeatable Read	A transaction reads the same data twice and gets different values.
Phantom Read	A query returns different results when executed again in the same transaction.

🛡️ Solutions:

• Locking Protocols (e.g., Two-Phase Locking)
• Timestamp Ordering
• Optimistic Concurrency Control

---

🔁 Transaction Control Commands in SQL

Command	Description
BEGIN TRANSACTION	Starts a new transaction
COMMIT	Saves all changes made during the transaction
ROLLBACK	Undoes all changes made during the transaction
SAVEPOINT	Creates a point in the transaction to which you can roll back

---

📘 Example in SQL:

BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE id = 101;
SAVEPOINT before_bonus;
UPDATE employees SET bonus = 5000 WHERE id = 101;
ROLLBACK TO before_bonus;
COMMIT;

This will apply salary update but undo bonus update, thanks to the savepoint.

---

🧠 Importance of Transaction Processing

• Ensures data integrity and consistency
• Supports concurrent access by multiple users
• Recovers from failures (using logs, backups, checkpoints)
• Supports atomicity and rollback in case of errors

---

💥 Failure Types Handled in Transaction Processing

Failure Type	Description
System Crash	Power failure, OS crash
Transaction Failure	Logical errors in the transaction
Media Failure	Disk crash or file corruption
Concurrency Conflicts	Data inconsistency due to parallel transactions

---

🧩 Transaction Processing System (TPS)

A Transaction Processing System is a type of software system that handles and manages transactions for applications such as:

• Banking systems
• Reservation systems
• E-commerce platforms
• Inventory management

Features of TPS:

• High Availability
• Atomicity
• Consistency and Security
• Support for concurrent users

---

📝 Summary

Concept	Key Points
Transaction	A logical unit of work (group of DB operations)
ACID	Ensures reliability and consistency
Transaction States	Active, Partially Committed, Committed, Failed, Aborted
Issues	Lost updates, Dirty reads, Phantom reads
Controls	Locking, Timestamp ordering, Rollback, Savepoints

---