Orthogonal key-value locking Goetz Graefe, Hideaki Kimura - PowerPoint PPT Presentation

Orthogonal key-value locking Goetz Graefe, Hideaki Kimura Hewlett-Packard Laboratories Palo Alto, Cal. – Madison, Wis.

ARIES/KVL What about non-key updates? February 27, 2015 Orthogonal key-value locking 8

ARIES/IM “ Data-only locking” “Index - specific locking” • Logical row • Index entry + gap to next (lower) index entry • “Index - specific locking” in primary data structure What about non-key updates? February 27, 2015 Orthogonal key-value locking 10

Microsoft SQL Server lock modes February 27, 2015 Orthogonal key-value locking 11

Orthogonal key-range locking • Combine IS+S+Ø into SØ (“key shared, gap free”) Reduce lock manager invocations by factor 2-3 • Strict application of standard techniques No new semantics S X SØ ØS XØ ØX SX XS S ok ok ok Automatic derivation X Gap SØ ok ok ok ok ok _Ø _S _X ØS ok ok ok ok ok Key Ø_ Ø ØS ØX XØ ok ok S_ SØ S SX ØX ok ok X_ XØ XS X SX ok XS ok February 27, 2015 Orthogonal key-value locking 13

Prior work leaves problems to solve: • ARIES/KVL is complex and locks entire lists Unable to lock individual entries: reduced concurrency Poor support for phantom protection • ARIES/IM locks much more than needed Keys and gaps in multiple indexes Very poor support for phantom protection • SQL Server & Orthogonal KRL lock each entry Many lock manager calls: late failure, lock escalation Poor precision for equality queries February 27, 2015 Orthogonal key-value locking 19

Goals to achieve • Lock a distinct key value, i.e., an entire list  In a single lock manager invocation Including actual and possible list entries ⇒ key-value locking • Lock a key value or a gap or both  In a single lock manager invocation ⇒ orthogonal lock modes • Lock individual instances within a list  At least enable some concurrency within a list  February 27, 2015 Orthogonal key-value locking 20

New technique: partitioned lock lists • A list of entries per key Examples (4 partitions) • Hash partitioning function • Delete key value “Joe”, row id 9 on list entries Lock (“Joe”, hash(9) % 4) or “Joe” in ØXØØØ • A lock mode per partition • Select key value “Joe” • A lock mode for the gap Lock “Joe” in SSSS Ø February 27, 2015 Orthogonal key-value locking 21

Case studies: example table • Table with Emp First Zip Phone No Name Code unique and non-unique columns and indexes 1 Mike 42062 4567 • Primary index on 2 Gary 10032 1122 primary key: EmpNo 3 Joe 46045 9999 • Unique secondary index: 4 Larry 53704 5347 Phone 5 Joe 67882 5432 • Secondary indexes: … ZipCode, FirstName February 27, 2015 Orthogonal key-value locking 31

Absence in a non-unique index • ARIES/KVL First Count EmpNo values… Name All Joe values + gap to Gary Gary 1 2 • ARIES/IM Joe 2 3, 5 Row 3 + all lower gaps Larry 1 4 • SQL Server KRL Mike 1 1 Joe:3 + gap to Gary:2 • Orthogonal KRL Select… FN=“Henry” Gap above Gary:2 Phantom protection • Orthogonal KVL Gap above Gary (below Joe) February 27, 2015 Orthogonal key-value locking 33

Selection in a non-unique index • ARIES/KVL First Count EmpNo All Joe values + gap to Gary values… Name • ARIES/IM Gary 1 2 Rows 3, 5, 4, Joe 2 3, 5 + 3 gaps in each index Larry 1 4 • SQL Server KRL Mike 1 1 Joe:3, Joe:5, Larry:4, + 3 gaps • Orthogonal KRL Select… FN=“Joe” Gap above Gary:2, Successful selection Joe:3, Joe:5, with gaps • Orthogonal KVL All partitions of Joe, no gaps February 27, 2015 Orthogonal key-value locking 34

Range queries in a non-unique index • ARIES/KVL First Count EmpNo Joe, Larry, + 2 gaps values… Name • ARIES/IM Gary 1 2 Rows 3, 5, 4, 1: 4 rows Joe 2 3, 5 + 4 gaps in each index Larry 1 4 • SQL Server KRL Mike 1 1 Joe:3, Joe:5, Larry:4, Mike:1: 4 keys + gaps Select… FN between • Orthogonal KRL “Joe” and “Larry” Gary:2, Joe:3, Joe:5, Larry:4: 3 keys + 4 gaps • Orthogonal KVL Joe, Larry: 2 keys + 1 gap February 27, 2015 Orthogonal key-value locking 35

Non-key updates • ARIES/KVL First Emp Zip Name No Code X on Joe including gap Gary 2 10032 • ARIES/IM Joe 3 46054 X on row 3 and all gaps Joe 5 67882 • SQL Server KRL Larry 4 53704 X on Joe:3 and gap Mike 1 42062 • Orthogonal KRL X on Joe:3, not on gap Update ZipCode=… • Orthogonal KVL where EmpNo=3 X on (Joe, hash (3) % k) February 27, 2015 Orthogonal key-value locking 36

Deletion in a non-unique index • ARIES/KVL First Count EmpNo values… Name X on Joe, no ghost Gary 1 2 • ARIES/IM (via ghost) Joe 2 3, 5 X on row 3 and all gaps Larry 1 4 • SQL Server KRL Mike 1 1 X on Joe:3 and gap • Orthogonal KRL Delete… EmpNo= 3 X on Joe:3, not on gap • Orthogonal KVL X on (Joe, hash (3) % k) February 27, 2015 Orthogonal key-value locking 37

Insertion of an additional instance • ARIES/KVL First Count EmpNo IX on Joe + gap values… Name • ARIES/IM Gary 1 2 Instant X on 4 + X on 6 Joe 2 3, 5 • SQL Server KRL Larry 1 4 Instant X on Larry:4 + Mike 1 1 X on Joe:6 • Orthogonal KRL Insert… ( 6 , “ Joe ”, …) Test ØX on Joe:5, then XØ on ghost Joe:6 • Orthogonal KVL X on (Joe, hash (6) % k) February 27, 2015 Orthogonal key-value locking 38

Insertion of a new key value • ARIES/KVL: complex! First Count EmpNo values… Name • ARIES/IM: many gaps! Gary 1 2 • SQL Server KRL Joe 2 3, 5 Instant X on Joe:3 Larry 1 4 X on Henry:7 Mike 1 1 • Orthogonal KRL Check gap above Gary:2 Insert… ( 7 , “ Henry ”, …) X on ghost Henry:7 • Orthogonal KVL Check gap above Gary X on (Henry, hash (7)) February 27, 2015 Orthogonal key-value locking 39

Performance • TPC-C customer table Shore-MT with many performance improvements Non-unique secondary index • Ghost records on (w_id, d_id, last, first, id) 3,000 customers per • System transactions warehouse & district • Foster b-trees 3 customers per last name • Buffer pool with swizzled • HP workstation parent-to-child pointers HP Z820 Xeon • Log with flush pipeline & 2 × 8 cores, 3.4 GHz, 128 GB consolidation array • Read-after-write lock management February 27, 2015 Orthogonal key-value locking 44

Read-only cursor, equality predicate February 27, 2015 Orthogonal key-value locking 45

Updates only – lots of contention February 27, 2015 Orthogonal key-value locking 47

Read-write workload with contention February 27, 2015 Orthogonal key-value locking 48

Orthogonal key-value locking Techniques Comparisons • Hybrid of traditional KVL • Fewest lock requests and orthogonal KRL Better than ARIES/IM, SQL Server, orth KRL • Fixed # of partitions in • Precise locks for queries each list of bookmarks Better than ARIES/KVL, /IM, • A single request can lock: SQL Server, orth KRL a key value’s entire list – • High update concurrency all possible instances Better than ARIES/KVL, /IM a single partition within a list • Fewest lock modes a gap between distinct keys any combination, eg list + gap Better than SQL Server, orthogonal key-range locks February 27, 2015 Orthogonal key-value locking 49

Why research granularity of locking? Bad reputation of locking Recommendation • Too much overhead • Strict serializability Is this actually true? Easy application development • Too little concurrency • Read-only transactions in snapshot isolation: Poorly chosen lock modes commit point = start-of-tx Poorly chosen granules • All other transactions lock: Excessive lock duration • Weak isolation levels commit point = end-of-tx Minimal lock duration Dirty read, read committed Optimal lock modes Eventual consistency Optimal granularity of locking February 27, 2015 Orthogonal key-value locking 50

Design Origin Granularity Comments ARIES/KVL IBM 1990 Distinct key value All possible instances Incl gap to next lower “Instant duration locks” ARIES/IM IBM 1992 Logical row Heap record + “data only all index entries + locking” gaps to next lower ARIES/IM Index entry Incl gap to next lower per index Key-range DEC 1993 Index entry First key-gap separation “Insertion” lock mode locking Range Cartesian product – Orthogonal Msft 2006 Index entry key-range Gap simple derivation of locking locks & compatibility Orthogonal HP 2013 Distinct key All possible instances key-value Partition Hierarchy: key value + locking Gap partitions To be done… Orthogonal HP 2015 Logical row row locking Index entry(ies) Gap or gaps February 27, 2015 Orthogonal key-value locking 51