TR-C flashcards

33 cards. Read the term and the definition here. The deck at the top of this page is optional.

ACID

Four transaction guarantees: atomicity (all or nothing), consistency (valid state to valid state), isolation (concurrent transactions do not interfere), durability (committed changes survive failure).

Dirty read

A transaction reads data written by another transaction that has not yet committed. If the writer rolls back, the reader acted on data that never officially existed.

Phantom read

A transaction re-runs a query with the same predicate and sees new rows inserted by another committed transaction, because the predicate range itself changed between executions.

Write-ahead logging

Protocol requiring the log record describing a change to reach stable storage before the corresponding data page is written, enabling redo after a crash.

Strict two-phase locking

Locking protocol with a growing phase where locks are acquired and a shrinking phase where they are released; strict 2PL holds exclusive locks until commit to prevent cascading rollback.

Lost update

Two transactions read the same value, each computes a new value, and the second write overwrites the first, silently discarding one update.

Third normal form

A relation in second normal form with no transitive dependency, meaning every non-key attribute depends on the key and nothing but the key.

Star schema

Dimensional model with one central fact table holding measures and foreign keys, surrounded by denormalized dimension tables that provide descriptive attributes for filtering and grouping.

Fact table grain

The precise meaning of one fact row, such as one order line per day. Declaring grain first prevents double counting when measures from different levels are mixed.

SCD Type 2

Slowly changing dimension technique that preserves history by inserting a new row for each change, with effective dates and a current-row flag.

Junction table

Table resolving a many-to-many relationship, containing foreign keys to both parent tables, usually with a composite primary key and optional relationship attributes.

Hash partitioning

Partitioning that applies a hash function to a key so rows spread evenly across partitions, optimizing parallel writes but requiring all partitions for range queries.

Clustered index

Index that determines the physical storage order of table rows. A table can have only one, and it is often the primary key.

Covering index

Index containing all columns referenced by a query, so the engine satisfies the query from the index without touching the base table.

Parameterized query

Statement where SQL text and data values are sent separately, allowing plan reuse and preventing injected values from being parsed as code.

Connection pool exhaustion

Condition where all pooled connections are checked out, usually because connections are leaked or held during long operations, causing new requests to time out.

Read-committed snapshot

Isolation option where readers use row versions instead of shared locks, so reads do not block writers and writers do not block readers.

Foreign key index

Index on the referencing column of a foreign key. Without it, parent deletes and updates scan the child table and hold wider locks.

Full recovery model

Recovery model that logs every change and requires log backups for point-in-time restore. The log cannot truncate until a log backup completes.

Recovery point objective

RPO is the maximum acceptable data loss expressed as time. It determines backup frequency, since data written after the last backup is lost.

Recovery time objective

RTO is the maximum acceptable downtime after a failure. It determines whether standby servers, clustering, or restore-from-backup strategies are required.

Differential backup

Backup of all extents changed since the most recent full backup. Restoring requires the full backup, the latest differential, then subsequent log backups.

Log truncation

Release of inactive log space. In full recovery it happens only after a log backup; in simple recovery a checkpoint truncates the log.

Availability group

High-availability feature replicating databases to secondary replicas that can host readable copies and support automatic or manual failover.

Blocking versus deadlock

Blocking is a normal lock wait that ends when the holder commits. A deadlock is a wait cycle the engine resolves by aborting one victim.

PAGEIOLATCH wait

Wait type indicating a session is waiting for a data page to be read from disk into memory, a signal of storage latency or memory pressure.

Parameter sniffing

Behavior where a plan is compiled for the first parameter values seen and cached. Atypical values then receive an inefficient plan until recompiled.

Stale statistics

Outdated distribution data causing the optimizer to misestimate row counts, often producing nested loops where a hash join would be faster.

Index fragmentation

Out-of-order or sparse index pages that increase read cost. Rebuild for high fragmentation on large indexes, reorganize for moderate levels, skip tiny indexes.

Authentication versus authorization

Authentication verifies who a principal is. Authorization determines which objects and actions that principal may access after identity is established.

Least privilege

Granting the minimum permission needed at the narrowest scope, so a compromised or misused account cannot affect objects outside its task.

Dynamic data masking

Feature that hides sensitive values in query results for unauthorized users while the stored data remains unchanged and visible to users holding unmask permission.

Row-level security

Predicate-based filter applied automatically to queries, restricting which rows a principal can see or modify regardless of the query written.