Transaction Integrity

Up until now we’ve covered structured data and how to model it, discussed relational databases (systems built to store structured data), and the language used to query and update that data: SQL. Now we’ll focus on transaction integrity (also called transactional integrity or transaction management). This concept is essential when multiple related updates must be treated as a single logical operation. We’ll use a classic banking example to illustrate. Imagine you have two bank accounts: a savings account and a checking account. The bank reports a total of

70:

20 in savings and $50 in checking. In a relational database, those amounts would typically be stored in two separate rows. If you transfer $10 from savings to checking, that logical transfer requires two separate updates at the database level:

Subtract $10 from the savings account row.
Add $10 to the checking account row.

If both updates succeed, the final balances are

10 in savings and

60 in checking, and the total still equals $70. But because the transfer maps to two update statements, things can go wrong if only one of the updates runs or if another process observes the intermediate state.

A slide titled "Moving Money: Step 1" showing a total amount of 70. It has a before-and-after bar chart where 20 (green) and 50 (purple) on the left become 10 (green) and $60 (purple) on the right, with an arrow between them.

Common failure scenarios:

The second update never happens (we remove $10 from savings but never add it to checking).
A concurrent process inspects the accounts between the two updates and sees an inconsistent intermediate state (for example: $10 in savings and$ 50 in checking), which does not add up to the $70 the bank owes you.

To avoid these issues, databases use four transaction properties—commonly known as ACID—which guarantee correct behavior for multi-step updates.

Atomicity: All updates in the transaction are treated as a single unit. Either every update succeeds, or none are applied.
Consistency: The database moves from one valid state to another valid state. Constraints and business rules are preserved.
Isolation: Concurrent transactions do not see each other’s intermediate states. Observers see either the state before the transaction or the state after it completes.
Durability: Once a transaction commits, its effects survive system failures.

ACID at a glance:

Property	What it ensures	Example effect
Atomicity	Transaction is all-or-nothing	If adding $10 fails after subtraction, both updates are rolled back.
Consistency	Data stays valid before/after commit	Balance constraints (e.g., non-negative) are preserved.
Isolation	No partial states visible to others	Other sessions cannot read the intermediate $10/$ 50 state.
Durability	Committed changes survive crashes	After commit, the new balances persist even if the server restarts.

A presentation slide titled "Transaction Criteria: ACID" showing the four properties—Atomic, Consistent, Isolated, Durable—in colored boxes with brief explanations. It notes atomicity as all updates seen as one step, consistency as data remaining correct, isolation hiding intermediary steps, and durability surviving failures.

In SQL you express a multi-step operation as an explicit transaction so you can commit or roll back as a unit. A simple transfer looks like this:

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 10
WHERE account_type = 'savings' AND account_id = 123;

UPDATE accounts
SET balance = balance + 10
WHERE account_type = 'checking' AND account_id = 456;

-- If both updates succeed:
COMMIT;

-- If an error occurs before commit:
-- ROLLBACK;

With ACID guarantees in place:

If both updates succeed, the database commits the changes (final state: $10 savings,$ 60 checking).
If any step fails, the database rolls everything back to the original consistent state (original: $20 savings,$ 50 checking).

A presentation slide titled "Result of Transaction Management" showing an "All updates succeed" section with listed balances (70 in the bank, 10 in savings, 60 in checking) and a bar chart. Below it is an "All updates fail" section with different balances (70 bank, 20 savings, 50 checking).

Different database systems implement ACID with varying default isolation levels and concurrency mechanisms. Be aware of your DBMS’s settings (for example: READ COMMITTED, REPEATABLE READ, SERIALIZABLE) because they affect visibility, performance, and concurrency.

Practical tips:

Keep transactions short to reduce locking and contention.
Use appropriate isolation levels for your workload: higher isolation reduces anomalies but can decrease concurrency.
Validate constraints inside the transaction (for example, check for sufficient funds) to maintain consistency.
Test failure paths (e.g., simulate errors or crashes) to ensure rollbacks work as expected.

Summary

Transaction integrity ensures multi-step operations behave as single, indivisible actions.
Use transaction control statements (BEGIN, COMMIT, ROLLBACK) and understand ACID to preserve correctness under errors and concurrency.
When designing systems, balance isolation needs against performance and concurrency requirements.

Links and References

Introduction

File-Based Storage

Structured Data

Semi-Structured Data

Cosmos DB

Analyzing Data

Transaction Integrity

Watch Video