Basic Locking Concepts

What is Locking?

Think of a library book - only one person can check it out at a time. Similarly, in databases, locks ensure that only one transaction can modify data at a time to prevent conflicts.

Lock Modes

1. Shared (S) Lock

• Like reading a book - multiple people can read the same book simultaneously

• Transaction can READ but cannot MODIFY the data

• Example: T1 has S-lock on Account A, T2 can also get S-lock to read Account A

2. Exclusive (X) Lock

• Like editing a document - only one person can edit at a time

• Transaction can both READ and MODIFY the data

• Example: T1 has X-lock on Account A, no other transaction can access Account A

Lock Compatibility Matrix

Copy      S    X
S   YES   NO
X    NO   NO

• S+S = Compatible (multiple readers allowed)

• S+X = Incompatible (reader-writer conflict)

• X+X = Incompatible (writer-writer conflict)

Problems Without Proper Locking

Starvation Example:

• T2 has S-lock on item X

• T1 requests X-lock on item X (must wait)

• T3 gets S-lock on item X (compatible with T2)

• T4 gets S-lock on item X (compatible with T2, T3)

• T1 keeps waiting forever!

Solution: First-come-first-served rule - no new requests bypass waiting requests.