GK One-Line Question & Answer

A named point within a transaction to which a partial rollback can be made - ROLLBACK TO SAVEPOINT name undoes work done after the savepoint but keeps work done before it allowing partial recovery without rolling back the entire transaction

2PL: a transaction must acquire ALL needed locks before releasing ANY lock - Phase 1 (growing phase): acquire locks only no releases. Phase 2 (shrinking phase): release locks only no acquisitions. This guarantees serializability because the lock acquisition order is consistent and creates a serial order

When one transaction rolls back all other transactions that read its uncommitted data must also rollback (cascade) because those reads are now dirty reads - prevented by Strict 2PL (holds write locks until commit so other transactions cannot read uncommitted changes)

PCC: locks data when accessed to prevent concurrent modifications (assume conflicts will occur); OCC: no locks during execution validates at commit time for conflicts (assume conflicts are rare) aborts if conflict detected - OCC better for low-contention workloads PCC better for high-contention

Each transaction receives a timestamp at start; data items track the timestamps of the last read (max_R_TS) and write (max_W_TS); conflicts are resolved using these timestamps: if a younger transaction tries to read data written by an older committed transaction it is allowed; otherwise abort and restart

An optimization that allows a write operation to be silently ignored (rather than aborting the transaction) when a newer transaction has already written the same data item - since the outdated write would be overwritten by the newer write anyway it is safe to skip

A transaction that remains open for an extended period causing: accumulated locks blocking other transactions MVCC undo/version storage growth (old versions cannot be purged) replication lag in logical replication and increased probability of conflict/deadlock

The technique of dividing a long transaction into smaller sub-transactions to improve concurrency; safe only when: the sub-transactions maintain the same semantic consistency there are no dependencies between the pieces that require atomic execution and the application can handle partial failures

READ COMMITTED prevents dirty reads (only sees committed data at the time of each statement) but allows non-repeatable reads (re-reading same row can return different committed values) and phantom reads

A local transaction operates within a single DBMS; a distributed transaction spans multiple separate database instances and requires coordination protocols (typically Two-Phase Commit - 2PC) to ensure atomicity across all participating databases

Snapshot isolation: each transaction reads from a consistent snapshot taken at transaction start preventing dirty reads non-repeatable reads and phantoms but does NOT prevent write skew anomaly. REPEATABLE READ in standard SQL prevents non-repeatable reads; SNAPSHOT adds phantom prevention via MVCC

Two transactions each read overlapping data make decisions based on it and write to non-overlapping parts collectively violating an integrity constraint that neither individual write would violate - e.g. both doctors check at least one doctor on call both see the other is on call both decide to go off call

COMMIT WORK makes all transaction changes permanent and releases locks; ROLLBACK WORK undoes all changes and releases locks; implicit COMMIT occurs in most DBMS when: a DDL statement is executed (CREATE ALTER DROP) when a session normally ends or when autocommit is enabled (each statement auto-commits)

MVCC maintains multiple timestamped versions of each row; readers see the latest committed version as of their transaction start time (snapshot) without needing to acquire locks on data; writers create new versions without affecting readers current snapshots - reads and writes proceed concurrently

The X/Open XA specification for distributed transaction processing: defines an interface between a transaction manager (TM) and resource managers (RM/databases) enabling global transactions that span multiple databases message queues and other XA-compliant resources using 2PC coordination

Immediate write: changes are written to disk as soon as they occur (easier recovery more I/O). Deferred write: changes are kept in memory (buffer pool) and written to disk lazily (batch I/O better performance). WAL is written immediately for durability; data pages use deferred write (buffered writes with WAL ensuring recovery)

REPEATABLE READ prevents dirty reads and non-repeatable reads (same row read twice returns same value within a transaction by holding read locks or using MVCC snapshots) but in standard SQL REPEATABLE READ allows phantom reads - though MySQL InnoDB also prevents phantoms via gap locks

ACID (Atomicity Consistency Isolation Durability) was formalized by Jim Gray and Andreas Reuter in 1992 in their book Transaction Processing: Concepts and Techniques though the individual concepts were discussed earlier by Eswaran et al in 1976 (regarding consistency and isolation) and Gray in 1978 (regarding transaction properties)

Redo log: records the AFTER-image (new value) of each change enabling REDO (replay) of committed transactions during crash recovery to bring the database to the committed state. Undo log: records the BEFORE-image (original value) of each change enabling ROLLBACK of uncommitted transactions and MVCC snapshots for concurrent readers

A nested transaction is a transaction started within another transaction; in most DBMS true nested transactions are not supported but SAVEPOINTs provide similar functionality - a ROLLBACK TO SAVEPOINT is analogous to rolling back an inner transaction while keeping the outer transaction alive

A hierarchical locking mechanism: Intention Shared (IS) and Intention Exclusive (IX) are set on higher-level objects (table page) to signal intent to lock rows within them - this avoids scanning all row-level locks to check table-level lock compatibility making table-level lock checks O(1) instead of O(rows)

When a transaction acquires too many fine-grained locks (row/page level) the DBMS automatically escalates to a coarser-grained lock (table level) to reduce lock management overhead - reduces memory used for lock structures at the cost of reduced concurrency

A wait-for graph has one node per transaction and a directed edge T1 to T2 if T1 is waiting for a lock held by T2; a cycle in this graph indicates a deadlock; DBMS resolves by selecting a victim transaction to abort (typically the youngest cheapest or least-work-done transaction)

Ensuring deadlocks cannot occur by design without needing to detect them: (1) Wait-Die: older transaction waits younger dies/restarts; (2) Wound-Wait: older transaction wounds (forces rollback of) younger younger waits; (3) Lock ordering: all transactions acquire locks in predefined order (no circular waits possible)

Locking all rows satisfying a predicate (not just currently existing rows) to prevent phantom reads - e.g. locking all rows WHERE salary > 50000 prevents any insertion of a new row with salary=60000 by another transaction; theoretically necessary because row-level locks cannot prevent phantom insertions

A lock on the gap between index values that prevents other transactions from inserting new rows into that gap - used by InnoDB REPEATABLE READ to prevent phantom reads by locking the index ranges between existing values

A combination of a gap lock + record lock that locks both the index record AND the gap before it; used as the standard locking unit in InnoDB for REPEATABLE READ - e.g. next-key lock on value 20 locks the record 20 AND the gap (10 20)

MVCC: readers never block writers and writers never block readers (readers see old versions writers create new versions); Lock-based: readers block writers (S-lock) and writers block readers and writers (X-lock). MVCC provides higher read concurrency but uses more storage for multiple versions

A concurrency control method that builds the conflict serialization graph in real-time; commits the transaction only if its addition to the graph does not create a cycle (ensuring the resulting schedule is serializable); aborts if a cycle would be created

Centralized: all lock requests go to one lock manager node (simple consistent but single point of failure and bottleneck). Distributed: lock management responsibility spread across nodes (better scalability and fault tolerance but complex coordination and potential split-brain issues)

Deadlock: transactions are stuck waiting indefinitely (no progress possible). Livelock: transactions are active (not blocked) but keep aborting and restarting in response to each other without making progress - e.g. two transactions using Wait-Die that repeatedly abort each other

Strict 2PL holds ALL locks (both read and write) until the transaction commits or aborts instead of releasing write locks early. This prevents: cascading rollbacks (no dirty reads possible as uncommitted data is always locked) and simplifies recovery (undo only affects the aborting transaction)

Three phases: Read (execute locally no locks reads from database writes to private workspace); Validate (check if any committed transaction since start wrote to data this transaction read); Write (apply local workspace to database if validation passed)

A schedule is conflict-serializable if it is equivalent to some serial schedule; tested by building the conflict graph: edge Ti to Tj if Ti performs an operation conflicting with a later operation by Tj (both access same data item at least one is a write); schedule is conflict-serializable iff graph is acyclic

View serializability is a broader criterion: a schedule S is view-serializable if it is view-equivalent to some serial schedule (same transactions read the same values and produce the same final writes). Every conflict-serializable schedule is view-serializable but NOT vice versa

A policy where a transaction immediately aborts if any requested lock is not immediately available (rather than waiting) then restarts - eliminates deadlocks by eliminating the hold and wait condition; appropriate for: short transactions high-contention workloads where waiting is rarely productive and systems where fast retry is acceptable

A situation where a transaction is indefinitely prevented from acquiring a lock because other transactions continuously acquire the same lock preventing the waiting transaction from ever proceeding; prevented by: FIFO queuing for lock requests priority aging (waiting transactions gain priority over time)

Deliberately using a lower isolation level than SERIALIZABLE to improve performance; safe when: the application can tolerate specific anomalies (e.g. approximate counts where phantom reads are acceptable) or when transactions are read-only (no write skew possible) or when application-level controls compensate

New rows satisfying a predicate can be inserted by other transactions after the predicate is evaluated causing re-evaluation to return different rows - row-level locking cannot prevent this because the new rows do not exist yet when locks are acquired (cannot lock non-existent rows)

A concurrency control combining 2PL with two versions of data: write transactions use 2PL to synchronize with each other but read transactions read older committed versions (not the newest possibly-being-written version) allowing reads to proceed without waiting for write locks