Shared/exclusive locks; compatibility matrix

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What does a strict schedule forbid? from Lesson 6.5.1
2. When is a schedule conflict serializable? from Lesson 6.4.2
3. Which ACID property does concurrency control protect? from Lesson 6.1.1

1 · The Hook

A library lets many people read the same reference book at once, but when someone is correcting it, nobody else may read or edit it. Databases control access to data items the same way, with two kinds of locks: shared for reading, exclusive for writing.

2 · Predict First

T1 holds a shared (read) lock on A. T2 asks for a shared lock on A; T3 asks for an exclusive (write) lock on A. Which requests are granted immediately?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

T2's shared lock is granted (readers do not interfere). T3 must wait until T1 (and T2) release their shared locks.

Lock modes

• Shared (S) lock: needed to read an item. Several transactions may hold S locks on the same item at once.
• Exclusive (X) lock: needed to write an item (and allows reading it). Only one transaction may hold an X lock, and no one else may hold any lock on that item.

Compatibility matrix

Lock manager

Transactions issue lock-S(A), lock-X(A) and unlock(A). The lock manager grants a request if it is compatible with all locks held by other transactions on that item; otherwise the transaction waits in a queue.

• Upgrade: a transaction holding S on A converts it to X (allowed if no other transaction holds S on A).
• Downgrade: X to S.

Locking alone is not enough

Locks make each read and write safe, but releasing a lock too early still allows non-serializable schedules:

T1: lock-X(A) R(A) W(A) unlock(A) · T2: lock-X(A) R(A) W(A) lock-X(B) R(B) W(B) unlock(A) unlock(B) · T1: lock-X(B) R(B) W(B) unlock(B)

T2 sees T1's new A but old B: precedence T1 → T2 (on A) and T2 → T1 (on B): a cycle. A rule about when to release locks is needed: two-phase locking, next lesson.

4 · Visual

              requested S      requested X
held S        grant            wait
held X        wait             wait

"Many readers, or one writer, never both."

5 · Worked Examples

Example 1 Easy

T1 holds X on B. T2 requests S on B. Granted?

Solution

No: S is incompatible with X. T2 waits.

Example 2 Medium

T1 and T2 both hold S on A. T1 asks to upgrade to X. What happens?

Solution

T1 waits until T2 releases its S lock (X is incompatible with T2's S).

Example 3 GATE

Why can locking with early unlocks still produce a non-serializable schedule?

Solution

Because a transaction can release a lock on A, let another transaction read and change A, and then lock B after the other transaction changed B: conflicts on A and B then point in opposite directions (a cycle). Locks protect single operations, not the order of a whole transaction.

8 · Connect the Dots

Lesson 6.5.1 defined safe schedules; this lesson introduced locks, the mechanism that enforces them. Next: two-phase locking, the rule on lock and unlock timing that guarantees conflict serializability.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Compatibility

Which pair of locks on the same item can be held by two different transactions at once?

1. (A) X and X
2. (B) S and S
3. (C) X and S
4. (D) S and X

Q2 · MCQ · 1 mark(s) — Request

T1 holds S on Q. T2 requests X on Q. The lock manager:

1. (A) aborts T1
2. (B) downgrades T1
3. (C) makes T2 wait
4. (D) grants it

Q3 · NAT · 1 mark(s) — Matrix

In the S/X compatibility matrix, how many of the 4 entries are "grant"?

Numerical answer: ________

Q4 · MSQ · 2 mark(s) — Locks

Which are true?

1. (A) Upgrading S to X can require waiting.
2. (B) An X lock lets its holder read the item.
3. (C) Several transactions may hold S locks on one item.
4. (D) Locking every item before access guarantees serializability, whatever the unlock order.

Q5 · MCQ · 1 mark(s) — Purpose

Shared locks are requested for:

1. (A) deleting a table
2. (B) writing
3. (C) committing
4. (D) reading

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

lockshared lock (S)exclusive lock (X)compatibility matrixlock managerlock upgrade / downgrade