SQL Access Patterns for Optimistic Concurrency Control

TL;DR

This paper introduces SQL access patterns and a Row Version Verifying discipline to enable optimistic concurrency control in relational databases, improving transaction reliability in unreliable communication scenarios.

Contribution

It proposes a new SQL access pattern framework and RVV discipline to implement OCC in DBMSs lacking native support, with practical guidelines for developers.

Findings

RVV reduces lost updates in unreliable communication environments.

SQL access patterns provide a general guideline for implementing OCC.

Demonstrated applicability on Oracle, DB2, and SQLServer.

Abstract

Transaction processing is of growing importance for mobile and web applications. Booking tickets, flight reservation, e-Banking, e-Payment, and booking holiday arrangements are just a few examples. Due to temporarily disconnected situations the synchronization and consistent transaction processing are key issues. To avoid difficulties with blocked transactions or communication loss several authors and technology providers have recommended to use Optimistic Concurrency Control (OCC) to solve the problem. However most vendors of Relational Database Management Systems (DBMS) implemented only locking schemes for concurrency control which prohibit the immediate use of OCC. We propose Row Version Verifying (RVV) discipline to avoid lost updates and achieve a kind of OCC for those DBMS not providing an adequate non-blocking concurrency control. Moreover, the different mechanisms are categorized as access pattern in order to provide programmers with a general guideline for SQL databases. The proposed SQL access patterns are relevant for all transactional applications with unreliable communication and low conflicting situations. We demonstrate the proposed solution using mainstream database systems like Oracle, DB2, and SQLServer.