Transactions and ACID

A transaction is a group of database operations that the database treats as one unit of work: either all of them take effect, or none of them do. Transactions are what let a bank move money, a shop reserve stock and an airline sell a seat without corrupting data when many users act at once or the server crashes halfway. Interviewers use this topic to check two things. First, can you explain ACID in your own words with a concrete example, and say how a database actually delivers each property? Second, can you reason about schedules (interleavings of operations from several transactions): draw a precedence graph, decide whether a schedule is serializable, and say whether it is recoverable. This lesson covers both, step by step, with every example checked.

What a transaction is

Think of a transfer of 1,000 rupees from Asha's account to Ravi's account. In SQL it is two statements:

UPDATE accounts SET balance = balance - 1000 WHERE id = 1; -- Asha
UPDATE accounts SET balance = balance + 1000 WHERE id = 2; -- Ravi

If the server crashes after the first statement and before the second, 1,000 rupees has vanished. If another session reads both balances between the two statements, it sees a total that is 1,000 short. A transaction wraps both statements so that neither problem can happen:

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

• BEGIN (or START TRANSACTION) opens the transaction.
• COMMIT makes every change permanent and visible to others.
• ROLLBACK throws every change away, as if the transaction never ran.

At the level the theory works with, a transaction is just a sequence of reads and writes of data items, ending in either commit or abort. We write R1(A) for "transaction T1 reads item A" and W1(A) for "T1 writes item A". A data item can be a row, a page or a whole table; for the theory the size does not matter.

ACID, explained with a bank transfer

ACID is an acronym for the four guarantees a transactional database gives you. For each one, you need the what (the promise) and the how (the mechanism that keeps it). Interviewers almost always ask the second half.

We will use this table throughout. It runs as shown in SQLite, PostgreSQL and MySQL:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  owner   TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 5000), (2, 'Ravi', 2000);

Atomicity: all or nothing

The promise. Either every operation in the transaction is applied, or none is. There is no state where the debit happened but the credit did not.

Example. Asha's 5,000 becomes 4,000; the server loses power before Ravi's row is updated. After restart, the database must show Asha with 5,000 and Ravi with 2,000, because the transaction never committed.

How the DBMS does it. Before changing a data page, the database writes a log record describing the change, including the old value (the "before image"). If the transaction aborts, or the system crashes before the commit record is safely on disk, the recovery manager reads the log backwards and puts the old values back. This is called undo. PostgreSQL achieves the same effect differently: it never overwrites rows in place, so an uncommitted transaction's new row versions are simply ignored because the transaction is marked as aborted. The recovery lesson covers logging in detail.

Consistency: rules hold before and after

The promise. A transaction takes the database from one valid state to another valid state. "Valid" means every declared rule (primary keys, foreign keys, CHECK constraints, NOT NULL, unique indexes, triggers) holds, and so do the application's own invariants, such as "money is neither created nor destroyed by a transfer".

Example. Asha tries to send 9,000 rupees but only has 4,000. The CHECK (balance >= 0) constraint rejects the update and the transaction is rolled back. Running this in SQLite gives:

sqlite> BEGIN;
sqlite> UPDATE accounts SET balance = balance - 9000 WHERE id = 1;
Runtime error: CHECK constraint failed: balance >= 0 (19)
sqlite> ROLLBACK;

How the DBMS does it. The database checks declared constraints on every write (or at commit, for constraints declared DEFERRABLE INITIALLY DEFERRED in PostgreSQL). It cannot check rules it was never told about: if your code debits Asha but forgets to credit Ravi, the database will happily commit an inconsistent but constraint-valid state. So consistency is a shared responsibility. The DBMS enforces declared constraints and provides atomicity and isolation; the application must write correct transactions.

Isolation: concurrent transactions do not see each other's half-done work

The promise. Even though many transactions run at the same time, each one behaves as if it ran alone. The strongest form, serializability, guarantees that the result equals some one-at-a-time (serial) order of the transactions.

Example. While the transfer is running, an auditor runs SELECT SUM(balance) FROM accounts. Without isolation the auditor might read Asha after the debit and Ravi before the credit and report 6,000 instead of 7,000. With isolation, the auditor sees either the state before the transfer or after it, both of which total 7,000.

How the DBMS does it. With a concurrency-control protocol. The two families are:

• Locking (for example two-phase locking): a transaction must hold a lock on an item before using it; conflicting transactions wait.
• Multi-version concurrency control (MVCC): writers create new versions of a row; each reader sees a consistent snapshot of committed versions, so readers do not block writers.

Real systems let you trade isolation for speed with isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable). All of this is the subject of the concurrency control lesson.

Durability: committed means permanent

The promise. Once the database tells you "commit succeeded", the change survives crashes, power cuts and restarts.

Example. The transfer commits, the application shows "Payment successful", and one millisecond later the server loses power. After restart, Asha must still have 4,000 and Ravi 3,000.

How the DBMS does it. With write-ahead logging (WAL): the log records for the transaction, ending with a commit record, are forced to stable storage (with fsync) before the commit is acknowledged. The changed data pages themselves can be written later. After a crash, recovery replays (redo) the log to rebuild any committed changes that had not reached the data files. For protection against losing the disk itself, databases add replication and backups.

Summary table

Transaction states

A transaction moves through a small state machine:

                 last statement            commit record
  +--------+       has run      +-----------+  on disk   +-----------+
  | Active | -----------------> | Partially | ---------> | Committed |
  +--------+                    | committed |            +-----------+
      |                         +-----------+                  |
      | error, deadlock,              |                        |
      | or ROLLBACK                   | crash before           |
      v                               | commit is durable      |
  +--------+ <------------------------+                        |
  | Failed |                                                   |
  +--------+                                                   |
      | effects undone                                         |
      v                                                        v
  +---------+                                          +------------+
  | Aborted | ---------------------------------------> | Terminated |
  +---------+         (after restart or kill)          +------------+

• Active: the transaction is running its reads and writes.
• Partially committed: the last statement has run, but the commit is not yet durable. Changes may still sit only in memory buffers.
• Committed: the commit log record is on stable storage. From now on the changes are permanent; they can only be reversed by a new, separate transaction (a compensating transaction, such as a refund).
• Failed: something went wrong (a constraint violation, a deadlock, a crash, or an explicit ROLLBACK). Normal execution cannot continue.
• Aborted: the database has undone all of the transaction's effects. The system can then restart the transaction (sensible after a deadlock, which is a transient problem) or kill it (sensible after a logic error, which will fail again).
• Terminated: either committed or aborted; the transaction is finished.

A common follow-up question: "Can a partially committed transaction still fail?" Yes. If the server crashes before the commit record is flushed, recovery treats the transaction as never committed and undoes it.

Schedules

When several transactions run at once, their operations interleave. A schedule is the order in which the operations of a set of transactions are actually executed, keeping each transaction's own operations in their original order.

Serial schedules

A serial schedule runs one transaction completely, then the next, with no interleaving. With two transactions T1 and T2 there are exactly two serial schedules: T1 then T2, or T2 then T1. With n transactions there are n! serial schedules.

Serial schedules are always correct (if each transaction is correct on its own) but slow: while T1 waits for a disk read, the CPU sits idle and every other user waits.

Concurrent (non-serial) schedules

A concurrent schedule interleaves operations. It gives better throughput and response time, but some interleavings produce wrong answers. Here is the classic one. T1 transfers 100 from A to B; T2 adds 10% interest to A. Initially A = 1000, B = 2000.

   time   T1                      T2
   ----   ---------------------   ---------------------
    1     R1(A)   A = 1000
    2                             R2(A)   A = 1000
    3     A := A - 100
    4     W1(A)   A = 900
    5                             A := A * 1.1
    6                             W2(A)   A = 1100
    7     R1(B)   B = 2000
    8     B := B + 100
    9     W1(B)   B = 2100

Final state: A = 1100, B = 2100, total 3200. Compare with the two serial orders:

• T1 then T2: A = (1000 - 100) x 1.1 = 990, B = 2100, total 3090.
• T2 then T1: A = 1000 x 1.1 - 100 = 1000, B = 2100, total 3100.

The interleaved result matches neither. T1's write to A was overwritten (a lost update). The goal of concurrency control is to allow only interleavings whose result matches some serial schedule. Such schedules are called serializable.

Conflicts and conflict serializability

Checking "does the result match a serial schedule?" by computing values is impractical, because it depends on what the transactions compute. Instead, databases use a structural test based on conflicts.

Conflicting operations

Two operations conflict if all three hold:

1. They belong to different transactions.
2. They access the same data item.
3. At least one of them is a write.

Non-conflicting adjacent operations can be swapped without changing anything that any transaction sees or the final database state.

Conflict equivalence and conflict serializability

Two schedules are conflict equivalent if they contain the same operations and every pair of conflicting operations appears in the same order in both. A schedule is conflict serializable if it is conflict equivalent to some serial schedule. Intuitively: you can turn it into a serial schedule by repeatedly swapping adjacent non-conflicting operations.

The precedence graph test

Swapping by hand is slow. The standard test builds a precedence graph (also called a serialization graph):

1. Draw one node per transaction.
2. For each pair of conflicting operations where Ti's operation comes first and Tj's comes later, draw an edge Ti → Tj ("Ti must come before Tj").
3. If the graph has no cycle, the schedule is conflict serializable. Any topological order of the graph (an ordering where every edge points forward) is an equivalent serial schedule.
4. If there is a cycle, the schedule is not conflict serializable.

Why does this work? An edge Ti → Tj says that in any equivalent serial schedule Ti must come before Tj. A cycle T1 → T2 → T1 demands that T1 come both before and after T2, which no serial order can satisfy. With no cycle, a topological sort gives an order that respects every conflict.

Worked example 1: a serializable interleaving

S1: R1(A) W1(A) R2(A) W2(A) R1(B) W1(B) R2(B) W2(B)

T1 and T2 alternate: T1 works on A, then T2 works on A, then T1 on B, then T2 on B.

Step 1. List the conflicts on A. Operations on A in order: R1(A) W1(A) R2(A) W2(A).

• R1(A) before W2(A): edge T1 → T2.
• W1(A) before R2(A): edge T1 → T2.
• W1(A) before W2(A): edge T1 → T2.

Step 2. Conflicts on B. Order: R1(B) W1(B) R2(B) W2(B). The same pattern gives only T1 → T2 edges.

Step 3. The graph:

   +----+        +----+
   | T1 | -----> | T2 |
   +----+        +----+

No cycle. S1 is conflict serializable and equivalent to the serial schedule T1, T2. This is exactly the pattern a database wants to allow: it overlaps T2's work on A with T1's work on B, but the result is the same as running T1 first.

Worked example 2: a two-transaction cycle

S2: R1(A) R2(A) W1(A) W2(A)

• R1(A) before W2(A): T1 → T2.
• R2(A) before W1(A): T2 → T1.
• W1(A) before W2(A): T1 → T2.

   +----+ -----> +----+
   | T1 |        | T2 |
   +----+ <----- +----+

There is a cycle T1 → T2 → T1, so S2 is not conflict serializable. This is the lost-update pattern: both read the old A, both write, and one write is lost.

Worked example 3: swapping the order on B

S3: R1(A) W1(A) R2(A) W2(A) R2(B) W2(B) R1(B) W1(B)

On A, T1 comes first: edges T1 → T2. On B, T2 comes first (W2(B) before R1(B), R2(B) before W1(B), W2(B) before W1(B)): edges T2 → T1. Cycle, so not conflict serializable. In terms of the transfer: T2 sees T1's new A but the old B, so it observes a state that never existed in any serial run.

Worked example 4: three transactions, a chain

S4: R2(A) R1(B) W2(A) R3(A) W1(B) W3(A) R2(B) W2(B)

Group the operations by item.

On A: R2(A) W2(A) R3(A) W3(A).

• R2(A) before W3(A): T2 → T3.
• W2(A) before R3(A): T2 → T3.
• W2(A) before W3(A): T2 → T3.

On B: R1(B) W1(B) R2(B) W2(B).

• R1(B) before W2(B): T1 → T2.
• W1(B) before R2(B): T1 → T2.
• W1(B) before W2(B): T1 → T2.

   +----+      +----+      +----+
   | T1 | ---> | T2 | ---> | T3 |
   +----+      +----+      +----+

No cycle. The only topological order is T1, T2, T3, so S4 is conflict equivalent to the serial schedule T1, T2, T3. Notice that in S4 the very first operation belongs to T2, yet T2 is second in the equivalent serial order. The serial order comes from the conflicts, not from who started first.

Worked example 5: three transactions, three items

This one is a favourite in exams because the answer order looks surprising.

S5: R1(X) R2(Z) R1(Z) R3(X) R3(Y) W1(X) W3(Y) R2(Y) W2(Z) W2(Y)

On X: R1(X) R3(X) W1(X).

• R1(X) and R3(X): both reads, no conflict.
• R3(X) before W1(X): T3 → T1.

On Y: R3(Y) W3(Y) R2(Y) W2(Y).

• R3(Y) before W2(Y): T3 → T2.
• W3(Y) before R2(Y): T3 → T2.
• W3(Y) before W2(Y): T3 → T2.

On Z: R2(Z) R1(Z) W2(Z).

• R2(Z) and R1(Z): no conflict.
• R1(Z) before W2(Z): T1 → T2.

            +----+
            | T3 |
            +----+
           /      \
          v        v
      +----+      +----+
      | T1 | ---> | T2 |
      +----+      +----+

Edges: T3 → T1, T3 → T2, T1 → T2. No cycle. Topological sort: T3 has no incoming edges, so it goes first; then T1; then T2. S5 is conflict serializable and equivalent to T3, T1, T2, even though T1 issued the first operation.

View serializability

Conflict serializability is a sufficient test, not a necessary one: some schedules are correct but fail it. A weaker (more permissive) notion is view serializability.

Two schedules S and S' over the same transactions are view equivalent if, for every data item Q:

1. Initial read. If Ti reads the initial value of Q in S (no one wrote Q before), Ti also reads the initial value of Q in S'.
2. Reads-from. If Ti reads a value of Q written by Tj in S, Ti reads the value of Q written by Tj in S'.
3. Final write. The transaction that performs the last write of Q in S also performs the last write of Q in S'.

In plain words: every transaction reads the same values in both schedules, and the database ends in the same state. A schedule is view serializable if it is view equivalent to some serial schedule.

Worked example 6: view serializable but not conflict serializable

S6: R1(A) W2(A) W1(A) W3(A)

Precedence graph:

• R1(A) before W2(A): T1 → T2.
• W2(A) before W1(A): T2 → T1.
• R1(A), W1(A) before W3(A), and W2(A) before W3(A): T1 → T3, T2 → T3.

T1 → T2 → T1 is a cycle, so S6 is not conflict serializable.

Now check view equivalence with the serial schedule T1, T2, T3 (which is R1(A) W1(A) W2(A) W3(A)):

So S6 is view serializable. The writes by T1 and T2 are blind writes (writes of an item the transaction did not read first) and are overwritten by T3 anyway, so their order does not matter.

Relationship between the two

 +------------------------------------------------+
 | All schedules                                  |
 |  +------------------------------------------+  |
 |  | View serializable                        |  |
 |  |  +------------------------------------+  |  |
 |  |  | Conflict serializable              |  |  |
 |  |  |  +------------------------------+  |  |  |
 |  |  |  | Serial                       |  |  |  |
 |  |  |  +------------------------------+  |  |  |
 |  |  +------------------------------------+  |  |
 |  +------------------------------------------+  |
 +------------------------------------------------+

• Every conflict-serializable schedule is view serializable.
• A view-serializable schedule that is not conflict serializable always contains a blind write.
• Testing view serializability is NP-complete, while the precedence-graph test is fast (linear in the size of the graph). That is why real systems enforce conflict serializability (through locking or serialization-graph checks) rather than view serializability.

Recoverability

Serializability talks about schedules where every transaction commits. Real transactions also abort, and an abort can leave other transactions in trouble if they read data the aborted one wrote. Recoverability classifies schedules by how they cope with aborts. In the examples, C1 means T1 commits and A1 means T1 aborts.

A dirty read is reading a value written by a transaction that has not committed yet.

Non-recoverable schedule

S7: W1(A) R2(A) C2 A1

T2 reads T1's uncommitted A and commits. Then T1 aborts, so the value T2 read never officially existed. T2 should be rolled back too, but it has already committed, and committed means permanent. The database cannot fix this. Such a schedule is non-recoverable and must never be allowed.

Recoverable schedule

A schedule is recoverable if, whenever Tj reads a value written by Ti, Ti commits before Tj commits.

S8: W1(A) R2(A) C1 C2

T2 read from T1, and T1 committed first. If T1 had aborted instead, T2 would still be uncommitted and could be aborted as well. S8 is recoverable.

Cascading rollback

Recoverable schedules can still be expensive:

S9: W1(A) R2(A) W2(B) R3(B) A1

T2 read T1's dirty A; T3 read T2's dirty B. When T1 aborts, T2 must abort (it used T1's value), and then T3 must abort (it used T2's value). One failure triggers a chain of rollbacks, called a cascading rollback (or cascading abort). It wastes work and is hard to bound.

Cascadeless schedule

A schedule is cascadeless (avoids cascading aborts) if every transaction reads only values written by committed transactions. In other words: no dirty reads.

S10: W1(A) C1 R2(A) C2

T2 reads A only after T1 commits. Every cascadeless schedule is also recoverable.

Strict schedule

A schedule is strict if no transaction reads or writes an item until the last transaction that wrote it has committed or aborted.

S11: W1(A) W2(A) C1 C2      cascadeless, but NOT strict
S12: W1(A) C1 W2(A) C2      strict

S11 has no dirty reads, so it is cascadeless. But T2 overwrites T1's uncommitted A. If T1 now aborts, undo would restore A to the value before T1, wiping out T2's write. Strictness forbids this, which makes undo simple: to abort a transaction, just put back the before-images of its writes.

Strict schedules are what strict two-phase locking produces, which is why most lock-based databases use it.

Hierarchy and summary

  Strict  ⊂  Cascadeless  ⊂  Recoverable  ⊂  All schedules

Commit, rollback and savepoints in SQL

The basic commands

Savepoints

A savepoint is a named marker inside a transaction. ROLLBACK TO SAVEPOINT name undoes only the work done after the marker; the transaction stays open, and everything before the marker is kept. RELEASE SAVEPOINT removes the marker (it does not commit anything; the outer COMMIT still decides).

This runs as shown in SQLite and PostgreSQL:

BEGIN;
INSERT INTO accounts VALUES (3, 'Meera', 100);
SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 500 WHERE id = 3;
-- the bonus rule turns out to be wrong: undo only the bonus
ROLLBACK TO SAVEPOINT before_bonus;
RELEASE SAVEPOINT before_bonus;
COMMIT;

SELECT * FROM accounts WHERE id = 3;   -- 3 | Meera | 100

Meera's account exists with 100: the insert survived, the bonus did not.

Typical uses:

• Processing a batch where one bad row should be skipped, not fail the whole batch.
• ORMs implementing "nested transactions": an inner transaction() block becomes a savepoint inside the outer transaction.
• In PostgreSQL, recovering from an error inside a transaction. After any error, PostgreSQL puts the transaction into an aborted state and rejects further statements ("current transaction is aborted, commands ignored until end of transaction block") until you ROLLBACK or roll back to a savepoint set before the error.

Implicit commits and DDL

Whether schema changes (CREATE, ALTER, DROP) can be rolled back depends on the database:

• PostgreSQL and SQLite: DDL is transactional. You can CREATE TABLE inside a transaction and roll it back.
• MySQL: most DDL statements cause an implicit commit of the current transaction before they run, so they cannot be rolled back with it. (MySQL 8.0 made individual DDL statements atomic, but they still end the surrounding transaction.)

This matters for database migrations: in MySQL, a migration that fails halfway can leave the schema partly changed.

A transfer written properly in application code

The pattern is the same in every language: begin, do the work, commit; on any error, roll back. In Python with the built-in sqlite3 module:

import sqlite3

def transfer(conn, src, dst, amount):
    try:
        conn.execute("BEGIN")
        conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?",
                     (amount, src))
        conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?",
                     (amount, dst))
        conn.execute("COMMIT")
    except Exception:
        conn.execute("ROLLBACK")
        raise

conn = sqlite3.connect("bank.db", isolation_level=None)  # manual control

With isolation_level=None, Python's sqlite3 module stops opening transactions on its own, so the explicit BEGIN, COMMIT and ROLLBACK are the only ones.

Interview questions

Q1. What is a transaction?

A transaction is a sequence of reads and writes that the database executes as one logical unit: either all of its changes are committed, or all are rolled back. It is the unit of atomicity, isolation and recovery. In SQL you open one with BEGIN or START TRANSACTION and end it with COMMIT or ROLLBACK; outside an explicit transaction most databases autocommit each statement.

Q2. Explain ACID with an example.

Take a transfer of 1,000 from A to B. Atomicity: both the debit and credit happen or neither does, enforced with an undo log. Consistency: constraints such as balance >= 0 and the invariant that the total is unchanged hold before and after, enforced by constraint checks plus correct code. Isolation: a concurrent report never sees the debit without the credit, enforced by locking or MVCC. Durability: once committed, the transfer survives a crash, enforced by forcing the write-ahead log to disk at commit.

Q3. Which ACID property is the application's responsibility?

Consistency, partly. The database enforces declared constraints, but it cannot know business rules you never declared, such as "a transfer must credit exactly what it debits". If the transaction logic is wrong, the database will commit a wrong but constraint-valid state. Atomicity, isolation and durability are provided by the DBMS.

Q4. What are the states of a transaction?

Active, partially committed, committed, failed, aborted, and finally terminated. A transaction becomes partially committed after its last statement, and committed once its commit log record reaches stable storage. On any error it becomes failed; after its effects are undone it is aborted, after which the system may restart or kill it.

Q5. What is a schedule? Why not just run transactions serially?

A schedule is the chronological order in which operations from several transactions execute, preserving each transaction's internal order. Serial schedules are always correct but waste resources: while one transaction waits for disk or network, others could use the CPU. Concurrent schedules improve throughput and latency, so we allow them as long as they are equivalent to some serial schedule.

Q6. When do two operations conflict?

When they belong to different transactions, access the same data item, and at least one of them is a write. Read-read pairs never conflict. The order of conflicting operations determines what values transactions read and the final state, so conflict equivalence requires preserving that order.

Q7. How do you test conflict serializability?

Build a precedence graph with a node per transaction and an edge Ti → Tj whenever an operation of Ti conflicts with and precedes an operation of Tj. If the graph is acyclic, the schedule is conflict serializable, and any topological order gives an equivalent serial schedule. If there is a cycle, it is not conflict serializable.

Q8. What is the difference between conflict and view serializability?

Conflict serializability requires preserving the order of every conflicting pair; view serializability only requires that each transaction reads the same values and the same transaction writes each item last. Every conflict-serializable schedule is view serializable, but not vice versa; the extra view-serializable schedules always contain blind writes. Testing view serializability is NP-complete, so databases enforce conflict serializability.

Q9. What is a blind write?

A write of a data item that the transaction did not read first, for example W2(A) without a preceding R2(A). Blind writes are what allow a schedule to be view serializable without being conflict serializable, because an overwritten blind write's order does not affect any read or the final state.

Q10. What is a recoverable schedule?

One in which, if Tj reads a value written by Ti, Ti commits before Tj commits. This guarantees that if Ti aborts, Tj has not committed yet and can be aborted too. A non-recoverable schedule, such as W1(A) R2(A) C2 A1, leaves a committed transaction depending on data that was rolled back.

Q11. What is a cascading rollback and how do you avoid it?

A cascading rollback is when one transaction's abort forces others that read its uncommitted data to abort, and so on down the chain. You avoid it with cascadeless schedules, where transactions read only committed data. Strict two-phase locking achieves this by holding exclusive locks until commit.

Q12. What is a strict schedule and why do databases prefer it?

In a strict schedule, no transaction reads or overwrites an item until the transaction that last wrote it has committed or aborted. It makes undo trivial, because restoring a transaction's before-images can never wipe out another transaction's work. Strict two-phase locking produces strict schedules, which is why it is the common lock-based protocol.

Q13. What does a savepoint do?

A savepoint marks a point inside a transaction. ROLLBACK TO SAVEPOINT undoes only the work after that point while keeping the transaction open, and RELEASE SAVEPOINT discards the marker. Savepoints are used for partial error recovery and to implement nested transactions in ORMs. Nothing is permanent until the outer COMMIT.

Q14. Can a serializable schedule be non-recoverable?

Yes. W1(A) R2(A) C2 A1 has one conflict, giving T1 → T2, so it is conflict serializable, but T2 committed after reading T1's uncommitted data and T1 then aborted. Serializability and recoverability are separate requirements, and a concurrency-control protocol must provide both.

Q15. Can DDL be rolled back?

It depends on the database. PostgreSQL and SQLite support transactional DDL, so CREATE TABLE or ALTER TABLE inside a transaction can be rolled back. In MySQL most DDL causes an implicit commit, ending the current transaction, so it cannot be rolled back with the rest of the work.

Key takeaways

• A transaction is an all-or-nothing unit of work ending in commit or abort.
• Atomicity comes from undo (or ignoring aborted versions), durability from write-ahead logging flushed at commit, isolation from locking or MVCC, and consistency from constraints plus correct transaction code.
• Transaction states: active, partially committed, committed, failed, aborted, terminated.
• Two operations conflict when they are in different transactions, touch the same item, and at least one writes.
• A schedule is conflict serializable if and only if its precedence graph is acyclic; a topological sort gives the equivalent serial order, which need not match who started first.
• View serializability is more permissive (it admits blind-write schedules) but NP-complete to test, so real systems enforce conflict serializability.
• Strict ⊂ cascadeless ⊂ recoverable. Non-recoverable schedules must never be allowed; strict schedules make undo simple.
• Savepoints allow partial rollback inside a transaction; DDL is transactional in PostgreSQL and SQLite but implicitly commits in MySQL.

Next lesson

Continue with Concurrency control.