Locking Key Ranges with Unbundled Transaction Services

1. Locking Key Ranges with Unbundled Transaction Services David Lomet Microsoft Research Mohamed Mokbel University of Minnesota

2. Talk Outline • Unbundled Transaction Services • Challenge: Key Range Locking • Using Conventional LockingManagers for Unbundled TransactionServices • The Partition Lock Protocol • Performance Analysis • Conclusion and Summary

3. Present Day engine Architecture • DB Kernel Provides Tightly Integrated Code • Access methods • Data caching & persistence • Concurrency control • Recovery Query Processing DB Kernel Concurrency Control Recovery Access Methods Caching Split between Query Processing and DB Kernel

4. Big Picture of New Kernel Architecture* Query Processing • Transaction Component (TC) • Concurrency control and recovery • Has a logical level knowledge about keys and records • No knowledge of pages, buffers, physical structure • Data Component (TC) • Access methods & cache • Provides atomic logical operations • No knowledge of how they are grouped in user transactions Transaction Component (TC) Concurrency Control Recovery Data Component (DC) Access Methods Caching DB Kernel • * David Lomet, Alan Fekete, Gerhard Weikum, and Mike Zwiling, Unbundling Transaction Services in the Cloud. In CIDR 2009 Split the DB Kernel to Transaction component (TC) and Data component (DC) 4

5. Why might this be interesting • Multi-core Architecture • Run TC and DC on separate cores to better exploit parallelism and reduce instruction cache misses Query Processing • Extensible DBMS • Provider of new access method does not need to deal with transactional concurrency control & recovery • Architectural advantage whether this is user or system builder extension • Cloud Data Store with Transactions • TC coordinates transactions across distributed collection of DCs • Can add TC to data store that already supports atomic operations on data; without changing, perhaps, the data store Transaction Component (TC) Concurrency Control Recovery Data Component (DC) Access Methods Caching DB Kernel

6. Talk Outline • Unbundled Transaction Services • Challenge: Key Range Locking • Using Conventional LockingManagers for Unbundled TransactionServices • The Partition Lock Protocol • Performance Analysis • Conclusion and Summary

7. Challenge: Key Range Locking UPDATE Student SET GPA= GPA+0.25 WHERE ID ≥ 10 and ID ≤ 40 • Required Locks: Need to lock all the records in the range R = [10,40] •  • New Kernel Architecture • The TC has no idea about the keys in R and thus has no way of requesting any lock on any specific key • There is a need for a new locking protocol that allows the TC to lock the required range without knowing the internal keys Present Day Engine Architecture • The storage engine does know all the keys within R and can request a lock for each single key in R

8. Key Range Locking in SQL Server • Key range locking requires locking each single key in the requested range, and thus, cannot be used in the new DB Kernel architecture as it requires the knowledge of the keys inside the range • We are not planning to replace key range locking. Our proposal is to supplement it to: (a) avoid locking each single key and (b) work without the knowledge of keys in the range SQL Server lock manager has special range “modes” that are used to lock ranges between keys to prevent phantoms A key range lock mode is interpreted by the storage engine as a lock on the key and a lock on the interval between the key and the next key

9. Talk Outline • Unbundled Transaction Services • Challenge: Key Range Locking • Using Conventional LockingManagers for Unbundled TransactionServices • The Speculative Visit Approach • The Table Lock Approach • The Partition Lock Protocol • Performance Analysis • Conclusion and Summary

10. The Speculative Visit Approach TC Speculative visit Certification visit DC • Main idea: • The TC takes a speculative visit to the DC to find the key that requires locking. • The TC acquires a lock on the returned key from the DC • The TC takes a certification visit to the DC to confirm that there are no changes between the two visits • If there is no difference between the two visits, the record access is completed, otherwise, the DC returns failure and the protocol is restarted

11. The Speculative Visit Approach • Enhancement: • Combine the certification visit with the speculative visit for the next key • Advantage: • No change in the lock manager • High concurrency • Disadvantage • Data records are accessed at least twice • Concurrent transactions may result in having multiple visits to the same record before being able to acquire a lock

12. The Table Lock Approach • Advantage: • No change in the lock manager. Very simple to implement • Disadvantage • Very low concurrency as we turn the system off by locking the whole table • Main idea: Acquire a lock on a larger granule than a record • We cannot go for pages as we would have the same problem as with the records (the TC does not know what are the pages to be accessed) • So, we need to lock the whole table in order to safely access the records

13. Talk Outline • Unbundled Transaction Services • Challenge: Key Range Locking • Using Conventional LockingManagers for Unbundled TransactionServices • The Partition Lock Protocol • Performance Analysis • Conclusion and Summary

14. The Partition Covering Lock • Idea: Have the range as a “resource” (similar to keys, pages, and tables) rather than a “mode” (similar to Key Range locking in MS SQL Server) • The TC can request a lock on a range “resource” from the DC • Each table is partitioned to a set of range “resources” • There are several alternatives for this partitioning • The boundary of the range resource does not have to be keys in the table. • This means that the TC can know these boundaries without the need to lock at the data

15. The Partition Locking Protocol • The TC maps the range R=[kl,ku], requested by a query, into the set of partition resources on which the TC can request locks….(The TC does know the available range resources) • The TC requests a lock on each partition before accessing its records • This lock acts as a cover lock in which the TC can talk safely with the DC to discover the records inside the locked range resource • When multiple partitions are required to cover the range R (the most common case), we lock the partitions one at a time, as we access records sequentially within R.

16. Partition Locking Protocol: Example S Lock S Lock S Lock S Lock IS Lock Boundary Partition IS Lock Internal Partitions Boundary Partition S Lock Ku Kl • Preventing Phantoms at the end points of the range • Locking a key and its preceding range • Indicating the end of the partition • When there are no more records within the partition, the DC indicates this by providing an end record sign. • Preventing phantoms between partitions • Need to place a key range lock on the first key of an internal partition before demoting the partition lock on the boundary partition • A shared lock (S) is requested on a range of values R=[kl,ku] under the SERIALIZABLE isolation level.

17. Talk Outline • Unbundled Transaction Services • Challenge: Key Range Locking • Using Conventional LockingManagers for Unbundled TransactionServices • The Partition Lock Protocol • Performance Analysis • Conclusion and Summary

18. Performance Analysis • Locking Protocols • Key Lock: Conventional lock managers • Though it is not applicable to unbundled transaction services • The Speculative Visit protocol • The Table Lock protocol • The Range Lock approach • Performance measures • Concurrency control overhead due to locking and reading the data in the requested range • Loss of concurrency incurred by holding locks on resources for some period of time

19. Performance Analysis Concurrency Control Overhead

20. Performance Analysis Loss of Concurrency

21. Talk Outline • Unbundled Transaction Services • Challenge: Key Range Locking • Using Conventional LockingManagers for Unbundled TransactionServices • The Partition Lock Protocol • Performance Analysis • Conclusion and Summary

22. Conclusion and Summary • Unbundled transaction services call for a new locking protocol to handle locking a range of data • Conventional lock managers can be used to support unbundled transaction services, yet, with very inefficient performance • Speculative visit protocol • Table lock protocol • The partition locking protocol is proposed to support efficient key range locking for unbundled transaction services • The main idea is to add a new range resource in the locking hierarchy; in which the TC has a fair knowledge about it • Analytical models show that the new proposed partition locking protocol has a much lower locking overhead and provides better concurrency than other locking protocols

23. Questions..?

24. Partition Locking Protocol: Dealing with Empty Partitions S Lock S Lock IS Lock S Lock S Lock Ku Kl • Internal partitions • The locking protocol is unchanged • Final boundary partition • Treat the empty end partition as an internal partition • Initial boundary partition • Demote the partition lock to an intention lock, but need to ensure that the first record in the range has a key range lock to protect this early part of the range