Thursday, March 7th, 2024
Don't be afraid to go out on a limb. That's where the fruit is.
— H. Jackson Brown Jr.
ECS165A Lecture
multiple-granularity locks (not internal latches)
- database → table → page → tuple
- contention (especially database-level lock)
- in some scenarios, thread may also hold a lock (apart from transactions)
- acquiring an exclusive lock on a tuple require acquiring shared locks on all previous levels (all locks from database until tuple)
- problems
- can’t get multi-threaded read-only access to entire layer
- can’t get multi-threaded reads and writes of different tuples
- can’t get multi-threaded write access to different tuples
Instead of simple shared/exclusive (S, X) locks, we use intention locks (S, X, IS, IX) for each level
- two new lock types: IS (intention to share / read), IX (intention to exclude, but at a more granular level)
- intention locks are for granular level access
- S and X is reserved for bulk access, e.g. for a database lock:
- S = bulk read of entire database (makes database read-only while lock is active)
- X = bulk write of entire database
- so S is incompatible with IX because S doesn’t want any writes regardless of what level it’s on (whether it’s at whole database level or lower)
- when locking: go from top to bottom
- e.g. for writing: acquire IX for DB, table, and page, then acquire X for tuple
- when unlocking: go from bottom to top
- SIX mode: it’s also possible to have S & IX in the same lock (but S and IX are not compatible when they are in separate locks)
- at a single layer, a SIX lock is only compatible with IS (read-only at a lower level)
- lock acquisition
- transaction must acquire locks top-down (database before table, etc)
- to acquire S / IS at a lower level, must acquire IS / IX at a higher level
- to acquire X / IX / SIX at a lower level, must acquire IX / SIX at a higher level
- examples
- Transaction 1 scans table R and needs to make a few updates
- strategy 1: database level X (too broad)
- strategy 2: database level SIX (too broad)
- strategy 3: database level IX, table level SIX (just right)
- Transaction 2 scans part of R via an index
- strategy 1: database level IS, table level IS, record level S (doesn’t care if we miss a few new records)
- Transaction 3 scans table R
- strategy 1: database level IS, table level S
- Transaction 1 scans table R and needs to make a few updates
Lock compatibility chart (at a single layer):
