Quizmaster

Learning objectives

What you will be able to explain

In the context of ACID properties, which property ensures that a transaction is treated as an "all-or-nothing" unit of work, where either all operations are applied or none are?
Regarding the ACID properties:
What is the primary problem addressed by the "Isolation" property?
Analyze the schedule for conflict serializability.
Which statement correctly describes the relationship between Conflict Serializability and View Serializability?
Which of the following pairs of operations are in conflict?

Section 01

In the context of ACID properties, which property ensures that a transaction is treated as an "all-or-nothing" unit of work, where either all operations are applied or none are? to What is the primary problem addressed by the "Isolation" property?

In the context of ACID properties, which property ensures that a transaction is treated as an "all-or-nothing" unit of work, where either all operations are applied or none are?

Atomicity is the all-or-nothing guarantee: either the full transaction effects become durable or none of them do. Partial completion is not allowed.

Regarding the ACID properties:

A is True because consistency means constraints hold before and after committed transactions. B is False because durability is mainly recovery/logging responsibility, not lock management. C is True since isolation hides intermediate states. D is True because logging and recovery mechanisms implement atomic rollback/commit behavior.

What is the primary problem addressed by the "Isolation" property?

Isolation addresses concurrent-interference anomalies (dirty reads, lost updates, etc.) by controlling visibility and ordering among overlapping transactions.

Section 02

Analyze the schedule for conflict serializability. to Which of the following pairs of operations are in conflict?

Analyze the schedule for conflict serializability.

Path of Calculation: 1. Identify conflicting operations (different transactions, same item, at least one write). 2.

r_2(Y) \rightarrow w_1(Y)

creates an edge

T_2 \rightarrow T_1

. 3.

w_2(Y) \rightarrow r_1(Y)

creates an edge

T_2 \rightarrow T_1

. 4.

w_2(Y) \rightarrow w_1(Y)

creates an edge

T_2 \rightarrow T_1

. 5. The resulting precedence graph is

T_2 \rightarrow T_1

. 6. Since there are no cycles, the schedule is conflict-serializable. Correct Answer: Yes, it is conflict-serializable; equivalent to serial order

(T_2, T_1)

Guided checkpoint

Given schedule $S$ :

Step	Operation
1	$r_1(X)$
2	$r_2(Y)$
3	$w_1(X)$
4	$w_2(Y)$
5	$r_1(Y)$
6	$w_1(Y)$

Determine whether $S$ is conflict-serializable and identify the equivalent serial order (if one exists).

Which statement correctly describes the relationship between Conflict Serializability and View Serializability?

Conflict-serializable schedules are a subset of view-serializable schedules, so every conflict-serializable schedule is also view-serializable.

Which of the following pairs of operations are in conflict?

Two operations conflict when they are from different transactions, access the same item, and at least one is a write. That matches B (read-write on X) and D (write-write on X).

Section 03

Complete the Lock Compatibility Matrix (Yes = Compatible, No = Conflict): to Evaluate the sequence under Two-Phase Locking (2PL).

Complete the Lock Compatibility Matrix (Yes = Compatible, No = Conflict):

Shared locks are compatible with shared locks, so S/S is Yes. Any request involving an exclusive lock against an existing S or X lock conflicts, so S/X and X/X are No.

What is the defining rule of the Two-Phase Locking (2PL) protocol?

2PL has a growing phase (acquire locks) and shrinking phase (release locks). Once any lock is released, no new locks may be acquired.

Evaluate the sequence under Two-Phase Locking (2PL).

Path of Calculation: 1. The "Growing Phase" is

Lock_S(A), Lock_X(B)

. 2. The "Shrinking Phase" starts at

Unlock(A)

. 3. According to 2PL, once you enter the shrinking phase (by unlocking), you cannot acquire new locks. 4. Therefore,

Lock_S(C)

after

Unlock(A)

is a violation. Correct Answer: The first sequence is valid 2PL. Adding

Lock_S(C)

after

Unlock(A)

makes it NOT 2PL.

Guided checkpoint

A transaction $T$ follows the Two-Phase Locking protocol and performs this sequence:

$Lock_S(A), Read(A), Lock_X(B), Write(B), Unlock(A), Unlock(B)$

Is this valid according to 2PL? If $T$ then tried to $Lock_S(C)$ after $Unlock(A)$ , would it still be 2PL?

Section 04

What is the primary difference between Basic 2PL and Strict 2PL? to In hierarchical locking (Multiple Granularity Locking), what does an "Intention Exclusive" (IX) lock at the Table level signify?

What is the primary difference between Basic 2PL and Strict 2PL?

Strict 2PL keeps X locks until commit/abort, preventing other transactions from seeing uncommitted writes and thereby avoiding cascading aborts.

Regarding 2PL variations:

A is False because 2PL can still deadlock. B is True: strictness prevents reading uncommitted written data. C is True: rigorous 2PL holds both S and X locks to commit. D is True: 2PL guarantees conflict-serializability.

In hierarchical locking (Multiple Granularity Locking), what does an "Intention Exclusive" (IX) lock at the Table level signify?

An IX lock at table level signals intent to acquire exclusive locks on descendants (pages/rows). It is an intention marker, not full-table exclusive ownership.

Section 05

Transaction $T_1$ holds an IX lock on Table $R$ . Transaction $T_2$ requests an S lock on Table $R$ . Based on the compatibility matrix for intention locks, should $T_2$ be granted the lock? to Which deadlock prevention strategy aborts the younger transaction if it requests a lock held by an older transaction?

Transaction $T_1$ holds an IX lock on Table $R$ . Transaction $T_2$ requests an S lock on Table $R$ . Based on the compatibility matrix for intention locks, should $T_2$ be granted the lock?

Path of Calculation: 1. Check IX compatibility with S. 2. IX means someone is writing a lower node. S means someone wants to read the whole node. 3. These are incompatible because the whole-node reader (S) cannot be sure the lower-level writer (X) isn't changing data they are reading. Correct Answer: No.

Which of the following lock modes are compatible with a "Shared Intention Exclusive" (SIX) lock?

SIX combines shared access at the node with intention to take exclusive locks below. In standard compatibility, only IS can coexist safely with SIX at the same node.

Which deadlock prevention strategy aborts the younger transaction if it requests a lock held by an older transaction?

In Wait-Die, older transactions are allowed to wait, while younger requesters die (abort) when they need a lock held by an older transaction.

Section 06

In a "Wait-For Graph", Transaction $T_1$ is waiting for a lock held by $T_2$ , $T_2$ is waiting for $T_3$ , and $T_3$ is waiting for $T_1$ . What does this state represent, and what action must the Lock Manager take? to Match the read phenomenon to its definition:

In a "Wait-For Graph", Transaction $T_1$ is waiting for a lock held by $T_2$ , $T_2$ is waiting for $T_3$ , and $T_3$ is waiting for $T_1$ . What does this state represent, and what action must the Lock Manager take?

Path of Calculation: 1. Nodes:

T_1, T_2, T_3

. Edges:

T_1 \rightarrow T_2, T_2 \rightarrow T_3, T_3 \rightarrow T_1

. 2. This forms a cycle:

T_1 \rightarrow T_2 \rightarrow T_3 \rightarrow T_1

. 3. A cycle in the Wait-For graph indicates a Deadlock. 4. The Lock Manager must select a "victim" transaction to abort. Correct Answer: This is a Deadlock; the Lock Manager must abort one of the transactions.

What is the main drawback of the "Timeout-based" deadlock detection?

Timeout detection is simple but imprecise: long waits can be mistaken for deadlocks, causing unnecessary aborts even when no cycle exists.

Match the read phenomenon to its definition:

Dirty Read means reading uncommitted data. Non-repeatable Read means the same row value changes between reads in one transaction. Phantom Read means a repeated predicate query returns a changed row set due to concurrent inserts/deletes.

Section 07

Which SQL isolation level provides the highest level of protection by preventing all three phenomena (Dirty Read, Non-repeatable Read, Phantom)? to What are the three phases of Optimistic Concurrency Control?

Which SQL isolation level provides the highest level of protection by preventing all three phenomena (Dirty Read, Non-repeatable Read, Phantom)?

Serializable is the strongest SQL isolation level and prevents dirty reads, non-repeatable reads, and phantoms by enforcing serial-equivalent behavior.

Regarding SQL Isolation Levels:

A is True since Read Committed is widely used as default. B is False because row locks alone do not fully prevent phantoms. C is True because Read Uncommitted allows reading data that may roll back. D is False: stronger isolation usually reduces concurrency.

What are the three phases of Optimistic Concurrency Control?

OCC runs transactions in Read, then Validation, then Write phase. It avoids lock-heavy coordination during read and checks conflicts before commit.

Section 08

In OCC, when is the "Validation Phase" performed? to In Snapshot Isolation, which version of a data item does a transaction $T$ read?

In OCC, when is the "Validation Phase" performed?

Validation occurs at commit time boundary: before making writes visible, OCC verifies that serializability/conflict conditions still hold.

What is the primary assumption behind Optimistic Concurrency Control?

OCC is based on the optimistic assumption that conflicts are infrequent, so doing work without locks and occasionally restarting is cheaper overall.

In Snapshot Isolation, which version of a data item does a transaction $T$ read?

Snapshot Isolation gives each transaction a stable snapshot as of its start timestamp, so reads see the latest committed version at transaction start, not at read time.

Section 09

Under Snapshot Isolation, which version of $X$ does $T_1$ see? to Regarding Multi-version Concurrency Control (MVCC):

Under Snapshot Isolation, which version of $X$ does $T_1$ see?

Path of Calculation: 1. Under SI, a transaction sees the state of the DB as of its Start Timestamp (

ST

). 2.

T_1

ST

is 10. 3. Any updates committed after time 10 are invisible to

T_1

. 4.

T_2

's update committed at time 20, which is

> 10

. Correct Answer:

T_1

sees the version of

X

that existed at time 10 (the version before

T_2

's update).

Guided checkpoint

Given the following timeline:

Time	Event
10	$T_1$ starts ( $ST=10$ )
15	$T_2$ starts ( $ST=15$ )
20	$T_2$ updates $X$ and commits ( $CT=20$ )
25	$T_1$ reads $X$

Determine which version of $X$ is visible to $T_1$ .

What is the "First-Committer-Wins" rule in Snapshot Isolation used for?

First-Committer-Wins resolves concurrent write-write conflicts by allowing only the earliest committer to succeed, preventing lost updates under SI.

Regarding Multi-version Concurrency Control (MVCC):

MVCC lets readers use stable versions without blocking writers, and writers can create new versions without blocking readers in common cases. Old versions must be garbage-collected. MVCC supports multiple isolation levels, not only Read Uncommitted.

Section 10

What is the "Write Skew" anomaly? to A system uses "Wound-Wait". $T_{old}$ (Start Time 100) requests a lock held by $T_{young}$ (Start Time 200). What happens? Conversely, what happens if $T_{young}$ requests a lock held by $T_{old}$ ?

What is the "Write Skew" anomaly?

Write skew occurs when concurrent transactions read overlapping predicates but update different rows, jointly violating a cross-row constraint. Snapshot Isolation is known to permit this anomaly.

Which component of the DBMS architecture is responsible for managing the lifecycle of locks?

The lock manager grants, queues, upgrades, and releases locks and handles deadlock-related decisions, making it the component that controls lock lifecycle.

A system uses "Wound-Wait". $T_{old}$ (Start Time 100) requests a lock held by $T_{young}$ (Start Time 200). What happens? Conversely, what happens if $T_{young}$ requests a lock held by $T_{old}$ ?

Path of Calculation: 1. Wound-Wait Rule: If

T_{req}

is older than

T_{held}

T_{req}

"wounds" (aborts)

T_{held}

. 2. Wound-Wait Rule: If

T_{req}

is younger than

T_{held}

T_{req}

waits. 3. Scenario 1:

100 < 200 \rightarrow T_{old}

wounds

T_{young}

. 4. Scenario 2:

200 > 100 \rightarrow T_{young}

waits. Correct Answer: In the first case,

T_{young}

is aborted. In the second case,

T_{young}

waits.

Knowledge check

Turn understanding into recall.

The quiz now follows the same concepts in scored form. You can return to this lesson from the quiz whenever a gap appears.

Start the quiz →Back to course

What you will be able to explain

In the context of ACID properties, which property ensures that a transaction is treated as an "all-or-nothing" unit of work, where either all operations are applied or none are? to What is the primary problem addressed by the "Isolation" property?

In the context of ACID properties, which property ensures that a transaction is treated as an "all-or-nothing" unit of work, where either all operations are applied or none are?

Regarding the ACID properties:

What is the primary problem addressed by the "Isolation" property?

Analyze the schedule for conflict serializability. to Which of the following pairs of operations are in conflict?

Analyze the schedule for conflict serializability.

Which statement correctly describes the relationship between Conflict Serializability and View Serializability?

Which of the following pairs of operations are in conflict?

Complete the Lock Compatibility Matrix (Yes = Compatible, No = Conflict): to Evaluate the sequence under Two-Phase Locking (2PL).

Complete the Lock Compatibility Matrix (Yes = Compatible, No = Conflict):

What is the defining rule of the Two-Phase Locking (2PL) protocol?

Evaluate the sequence under Two-Phase Locking (2PL).

What is the primary difference between Basic 2PL and Strict 2PL? to In hierarchical locking (Multiple Granularity Locking), what does an "Intention Exclusive" (IX) lock at the Table level signify?

What is the primary difference between Basic 2PL and Strict 2PL?

Regarding 2PL variations:

In hierarchical locking (Multiple Granularity Locking), what does an "Intention Exclusive" (IX) lock at the Table level signify?

Transaction T1T_1T1​ holds an IX lock on Table RRR. Transaction T2T_2T2​ requests an S lock on Table RRR. Based on the compatibility matrix for intention locks, should T2T_2T2​ be granted the lock?

Which of the following lock modes are compatible with a "Shared Intention Exclusive" (SIX) lock?

Which deadlock prevention strategy aborts the younger transaction if it requests a lock held by an older transaction?

In a "Wait-For Graph", Transaction T1T_1T1​ is waiting for a lock held by T2T_2T2​, T2T_2T2​ is waiting for T3T_3T3​, and T3T_3T3​ is waiting for T1T_1T1​. What does this state represent, and what action must the Lock Manager take? to Match the read phenomenon to its definition:

In a "Wait-For Graph", Transaction T1T_1T1​ is waiting for a lock held by T2T_2T2​, T2T_2T2​ is waiting for T3T_3T3​, and T3T_3T3​ is waiting for T1T_1T1​. What does this state represent, and what action must the Lock Manager take?

What is the main drawback of the "Timeout-based" deadlock detection?

Match the read phenomenon to its definition:

Which SQL isolation level provides the highest level of protection by preventing all three phenomena (Dirty Read, Non-repeatable Read, Phantom)? to What are the three phases of Optimistic Concurrency Control?

Which SQL isolation level provides the highest level of protection by preventing all three phenomena (Dirty Read, Non-repeatable Read, Phantom)?

Regarding SQL Isolation Levels:

What are the three phases of Optimistic Concurrency Control?

In OCC, when is the "Validation Phase" performed? to In Snapshot Isolation, which version of a data item does a transaction TTT read?

In OCC, when is the "Validation Phase" performed?

What is the primary assumption behind Optimistic Concurrency Control?

In Snapshot Isolation, which version of a data item does a transaction TTT read?

Under Snapshot Isolation, which version of XXX does T1T_1T1​ see? to Regarding Multi-version Concurrency Control (MVCC):

Under Snapshot Isolation, which version of XXX does T1T_1T1​ see?

What is the "First-Committer-Wins" rule in Snapshot Isolation used for?

Regarding Multi-version Concurrency Control (MVCC):

What is the "Write Skew" anomaly? to A system uses "Wound-Wait". ToldT_{old}Told​ (Start Time 100) requests a lock held by TyoungT_{young}Tyoung​ (Start Time 200). What happens? Conversely, what happens if TyoungT_{young}Tyoung​ requests a lock held by ToldT_{old}Told​?

What is the "Write Skew" anomaly?

Which component of the DBMS architecture is responsible for managing the lifecycle of locks?

A system uses "Wound-Wait". ToldT_{old}Told​ (Start Time 100) requests a lock held by TyoungT_{young}Tyoung​ (Start Time 200). What happens? Conversely, what happens if TyoungT_{young}Tyoung​ requests a lock held by ToldT_{old}Told​?

Turn understanding into recall.

Transaction $T_1$ holds an IX lock on Table $R$ . Transaction $T_2$ requests an S lock on Table $R$ . Based on the compatibility matrix for intention locks, should $T_2$ be granted the lock?

In a "Wait-For Graph", Transaction $T_1$ is waiting for a lock held by $T_2$ , $T_2$ is waiting for $T_3$ , and $T_3$ is waiting for $T_1$ . What does this state represent, and what action must the Lock Manager take? to Match the read phenomenon to its definition:

In a "Wait-For Graph", Transaction $T_1$ is waiting for a lock held by $T_2$ , $T_2$ is waiting for $T_3$ , and $T_3$ is waiting for $T_1$ . What does this state represent, and what action must the Lock Manager take?

In OCC, when is the "Validation Phase" performed? to In Snapshot Isolation, which version of a data item does a transaction $T$ read?

In Snapshot Isolation, which version of a data item does a transaction $T$ read?

Under Snapshot Isolation, which version of $X$ does $T_1$ see? to Regarding Multi-version Concurrency Control (MVCC):

Under Snapshot Isolation, which version of $X$ does $T_1$ see?

What is the "Write Skew" anomaly? to A system uses "Wound-Wait". $T_{old}$ (Start Time 100) requests a lock held by $T_{young}$ (Start Time 200). What happens? Conversely, what happens if $T_{young}$ requests a lock held by $T_{old}$ ?

A system uses "Wound-Wait". $T_{old}$ (Start Time 100) requests a lock held by $T_{young}$ (Start Time 200). What happens? Conversely, what happens if $T_{young}$ requests a lock held by $T_{old}$ ?