Lecture 10 - Introduction to Transactions

🎯 Learning Objectives

Understand the motivation and need for database transactions
Master the four ACID properties and their significance
Analyze different transaction states and state transitions
Distinguish between serial and concurrent schedules
Apply transaction concepts to real-world scenarios

💡 Motivation for Transactions

Why Do We Need Transactions?

In multi-user database environments, multiple operations often need to be executed as a single logical unit. Consider these real-world scenarios:

🏦 Banking System Example

Fund Transfer: Transfer $500 from Account A to Account B

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
COMMIT;

Problem: What if the system crashes after the first UPDATE but before the second? Account A loses $500, but Account B doesn't receive it!

🛒 E-commerce Example

Order Processing: Customer purchases a product

Reduce product inventory
Create order record
Process payment
Update customer account

Challenge: All steps must succeed, or none should be applied!

🔍 What is a Transaction?

A transaction is a logical unit of work that contains one or more database operations (read, write, update, delete) that must be executed as a single, indivisible unit.

Key Characteristics:

Logical Unit: Multiple operations treated as one
All-or-Nothing: Either all operations succeed or all fail
Consistency Preservation: Database remains in valid state
Isolation: Concurrent transactions don't interfere
Durability: Changes persist after completion

⚡ ACID Properties

ACID is the cornerstone of transaction processing, ensuring database reliability and consistency:

🔒 Atomicity

Transaction is indivisible - either all operations execute successfully, or none execute at all.

"All or Nothing"

✅ Consistency

Transaction transforms database from one consistent state to another consistent state.

"Valid State Always"

🔐 Isolation

Concurrent transactions execute independently without interfering with each other.

"Independent Execution"

💾 Durability

Once committed, changes are permanent and survive system failures.

"Permanent Changes"

🏦 ACID Illustrated: Bank Transfer Example

Scenario: Transfer $1000 from Alice's account to Bob's account

BEGIN TRANSACTION;
-- Step 1: Check Alice's balance
SELECT balance FROM accounts WHERE name = 'Alice'; -- Returns $5000

-- Step 2: Deduct from Alice's account
UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';

-- Step 3: Add to Bob's account
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Bob';
COMMIT;

ACID Analysis:

Atomicity: If any step fails (e.g., insufficient funds), the entire transaction is rolled back. No partial transfers occur.
Consistency: Total money in the system remains constant ($5000 + $3000 = $4000 + $4000).
Isolation: Other transactions can't see Alice's reduced balance until the transaction commits.
Durability: Once committed, the transfer is permanent even if the system crashes immediately after.

📊 Transaction States

A transaction progresses through various states during its lifecycle:

Figure: Transaction States in DBMS
Source: GeeksforGeeks - Transaction States in DBMS

State Descriptions:

Active: Initial state - transaction is executing
Partially Committed: All operations completed, but changes not yet written to disk
Committed: Transaction completed successfully, all changes permanent
Failed: Transaction cannot proceed due to error or system failure
Aborted: Transaction rolled back, database restored to previous state

State Transition Example

Normal Flow: Active → Partially Committed → Committed

Error Flow: Active → Failed → Aborted

System Failure: Partially Committed → Failed → Aborted

📅 Schedules: Serial vs Concurrent

What is a Schedule?

A schedule defines the chronological order of execution of operations from multiple concurrent transactions.

🔄 Serial Schedule

Transactions execute one after another, completely isolated.

T1: R(A) W(A) R(B) W(B)
T2:                     R(A) W(A) R(C) W(C)

Timeline: T1 completes entirely, then T2 starts

Pros: Always correct, no conflicts
Cons: Poor performance, low concurrency

⚡ Concurrent Schedule

Operations from different transactions can interleave.

T1: R(A) W(A)      R(B) W(B)
T2:          R(A) W(A)      R(C) W(C)

Timeline: Operations interleave for better performance

Pros: High performance, better resource utilization
Cons: Risk of conflicts and inconsistencies

Key Considerations:

Serializability: Concurrent schedule should be equivalent to some serial schedule
Conflict Detection: Identify operations that access same data item
Concurrency Control: Mechanisms to ensure correct concurrent execution

🎯 Real-World Transaction Examples

🏥 Hospital Management System

Patient Admission Transaction:

Create patient record
Assign hospital bed
Update bed availability
Generate admission bill
Update insurance records

ACID Requirement: If any step fails (e.g., no beds available), all steps must be undone.

✈️ Airline Reservation System

Flight Booking Transaction:

Check seat availability
Reserve seat
Process payment
Generate ticket
Update flight capacity

Challenge: Multiple users booking simultaneously - need proper isolation!

🔧 Transaction Control Statements

SQL Transaction Control Commands:

-- Start a transaction
BEGIN TRANSACTION; -- or START TRANSACTION;

-- Your database operations here
INSERT INTO orders (customer_id, product_id, quantity) VALUES (123, 456, 2);
UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 456;
UPDATE customers SET total_orders = total_orders + 1 WHERE id = 123;

-- Commit the transaction (make changes permanent)
COMMIT;

-- Or rollback if something goes wrong
-- ROLLBACK;

Transaction Control Flow:

BEGIN/START: Marks the beginning of transaction
COMMIT: Makes all changes permanent
ROLLBACK: Undoes all changes made in transaction
SAVEPOINT: Creates intermediate points for partial rollback

⚠️ Transaction Challenges & Problems

Common Issues in Concurrent Execution:

Lost Update: Two transactions update same data, one update is lost
Dirty Read: Reading uncommitted data from another transaction
Non-repeatable Read: Reading same data twice gives different results
Phantom Read: New rows appear between repeated queries

Lost Update Problem Example

Time | Transaction T1        | Transaction T2        | Account Balance
-----|----------------------|----------------------|----------------
1    | READ(balance) = $1000|                      | $1000
2    |                      | READ(balance) = $1000| $1000  
3    | balance = $1000 + $100|                     | $1000
4    |                      | balance = $1000 + $200| $1000
5    | WRITE(balance) = $1100|                     | $1100
6    |                      | WRITE(balance) = $1200| $1200

Problem: T1's update ($100) is lost! Final balance should be $1300, not $1200.

Dirty Read Problem Example

Time | Transaction T1 (Withdraw $200)  | Transaction T2 (Check Balance) | Account A
-----|--------------------------------|--------------------------------|-----------
1    | BEGIN;                         |                                 | $1000
2    | UPDATE accounts SET balance = 800  |                             | $800
     | WHERE account_id = 'A';        |                                 |
3    |                                 | BEGIN;                          | $800
4    |                                 | SELECT balance FROM accounts    | $800 (Dirty Read!)
     |                                 | WHERE account_id = 'A';         |
5    | ROLLBACK; (Transaction fails)   |                                 | $1000
6    |                                 | COMMIT; (Uses invalid data)     | $1000

Problem: T2 reads uncommitted data from T1. When T1 rolls back, T2's operations are based on invalid data.

Non-repeatable Read Example

Time | Transaction T1 (Check Balance) | Transaction T2 (Update Balance) | Account A
-----|-------------------------------|-----------------------------------|-----------
1    | BEGIN;                        |                                   | $1000
2    | SELECT balance FROM accounts  |                                   | $1000
     | WHERE account_id = 'A';       |                                   |
3    |                               | BEGIN;                            | $1000
4    |                               | UPDATE accounts SET balance = 1200 | $1200
     |                               | WHERE account_id = 'A';            |
5    |                               | COMMIT;                           | $1200
6    | SELECT balance FROM accounts  |                                   | $1200 (Different from first read!)
     | WHERE account_id = 'A';       |                                   |
7    | COMMIT;                       |                                   | $1200

Problem: T1 reads the same row twice but gets different results because T2 modified it in between.

Phantom Read Example

Time | Transaction T1 (Calculate Total) | Transaction T2 (Add Order) | Orders Table
-----|---------------------------------|-----------------------------|-------------
1    | BEGIN;                          |                             | [Order1, Order2]
2    | SELECT COUNT(*) FROM orders     |                             | 2 rows
3    |                                 | BEGIN;                      | [Order1, Order2]
4    |                                 | INSERT INTO orders VALUES   | [Order1, Order2, 
     |                                 | (Order3, 'Product X', 1);   |  Order3]
5    |                                 | COMMIT;                     | 
6    | SELECT COUNT(*) FROM orders     |                             | 3 rows (Phantom row!)
7    | COMMIT;                         |                             |

Problem: T1 executes the same query twice but gets a different number of rows because T2 inserted a new row that matches T1's query.

Isolation Levels and Their Solutions

READ UNCOMMITTED: No protection (all problems possible)
READ COMMITTED: Solves Dirty Reads
REPEATABLE READ: Solves Dirty Reads and Non-repeatable Reads
SERIALIZABLE: Solves all problems (Dirty Reads, Non-repeatable Reads, and Phantom Reads)

Example: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

📋 Summary

Key Takeaways:

Transactions are essential for maintaining database integrity in multi-user environments
ACID properties ensure reliable transaction processing:
- Atomicity: All-or-nothing execution
- Consistency: Valid state transitions
- Isolation: Independent concurrent execution
- Durability: Permanent changes
Transaction states track the lifecycle from active to committed/aborted
Schedules define execution order - serial (safe) vs concurrent (efficient)
Concurrency control mechanisms are needed to handle concurrent transaction execution

🔮 Next Lecture Preview

Lecture 13: Concurrency Control Protocols

Lock-based protocols
Two-phase locking (2PL)
Deadlock detection and prevention
Timestamp-based protocols

❓ Questions for Discussion

Can you think of a scenario where violating one ACID property might be acceptable for better performance?
How would you design a transaction for a multi-step online shopping checkout process?
What are the trade-offs between serial and concurrent execution of transactions?
How do modern NoSQL databases handle transaction concepts differently?

📚 Additional Resources

Database System Concepts - Silberschatz, Korth, Sudarshan (Chapter 14)
Fundamentals of Database Systems - Elmasri & Navathe (Chapter 21)
Transaction Processing - Gray & Reuter
Research Paper: "ACID Properties Revisited" by H. Haerder