A3.3.6 Describe how transactions maintain data integrity in a database. (HL only)

📚 You can find additional information related to these learning standards in the course companion pages 211 to 214.

The Big Idea

Databases are not just about storing and retrieving data—they must also preserve accuracy, correctness, and reliability at all times. In real-world systems, multiple operations must often be treated as a single unit—especially in banking, inventory systems, or enterprise applications. This is where transactions come in.

A transaction is a sequence of one or more SQL operations (e.g., INSERT, UPDATE, DELETE) that are executed as a single logical unit of work. Transactions help maintain data integrity, meaning the database remains valid, accurate, and consistent, even in the event of power failure, concurrent access, or system crashes.

To enforce these guarantees, relational databases follow the ACID model, and offer Transaction Control Language (TCL) commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK.

---

What Is a Transaction?

A transaction is a group of SQL operations that must either:

• All succeed together, or
• All fail together (and leave the database unchanged).

Key point: A transaction groups operations into a single, atomic unit. You don’t want only half of a bank transfer to complete.

Example Transaction – Bank Transfer

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A123';  -- Debit
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B456';  -- Credit
COMMIT;

If the second UPDATE fails, the first must not be saved. Otherwise, money disappears.

---

Data Integrity

Data integrity means the data in the database is:

• Accurate (reflects the real-world situation),
• Consistent (does not violate rules or constraints),
• Complete (all required parts of a transaction are applied),
• Valid (all data adheres to constraints like types, keys, and references).

Transactions preserve data integrity by ensuring changes are not partial, and do not leave the database in an invalid state.

---

The ACID Properties

The ACID model defines the four critical properties that every transaction must satisfy:

1. Atomicity

• The transaction is all or nothing.
• If any operation in the transaction fails, the entire transaction is rolled back.
• Prevents partial updates.

Analogy: Either you send the email and the attachment, or you send nothing at all.

---

2. Consistency

• The database starts and ends in a valid state, according to all rules (constraints, foreign keys, triggers).
• Every transaction must preserve the integrity rules of the schema.

Example: If a row is inserted into an enrollments table, it must reference a valid student ID and course ID.

---

3. Isolation

• Concurrent transactions do not interfere with each other.
• The result of running transactions concurrently is the same as if they were run sequentially.

Techniques:

• Locks: Prevent access to data while it's being changed.
• Isolation levels: Control phenomena like dirty reads, non-repeatable reads, and phantom reads.

---

4. Durability

• Once a transaction is committed, the changes are permanent, even in the event of system failure.
• Ensured through write-ahead logs or redo logs on disk.

Example: After you get a "payment successful" message, the transaction must be saved permanently—even if the power cuts out 10 seconds later.

---

Transaction Control Language (TCL) Commands

These are used to manage transactions explicitly:

BEGIN TRANSACTION

Marks the start of a transaction block.

BEGIN TRANSACTION;

COMMIT

Saves all operations in the transaction permanently to the database.

COMMIT;

ROLLBACK

Cancels the transaction and reverts all changes since BEGIN TRANSACTION.

ROLLBACK;

Example – Safe Insert with ROLLBACK

BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id) VALUES (101, 'C123');
INSERT INTO payments (order_id, amount) VALUES (101, 0);  -- Bad data: amount can’t be zero
ROLLBACK;  -- No changes saved

---

Real-World Example: E-Commerce Order Placement

A customer places an order. This involves:

• Deducting inventory,
• Inserting an order record,
• Logging a payment,
• Updating loyalty points.

All of these operations must succeed or fail together:

BEGIN TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 'P001';
INSERT INTO orders (order_id, customer_id) VALUES (...);
INSERT INTO payments (...) VALUES (...);
UPDATE customers SET loyalty_points = loyalty_points + 100 WHERE customer_id = 'C9001';
COMMIT;

If any UPDATE or INSERT fails, the whole block should be rolled back to avoid inconsistency.

---

Summary

Concept	Explanation
Transaction	A unit of work that executes a series of SQL operations together
Data Integrity	Ensures the database remains accurate, valid, and reliable
ACID	Atomicity, Consistency, Isolation, Durability—principles for safe transaction processing
TCL Commands	BEGIN TRANSACTION, COMMIT, ROLLBACK

---

Conclusion

Transactions are essential for maintaining data integrity in any serious database system. They protect against partial updates, concurrent conflicts, and system crashes. ACID properties provide a formal guarantee that data will remain correct, and TCL commands give developers the tools to control and recover from complex data operations.

For HL students, this topic forms a cornerstone of database reliability, especially when designing systems where accuracy and safety are critical—such as banking, inventory, or any multi-user transactional system.