SQL & Databases Transactions, Concurrency, and Administration Syllabus

What is the difference between a shared lock and an exclusive lock?

A shared (S) lock permits concurrent reads but blocks writes; an exclusive (X) lock permits neither concurrent reads nor writes and is used for modifications.

What is a deadlock in a database system?

A cycle where two or more transactions each hold locks the others need, so none can proceed; the DBMS resolves it by aborting a victim transaction.

What is lock granularity and what is the tradeoff between fine and coarse locks?

The size of the locked unit (row, page, table). Fine-grained (row) locks maximize concurrency but add overhead; coarse-grained (table) locks reduce overhead but limit concurrency.

What is Two-Phase Locking (2PL)?

A protocol with a growing phase (only acquire locks) and a shrinking phase (only release locks); once any lock is released, no new lock may be acquired, guaranteeing serializability.

What does MVCC stand for and what problem does it solve?

Multi-Version Concurrency Control; it keeps multiple versions of rows so readers never block writers and writers never block readers, avoiding most read locks.

How does snapshot isolation work?

Each transaction reads from a consistent snapshot of the database taken at its start, so it sees a stable view and does not observe concurrent commits.

What is a write skew anomaly under snapshot isolation?

Two transactions read overlapping data, make disjoint writes based on those reads, and both commit, together violating a constraint that neither violated alone.

Contrast optimistic and pessimistic concurrency control.

Pessimistic locks data upfront to prevent conflicts, best under high contention; optimistic lets transactions proceed and checks for conflicts at commit, best under low contention.

How does optimistic locking typically detect a conflict?

By checking a version number or timestamp column at commit time; if the row's version changed since it was read, the update is rejected and retried.

What is the difference between authentication and authorization?

Authentication verifies who a user is (login/identity); authorization determines what an authenticated user is allowed to do (privileges).

What is a role in database security?

A named collection of privileges that can be granted to users or other roles, simplifying permission management by grouping access rights.

What is the difference between a system privilege and an object privilege?

A system privilege allows a database-wide action (e.g., CREATE TABLE); an object privilege allows an action on a specific object (e.g., SELECT on a table).

Write the SQL to give user 'app' read access to the table 'orders'.

GRANT SELECT ON orders TO app;

What does the WITH GRANT OPTION clause do in a GRANT statement?

It allows the recipient to further grant the same privilege to other users.

Write the SQL to remove UPDATE permission on 'orders' from user 'app'.

REVOKE UPDATE ON orders FROM app;

What is the principle of least privilege?

Granting each user or process only the minimum permissions needed to perform its function, reducing the attack surface and blast radius of compromise.

What is Row-Level Security (RLS)?

A mechanism that restricts which rows a user can see or modify by attaching security policies (predicates) that filter rows based on the user's identity or attributes.

How does a Row-Level Security policy enforce access at query time?

The DBMS transparently appends the policy's predicate to every query's WHERE clause, so users only see rows satisfying that condition.

What is SQL injection?

An attack where malicious input is concatenated into a SQL query, altering its logic to read, modify, or destroy data or bypass authentication.

What is the primary defense against SQL injection?

Parameterized queries (prepared statements), which send SQL structure and user data separately so input can never be interpreted as code.

Why is input escaping alone an insufficient defense against SQL injection?

Escaping is error-prone and context-dependent; missed cases or type mismatches still allow injection, whereas parameterized queries are structurally safe.

Distinguish encryption at rest from encryption in transit.

At rest protects stored data on disk/backups (e.g., TDE/AES); in transit protects data moving over the network (e.g., TLS/SSL) from eavesdropping.

What is Transparent Data Encryption (TDE)?

Encryption of database files at rest performed automatically by the DBMS, so data on disk and in backups is encrypted without changing the application.

Compare full, differential, and incremental backups.

Full copies all data; differential copies all changes since the last full backup; incremental copies only changes since the last backup of any type — smallest but slowest to restore.

Define RPO and RTO in backup and recovery planning.

RPO (Recovery Point Objective) is the maximum tolerable data loss measured in time; RTO (Recovery Time Objective) is the maximum tolerable downtime to restore service.

What is Point-in-Time Recovery (PITR)?

Restoring a database to a specific past moment by loading a base backup and replaying archived transaction logs up to the chosen timestamp.

What database components are required to perform Point-in-Time Recovery?

A full base backup plus a continuous archive of write-ahead/transaction logs covering the period up to the target recovery time.

What is Write-Ahead Logging (WAL) and its core rule?

A durability technique that records changes to a log before applying them to data pages; the rule is that the log record must reach durable storage before the corresponding data change.

Contrast synchronous and asynchronous replication.

Synchronous replication confirms a commit only after replicas acknowledge, ensuring zero data loss but higher latency; asynchronous commits on the primary first, giving lower latency but risking loss on failover.

What key performance metrics indicate database health?

Throughput (transactions/queries per second), latency/response time, cache/buffer hit ratio, replication lag, lock wait time, and active connection count.

Give the formula for cache (buffer) hit ratio and explain why it matters.

$\text{Hit Ratio} = \frac{\text{cache hits}}{\text{cache hits} + \text{cache misses}}$; a high ratio means most reads are served from memory, reducing slow disk I/O.