SQL Azure's concurrency control relies on multi-versioning to prevent readers and writers from blocking each other and on in-memory row locks to prevent multiple writers modifying the same row. If the number of in-memory locks exceeds a threshold, then to reduce memory used for locking, table-level lock escalation occurs which severely reduces concurrency. This paper presents a technique called transaction-id locking that drastically reduces the number of in-memory locks and eliminates lock escalation. It also describes another technique called lock after qualification where rows are qualified without locking thereby letting concurrent transactions interested in mutually exclusive sets of rows execute without blocking each other. Optimized locking combines these two techniques with the prior scheme of in-memory row locks. This combination to improve common isolation levels (like Read Committed Snapshot Isolation) while retaining support for Serializable isolation level in a developer-friendly manner distinguishes this work from prior art. The paper presents in detail this new scheme which required changes in both the storage engine and the query processing engine. It also presents the results of deploying optimized locking to more than eleven million SQL databases in Azure.

Azure SQL Database and the upcoming release of SQL Server introduce a novel database recovery mechanism that combines traditional ARIES recovery with multi-version concurrency control to achieve database recovery in constant time, regardless of the size of user transactions. Additionally, our algorithm enables continuous transaction log truncation, even in the presence of long running transactions, thereby allowing large data modifications using only a small, constant amount of log space. These capabilities are particularly important for any Cloud database service given a) the constantly increasing database sizes, b) the frequent failures of commodity hardware, c) the strict availability requirements of modern, global applications and d) the fact that software upgrades and other maintenance tasks are managed by the Cloud platform, introducing unexpected failures for the users. This paper describes the design of our recovery algorithm and demonstrates how it allowed us to improve the availability of Azure SQL Database by guaranteeing consistent recovery times of under 3 minutes for 99.999% of recovery cases in production.