Database Transactions

1. What is a Transaction?

A database transaction is a group of operations that must be treated as a single logical unit of work.

Either:

• All operations succeed (COMMIT), or
• Nothing happens (ROLLBACK)

Example bank transfer: \ Money is removed from account A and added to account B.
If only one step happens, money is created or lost → unacceptable.

So the database guarantees consistency even if errors, crashes, or concurrent users occur.

---

2. Why Transactions Exist (Use‑Cases)

Transactions protect data from:

Problem	Example
Partial updates	Power outage during payment
Concurrent users	Two customers buying the last item
Software bugs	Exception halfway through saving
Server crashes	Application stops mid-operation

Typical use‑cases:

• Online payments
• Inventory systems
• Booking systems (seats, hotel rooms)
• Banking - Multiplayer games (shared state)
• Any multi‑step business logic

---

3. Transactions and the ACID Properties

Transactions implement the famous ACID guarantees:

A - Atomicity

All or nothing.

Withdraw 100
Deposit 100

Cannot end with only one step executed.

C - Consistency

Database rules must remain valid.

Examples:

• Foreign keys remain valid
• Account balance cannot be negative (if rule defined)
• Stock cannot drop below 0

I - Isolation

Transactions behave as if they run alone - even when many users run them simultaneously.

Different isolation levels trade correctness vs performance.

D - Durability

Once committed, data survives crashes (disk persistence / logs).

---

4. Transaction Syntax (MySQL)

Basic Structure

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
COMMIT;

If something goes wrong:

ROLLBACK;

---

With Error Handling

START TRANSACTION;
 
UPDATE products
SET stock = stock - 1
WHERE id = 10;
 
-- Ensure stock never negative
SELECT stock INTO @currentStock FROM products WHERE id = 10;
 
IF @currentStock < 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

---

Automatic Rollback Example

START TRANSACTION;
 
INSERT INTO orders(customer_id) VALUES (5);
INSERT INTO order_items(order_id, product_id) VALUES (LAST_INSERT_ID(), 99);
 
-- Suppose product does not exist -> error
-- MySQL aborts and we manually rollback
 
COMMIT;

---

5. Isolation Levels

Different levels prevent different concurrency problems:

Level	Prevents
READ UNCOMMITTED	Nothing
READ COMMITTED	Dirty reads
REAPEATABLE READ (MySQL default)	Dirty + non-repeatable reads
SERIALIZABLE	All anomalies (slowest)
Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM seats WHERE reserved = false;

---

6. Transactions in Backend Applications (C# + Entity Framework)

In real applications, the backend server controls transactions - not the user.

Automatic Transaction (SaveChanges)

using var db = new ShopDbContext();
 
order.Status = "Paid";
product.Stock--;
 
await db.SaveChangesAsync();

Entity Framework automatically wraps this in a transaction.

---

Manual Transaction

using var db = new ShopDbContext();
using var transaction = await db.Database.BeginTransactionAsync();
 
try
{
    order.Status = "Paid";
    product.Stock--;
 
    await db.SaveChangesAsync();
 
    payment.Log();
    await db.SaveChangesAsync();
 
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
}

Why manual transactions?

When multiple SaveChanges or external operations must succeed together:

• Payment API + database update
• Creating multiple related objects
• Complex business rules

Rule of thumb: Don’t use explicit/manual transactions unless you are sure you need them.

---

7. Concurrency: Multiple Users at Once

Two users buy the last item:

Without transaction:

1. Both read stock = 1
2. Both subtract 1
3. Result = -1 ❌

With transaction + locking: Only one succeeds, the other retries.

Databases internally use:

• Row locks
• MVCC (multi‑version concurrency control)

---

8. Distributed Transactions

A distributed transaction spans multiple systems:

Example:

• SQL database
• Payment provider
• Message queue

Classic solution: Two‑Phase Commit (2PC)

Problem

Distributed transactions are:

• Slow
• Fragile
• Block resources
• Difficult to scale in microservices

If one system hangs → whole system waits.

---

9. Modern Alternative - SAGA Pattern

See SAGA Pattern (covered in SYTD5)

---

10. Summary

Transactions ensure reliable data handling.

They:

• Protect against crashes
• Coordinate concurrent users
• Guarantee correctness

ACID gives formal guarantees.