IB COMPUTER SCIENCE | A3.3.6 Transactions and Data Integrity (HL ONLY)

Lesson objective

Describe how atomicity, consistency, isolation and durability maintain transaction integrity, using BEGIN TRANSACTION, COMMIT and ROLLBACK.

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 | PKClubNameBalance
101Robotics1000
202Engineering300
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

EventRoboticsEngineeringCombined total
Original committed state10003001300
Debit saved as a separate completed change8003001100
Failure before credit8003001100

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

CommandPurposeWhat it does not mean
BEGIN TRANSACTIONStart an explicit transaction block.Does not by itself validate the business operation.
COMMITFinish by making the transaction’s successful changes committed.Does not mean every written statement was logically correct.
ROLLBACKAbort 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

    StepUser AUser B
    1Reads Available = 1
    2Reads Available = 1
    3Records a booking and writes Available = 0
    4Uses 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 ideaMain description
    Read uncommittedThe standard permits dirty reads at this level; some DBMSs implement stronger behaviour.
    Read committedAvoids dirty reads, but separate reads can observe newly committed changes.
    Repeatable readProtects repeated reads more strongly; exact permitted anomalies and implementation vary.
    SerializableSuccessfully 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 pointIntended recovery outcome in the example
    Before the transfer startsOriginal 1000 and 300 remain.
    After debit but before successful completionUncommitted transfer is abandoned; original balances remain.
    After confirmed durable commitThe 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

    PropertyEvidence from the transfer
    AtomicityBoth debit and credit commit, or the transaction’s changes are abandoned.
    ConsistencyThe correctly implemented transfer preserves non-negative balances and total 1300.
    IsolationConcurrent work is controlled according to the chosen guarantees, avoiding prohibited interference.
    DurabilityA 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.

    MATCH THE TRANSACTION CONCEPT TO ITS ROLE

    Terminology

    Terminology

    Transaction

    A logical unit of database work controlled by a transaction boundary.

    ACID

    Atomicity, consistency, isolation and durability.

    Atomicity

    The transaction’s changes have an all-or-nothing committed outcome.

    Consistency

    Correct transactions preserve the database’s defined integrity rules between valid states.

    Isolation

    Controls interactions and visibility among concurrent transactions.

    Durability

    Confirmed committed changes remain recoverable through failures covered by the DBMS guarantees.

    TCL

    Transaction control language, including BEGIN TRANSACTION, COMMIT and ROLLBACK.

    BEGIN TRANSACTION

    Starts an explicit transaction block in the demonstrated syntax.

    COMMIT

    Completes a successful transaction by making its changes committed.

    ROLLBACK

    Abandons the current transaction’s uncommitted changes.

    Autocommit

    A mode commonly committing each successful statement as its own transaction, subject to the client and DBMS.

    Committed state

    Data changes successfully completed by a transaction and governed by its visibility rules.

    Uncommitted change

    Work performed inside an unfinished transaction.

    Invariant

    A rule that the completed operation must preserve, such as a conserved total.

    Constraint

    A database-enforced rule such as a primary key or CHECK condition.

    Affected-row count

    Information about how many rows a statement changed or targeted, interpreted under the driver/DBMS semantics.

    Concurrency

    Multiple operations making progress over overlapping periods.

    Dirty read

    Reading another transaction’s uncommitted change.

    Lost update

    A later write based on an earlier value overwrites another change.

    Non-repeatable read

    A repeated read of a row observes a newly committed change.

    Phantom read

    A repeated query observes a changed set of matching rows due to concurrent committed work.

    Serializable

    Isolation requiring successfully committed effects equivalent to some serial transaction ordering.

    Lock

    A mechanism restricting conflicting access to protected data or resources.

    MVCC

    Multiversion concurrency control, using versions to support visibility and concurrency rules.

    Deadlock

    A cycle of transactions waiting on resources held by one another.

    Retry

    Another attempt after a failed or conflicting operation, requiring correct handling of prior outcomes.

    Recovery log

    Persistent information a DBMS can use to recover transactional state.

    Savepoint

    A marker supporting partial rollback within a transaction where supported.

    Idempotent operation

    An operation designed so repeating the same logical request does not apply its effect again.

    Backup

    A recoverable copy supporting restoration; transactions do not replace it.

    Questions

    Questions

    CHECK YOUR UNDERSTANDING

    Select all correct choices. Each exact set earns one point.

    1. Which form the intended transfer unit?
    2. Which describe atomicity?
    3. Which support consistency here?
    4. Which describe TCL?
    5. Which checks matter in the example?
    6. Which describe isolation?
    7. Which describe durability?
    8. Which describe failure handling?
    9. Which help protect a last-place booking?
    10. Which are accurate limits?

    EXPLAIN AND COMPARE

    Write your answers before revealing each sample.

    1. Describe a transaction using the club transfer.

    2. Describe atomicity if the destination cannot be credited.

    3. Describe consistency and its dependency on correct logic.

    4. Describe isolation in a simultaneous booking example.

    5. Describe durability after a successful 200-credit transfer.

    6. Describe BEGIN TRANSACTION, COMMIT and ROLLBACK.

    7. Explain why the application must check the debit’s affected-row count.

    8. Describe the difference between atomicity and consistency.

    9. Describe how autocommit can break the intended transfer boundary.

    10. Describe why transaction boundaries alone may not prevent overbooking.

    11. Describe failure before and after confirmed commit.

    12. Describe two limits of transactions.

    DESCRIBE IT | A TRANSFER AND A LAST-PLACE BOOKING

    Describe the success and failure paths for the 200-credit transfer, including affected-row checks and final balances. Link all four ACID properties to the operation. Then describe why simultaneous bookings for one remaining place require suitable concurrency protection as well as transaction boundaries.

    Flashcards

    Flashcards

    Click to flip. Select the ideas you need to revisit.

    0 cards selected for revision.

      Selections are kept while this page is open.

      Workbook

      Workbook

      COMING SOON

      The workbook for A3.3.6 Transactions and Data Integrity (HL ONLY) is coming soon. Complete the matching task and written questions in your exercise book.