GK One-Line Question & Answer

An index where the bytes of the key are reversed before storage in the B-tree distributing sequential key inserts (like auto-increment IDs) across all index leaf blocks instead of always inserting into the rightmost block - reduces right-hand-side contention in OLTP systems

Statistical information about index column value distribution (cardinality histograms null count average row size) that the query optimizer uses to estimate result sizes and costs for different execution plan choices - stale or inaccurate statistics lead to poor plan choices

Index seek: uses the B-tree to navigate directly to the matching key value(s) - O(log n) + matching rows only. Index scan: reads all (or most) leaf pages of the index sequentially - O(index_size). Seek is much faster for selective queries; scan used when large fraction of data is returned or index cannot be used for point lookup

Building an index without holding a table lock (allows concurrent reads AND writes during index build) - trade-offs: takes longer than normal index creation (multiple table scans needed) uses more CPU/I/O cannot be done in a transaction and may fail requiring manual REINDEX

An extensible index framework that supports a wide variety of data types and query operators beyond equality/comparison: geometric objects text search IP ranges JSON paths custom types - by allowing user-defined key types and split/union/penalty functions

A directive added to a SQL query that forces or suggests the query optimizer to use a specific index instead of choosing one automatically - should be used sparingly when the optimizer consistently makes poor choices due to inaccurate statistics or atypical query patterns

An automatically built in-memory hash index that InnoDB creates on top of frequently accessed B-tree index pages - when InnoDB detects that the same index lookups are performed repeatedly it builds a hash index for those values enabling O(1) lookup vs O(log n) B-tree traversal

A technique that reduces the storage size of index entries by: prefix compression (storing only the suffix that differs from the previous key for sorted keys) dictionary compression (replacing repeated values with integer codes) or key compression - smaller index means more pages fit in buffer pool improving cache efficiency and reducing I/O

A primary index is built on the primary key and determines the physical order of data (clustered); secondary indexes are built on non-primary key columns and do not determine physical data order (non-clustered in most DBMS) - secondary indexes reference the primary key to locate actual rows

A special index type that stores data by column rather than by row enabling high compression and vectorized batch processing - primarily used for OLAP/analytical workloads where queries aggregate over large datasets and touch only a few columns

The cost of updating indexes on every INSERT/UPDATE/DELETE which includes: additional I/O for each index page write, page splits causing fragmentation, and potential index bloat - managed by: choosing indexes carefully (only create indexes that pay off) rebuilding fragmented indexes (ALTER INDEX REBUILD) and reorganizing (ALTER INDEX REORGANIZE) on a scheduled basis

All operations in a transaction either ALL complete successfully (COMMIT) or ALL are undone (ROLLBACK) - implemented via the undo log: before each change the original value is written to the undo log enabling complete rollback if the transaction fails or is explicitly rolled back

Consistency means the transaction takes the database from one valid state to another - the DBMS enforces defined constraints (PK FK CHECK NOT NULL) but the APPLICATION is responsible for ensuring business-level constraints are upheld (the DB only enforces what it knows about)

Isolation ensures concurrent transactions execute as if they were serial; stronger isolation (SERIALIZABLE) prevents more anomalies but requires more locking/MVCC overhead reducing concurrency and throughput. Weaker isolation (READ COMMITTED) allows more anomalies but permits higher concurrency

Once a transaction is committed its changes persist even through system crashes; WAL implements this by writing log records to disk BEFORE applying changes to the database ensuring that committed changes can be replayed during recovery even if the data pages were not yet flushed

Reading uncommitted changes of another concurrent transaction - if that other transaction later rolls back the read data never existed. Only READ UNCOMMITTED isolation level allows dirty reads; all higher levels prevent them

Within one transaction reading the same row twice returns different values because another committed transaction modified that row between the two reads; prevented by REPEATABLE READ and SERIALIZABLE isolation levels

Within one transaction executing the same range query twice returns different sets of rows because another committed transaction inserted/deleted rows in the query range; SERIALIZABLE adds range locking (predicate locks) or snapshot validation beyond REPEATABLE READ which only locks existing rows

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