Learn
A3.3.6 | TRANSACTIONS AND DATA INTEGRITY (HL ONLY)
01 | ONE REAL ACTION CAN REQUIRE SEVERAL CHANGES
The school moves 200 activity credits from Robotics to Engineering. The database must subtract 200 from one balance and add 200 to the other. Together, those changes represent one intended transfer.
What if the subtraction is saved, then the system fails before the addition? Robotics has lost credits that Engineering never received. Each individual statement might be valid, yet the overall record is wrong.
A transaction groups database work into a logical unit. Its boundaries and completion rules help prevent a partially completed operation from becoming the final recorded outcome.
This HL lesson develops ACID: atomicity, consistency, isolation and durability. For describe, explain what each property means and how it protects the data in a concrete situation.
02 | DEFINE THE RULES BEFORE THE TRANSACTION
| AccountID | PK | ClubName | Balance |
|---|---|---|
| 101 | Robotics | 1000 |
| 202 | Engineering | 300 |
CREATE TABLE ClubAccount (
AccountID INTEGER PRIMARY KEY,
ClubName VARCHAR(60) NOT NULL,
Balance INTEGER NOT NULL CHECK (Balance >= 0)
);Balances are whole activity credits, not currency. The rule for this example is that an internal transfer conserves the combined total: 1000 + 300 = 1300. Each balance must also remain non-negative.
A successful 200-credit transfer produces Robotics 800 and Engineering 500, still totalling 1300. If the transfer cannot complete, the intended outcome is Robotics 1000 and Engineering 300.
The CHECK constraint enforces a per-row non-negative balance. It does not alone enforce that every debit has a corresponding credit. Correct transaction logic must implement the transfer rule as well.
03 | WHY TWO SEPARATE SAVES CAN FAIL
| Event | Robotics | Engineering | Combined total |
|---|---|---|---|
| Original committed state | 1000 | 300 | 1300 |
| Debit saved as a separate completed change | 800 | 300 | 1100 |
| Failure before credit | 800 | 300 | 1100 |
If each UPDATE is independently committed, a failure between them can leave a completed debit without its matching credit. The database cannot infer that two independently completed actions were intended to be one unit.
Grouping the work allows incomplete changes to be rolled back together. That is different from fixing the mistake later with an unrelated correction.
Do not assume every tool uses the same autocommit behaviour. A SQL editor or database library may wrap individual statements or groups differently. Know where the transaction begins and where it actually commits.
04 | TCL MARKS THE TRANSACTION BOUNDARIES
| Command | Purpose | What it does not mean |
|---|---|---|
| BEGIN TRANSACTION | Start an explicit transaction block. | Does not by itself validate the business operation. |
| COMMIT | Finish by making the transaction’s successful changes committed. | Does not mean every written statement was logically correct. |
| ROLLBACK | Abort and undo the current transaction’s uncommitted database changes. | Does not undo every earlier committed transaction. |
These are transaction control language (TCL) commands. The data-changing UPDATE statements are DML inside the boundary.
Syntax varies: PostgreSQL and SQLite accept the illustrated BEGIN TRANSACTION form, while other environments may use START TRANSACTION or API methods. Client libraries can also manage boundaries on your behalf.
After COMMIT or a full ROLLBACK, that transaction has ended. A later ROLLBACK is not a universal undo button for work already committed.
05 | READ THE COMPLETE TRANSFER
BEGIN TRANSACTION; UPDATE ClubAccount SET Balance = Balance - 200 WHERE AccountID = 101 AND Balance >= 200; -- Application must check: exactly one row changed. -- If not, stop and ROLLBACK instead of continuing. UPDATE ClubAccount SET Balance = Balance + 200 WHERE AccountID = 202; -- Application must check: exactly one row changed. -- If not, stop and ROLLBACK instead of continuing. COMMIT;
The first UPDATE targets Robotics only if its balance can cover the amount. The second targets Engineering. The application must verify that each intended row was changed and that no error requires abandonment before it chooses COMMIT.
The comments describe decisions made by surrounding application code; SQL comments do not execute those checks. This is a successful-path illustration, not a complete stand-alone transfer routine. In an application, failure handling must stop further steps and roll back.
A statement that changes zero rows can be valid SQL, not an exception. If the debit finds no eligible account and the application blindly continues, it could credit Engineering without a matching debit. Similarly, a missing destination must cause the debit to be rolled back.
Transactions protect the unit you actually implement. They do not discover every missing requirement or automatically refuse an early COMMIT after only the debit.
06 | ATOMICITY: THE COMPLETE UNIT OR NONE
Atomicity means the transaction’s changes are treated as one all-or-nothing unit. If the full transfer commits, both balance updates become committed. If it aborts, neither update remains as a committed part of that transfer.
The transaction’s own session can see its intermediate work: after the debit, its working values can be 800 and 300. Atomicity does not claim that no intermediate computation ever exists.
If the credit cannot be completed, ROLLBACK removes the pending debit as part of abandoning the transfer. The final committed state returns to 1000 and 300.
Do not confuse this with atomic field values in database normalisation. Here atomicity concerns the indivisible transaction outcome, not whether a column contains one value.
07 | CONSISTENCY: PRESERVE THE DECLARED RULES
Consistency means a correctly designed transaction takes the database from a valid state to another valid state according to its integrity rules. In this transfer, balances remain non-negative and the total remains 1300.
The DBMS enforces declared constraints such as primary keys and CHECK conditions. Correct application logic must enforce additional business requirements, such as selecting the intended destination and requiring both updates to succeed.
A transaction can be atomic but wrong. If a programmer subtracts 200 and adds 150, committing both changes together is all-or-nothing, but it violates the transfer’s conservation rule. Atomicity alone does not supply consistency.
The temporary working total after a debit can differ during execution. What matters for the completed operation is that the intended invariant is preserved at the appropriate transaction boundary. Some constraints are checked immediately and others may be deferred where supported.
08 | ISOLATION: CONTROL CONCURRENT INTERFERENCE
Isolation governs how concurrent transactions interact and which changes they can observe. It helps stop one user’s work from being corrupted by another transaction’s incomplete or conflicting work.
For example, another user should not base a report on a debit that is later rolled back. Seeing an uncommitted change from another transaction is a dirty read. Common isolation configurations prevent it.
The strongest standard level, serializable, requires the effect of successfully committed concurrent transactions to be equivalent to some one-at-a-time ordering. This does not require the DBMS physically to run all transactions one after another.
Lower isolation levels allow particular anomalies. Simply writing BEGIN does not guarantee every concurrency problem disappears. The chosen level, SQL design and conflict handling matter.
09 | DURABILITY: A CONFIRMED COMMIT SURVIVES RECOVERY
Durability means successfully committed changes are retained through failures covered by the DBMS’s durability guarantees. If the transfer is committed and the service then crashes, recovery should retain 800 and 500 rather than forget the transfer.
DBMSs commonly use persistent logs and recovery mechanisms so committed work can be recovered without requiring every changed data page to be written immediately. Exact guarantees depend on configuration, storage and failure assumptions.
Durability is not a promise that every physical disaster is harmless. Backups, replication and tested recovery procedures address broader loss scenarios; they are not replaced by using COMMIT.
Likewise, a screen message from an application is not automatically proof of a confirmed database commit. Applications must handle errors and uncertain outcomes correctly.
10 | FOLLOW THE WORKING AND COMMITTED STATES
Run the transfer one step at a time. The left panel shows committed model data; the right shows the current transaction’s working changes. Try rolling back after the debit, failing the credit with a missing destination, and simulating a crash before or after commit.
The activity deliberately permits COMMIT only after both intended transfer steps, to teach the application rule. A real DBMS can commit a valid partial business operation if the application wrongly tells it to do so.
This is a temporary JavaScript model. “Simulate crash” discards its pending state while retaining its in-memory committed state; it does not test a real storage engine or survive closing this page. The separate panels model no dirty reads, not every isolation level or concurrent execution.
COMMITTED MODEL
TRANSACTION WORKING STATE
11 | ROLLBACK AND ERROR HANDLING WORK TOGETHER
BEGIN TRANSACTION; UPDATE ClubAccount SET Balance = Balance - 200 WHERE AccountID = 101 AND Balance >= 200; -- Suppose the destination is missing. -- Application abandons the transfer: ROLLBACK;
The debit is part of the current transaction, so the full rollback undoes it. No matching destination is credited and neither part of the transfer becomes committed.
Do not memorise “every SQL error automatically rolls back the whole transaction”. Systems differ in whether a particular error aborts the statement, marks the transaction failed or rolls back more work. Applications must handle the actual DBMS state and explicitly abandon the intended unit where required.
For example, PostgreSQL normally requires recovery from an error in a transaction block before useful work can continue, often by full ROLLBACK or an appropriate savepoint rollback. A zero-row result is a separate case that may need an application decision despite no SQL error.
12 | TWO USERS CAN BOTH SEE THE LAST PLACE
| Step | User A | User B |
|---|---|---|
| 1 | Reads Available = 1 | |
| 2 | Reads Available = 1 | |
| 3 | Records a booking and writes Available = 0 | |
| 4 | Uses the earlier value to record another booking and writes Available = 0 |
This conceptual read-modify-write schedule creates two bookings for one place if the system permits it. The final availability is not negative, so a simple non-negative check does not necessarily catch the overbooking.
A lost update can occur when a later write based on an earlier read overwrites another transaction’s change. The exact behaviour depends on the statements and concurrency controls; the table illustrates unsafe logic, not a guarantee that every DBMS will permit this schedule.
Wrapping the same unsafe reasoning in a transaction is not enough at every isolation level. Suitable locking, conditional updates, constraints or serializable execution may be needed.
13 | MAKE THE DECISION PART OF THE PROTECTED CHANGE
UPDATE Session SET Available = Available - 1 WHERE SessionID = 7 AND Available > 0;
Instead of reading a value and later writing a stale absolute result, this statement expresses the condition and decrement together. The application checks that exactly one row was changed before recording a successful reservation in the same transaction.
If no place is available, zero rows are changed and the application must not record a booking. If a later booking insertion fails, roll back the decrement. Correct DBMS concurrency semantics and transaction handling are still required.
Other designs can lock relevant rows or use serializable isolation and retry conflicts. A uniqueness constraint may enforce a specific seat assignment. The right protection depends on the rule being preserved; a general “BEGIN makes it safe” is too vague.
14 | LOCKS AND MULTIVERSION READS
A lock can prevent conflicting changes from proceeding at the same time. Another transaction may wait, fail or be retried rather than overwrite protected work. Longer-held locks can reduce concurrency.
Many DBMSs also use multiversion concurrency control (MVCC), allowing readers to see an appropriate committed snapshot while other work proceeds. That does not mean all conflicts disappear or every database uses the same snapshots.
A deadlock can arise when transactions wait on resources held by each other. The DBMS commonly detects and aborts a participant, so the application needs an appropriate rollback/retry strategy.
Keep transactions as short as their business operation allows. Do not hold one open while waiting for a user to fill in a form if the data can instead be gathered before beginning.
15 | ISOLATION LEVELS ARE NOT IDENTICAL
| Level or idea | Main description |
|---|---|
| Read uncommitted | The standard permits dirty reads at this level; some DBMSs implement stronger behaviour. |
| Read committed | Avoids dirty reads, but separate reads can observe newly committed changes. |
| Repeatable read | Protects repeated reads more strongly; exact permitted anomalies and implementation vary. |
| Serializable | Successfully committed results must be equivalent to a serial ordering; conflicts can require retries. |
You do not need a complete vendor-specific isolation matrix to understand the core property. Recognise that stronger protection can mean more waiting, tracking or aborted attempts, so performance and correctness requirements must be considered together.
A non-repeatable read means rereading a row and seeing a committed change from another transaction. A phantom concerns a changed set of matching rows on a repeated query. These are different from dirty reads of uncommitted data.
Do not assume a virtual view bypasses isolation: its query follows the same visibility rules as its underlying data access.
16 | FAILURE BEFORE COMMIT VERSUS AFTER COMMIT
| Failure point | Intended recovery outcome in the example |
|---|---|
| Before the transfer starts | Original 1000 and 300 remain. |
| After debit but before successful completion | Uncommitted transfer is abandoned; original balances remain. |
| After confirmed durable commit | The committed 800 and 500 are retained through covered recovery. |
Atomicity handles an incomplete unit; durability protects a successfully committed unit. Both involve recovery, but they answer different questions.
If the connection fails while the application is waiting for the commit response, the outcome may be uncertain from the application’s perspective: the server may have committed even though the reply was lost.
Blindly retrying the transfer can apply it twice. Real applications may use a unique operation identifier and an appropriate idempotent recovery design to determine whether it already happened. This extension reinforces that database guarantees and application communication are related but separate.
17 | INTEGRITY NEEDS MORE THAN ONE TOOL
Use constraints for rules the schema can enforce, appropriate permissions for who may change data, correct DML for what should change and transactions for the unit that must succeed together. Choose concurrency protection that preserves the rule under simultaneous use.
A transaction cannot send back an email already delivered or undo an unrelated external payment simply by rolling back database rows. External side effects need a coordinated application design.
Transactions also do not replace backups, input validation or testing. Describe them as part of a complete integrity design rather than a magic command that makes every program correct.
18 | CONNECT EACH ACID PROPERTY TO EVIDENCE
| Property | Evidence from the transfer |
|---|---|
| Atomicity | Both debit and credit commit, or the transaction’s changes are abandoned. |
| Consistency | The correctly implemented transfer preserves non-negative balances and total 1300. |
| Isolation | Concurrent work is controlled according to the chosen guarantees, avoiding prohibited interference. |
| Durability | A confirmed committed transfer remains recoverable after covered failure. |
Use the mechanism and consequence in a sentence: “ROLLBACK removes the pending debit, therefore no half-transfer remains committed.” Or: “The DBMS records committed work durably, therefore recovery retains the completed transfer.”
Avoid definitions that collapse all four properties into “data is safe”. Give the particular failure or rule each one addresses.
19 | CHECK THE PROPERTY AND COMMAND
Choose the best property or command for each situation. The first checked response counts; restart for another attempt.
20 | DESCRIBE A RELIABLE TRANSACTION
Identify the complete business action and its validity rules. Describe BEGIN, the related statements and their checks, then the COMMIT success path and ROLLBACK failure path. Link each ACID property to the example.
For concurrent work, state the intended isolation or protection rather than claiming every transaction automatically behaves as serializable. For recovery, distinguish incomplete work from confirmed committed work.
A strong answer says what remains in the database after each outcome. For our transfer, that is either 1000 and 300 after abandonment, or 800 and 500 after successful completion. Neither outcome is a half-transfer.
References: transaction boundaries and rollback and isolation levels and visibility.