Degrees of Transaction Isolation in SQL*Cache: A Predicate-based Client-side Caching System

Abstract

A caching scheme that uses query predicates to cache data on the client-side in a client-server relational database system was presented in [15]. The client-side cache (henceforth referred to as a SQL*Cacheloads query results dynamically in the course of transaction execution, and formulates a cache description based on the query predicates. SQL*Cache is associative in nature, in that it supports content-based reasoning and local execution of SQL queries on the cached data, thereby attempting to reduce query response times and increase server throughput. Local caching involves cache consistency maintenance and complicates transaction concurrency control -- in this paper, we examine serializability questions that arise in such a system. We first analyze the issues in supporting different (0/1/2/3) degrees of isolation that client transactions may specify, and describe algorithms to achieve these isolation levels. Then, we propose new levels of isolation that take into account the distributed nature of transaction execution, in terms of the lag of locally cached data with respect to the server database and discrepancies between local and remote reads. The SQL*Cache scheme is currently being implemented using a main memory database as the client-side cache, and commercial database servers as the backends.