Database concurrency control with precision: orthogonal key-value - PowerPoint PPT Presentation

Database concurrency control with precision: orthogonal key-value locking Goetz Graefe

Locks vs latches 2

Transaction isolation levels Snapshot isolation – great for read-only transactions on multi-version storage 0. No locks – no concurrency control 1. Dirty read – only write locks, no locks for reads 2. Cursor stability – short read locks 3. Repeatable read – read locks on presence but not on absence 4. Serializable – “repeatable count” – locks on predicates or on gaps Simple and reliable transaction logic requires ___ . 3

Example table + secondary index 4

Example table + secondary index 5

Orthogonal key-value locking Summary of possible lock scopes: 6

Phantom protection Gerhard, Gottfried, Select count (*) where FirstName = ‘Harry’ Hank 7

Equality queries Select count (*) where FirstName = ‘Jerry’ James Jim 8

Range queries ...where FirstName between ‘Jerry’ and ‘Mary’ Irv Mohan 9

Non-key updates ...where row# = 3 Non-key fields (“include” syntax), deletion (to ghost), insertion (from ghost) 10

Phantom protection with a ghost Select count (*) where FirstName = ‘Harry’ 11

12