414 Section 7

Locking

May 9th, 2024

Announcements

• HW5 due tomorrow @ 11pm
• Late due date: Sunday @ 11pm

Locking

Locking Overview

Remember - locking is for database internal implementation

• A way to provide the ACID guarantees
• Pessimistic concurrency control
• Assumes transaction executions will conflict
• Users of a database need only specify �BEGIN TRANSACTION … COMMIT / ROLLBACK
• ...and the isolation level...

Database Lock

Table Lock

Predicate Lock

Tuple Lock

Lock Granularity

SQLite!

Less Concurrency

More Concurrency

Binary Locks

2PL with Binary Locks

Lock growing phase

Lock shrinking phase

2PL vs. Strict 2PL

Invalid reads

Trickled unlock and transaction end

2PL vs. Strict 2PL

2PL:

• In every transaction, all lock requests must precede all unlock requests
• Ensures conflict serializability
• Might not be able to recover (Dirty Read: Read on some write that gets rolled back)
• Can result in deadlocks

Strict 2PL:

• Every lock for each transaction is held until commit or abort
• Ensures conflict serializability
• Recoverable as each transaction does not affect others until commit/abort
• Can result in deadlocks

3-Tiered Locking (Shared/Exclusive locks)

• X(A) - Exclusive lock on A
• No other transaction can read or write to A
• S(A) - Shared lock on A
• Other transactions can acquire a shared lock to A, only allows for reads
• If only one transaction holds a shared lock for A, �then it can upgrade to an exclusive lock
• U(A) - No lock on A (or think of it as Unlock)
• This transaction does not hold a lock on A
• If it had a shared lock, some other transactions might still have a lock for A

All the locks!

Worksheet