Interactions between query optimization and concurrency control

Abstract

The author argues the importance of and need for taking into consideration concurrency control related issues in making query optimization and query processing decisions. Such considerations are very important not only for attaining good performance, but also for assuring the correctness of the results returned to the users under certain circumstances. Some of the topics that they deal with include degrees of consistency or isolation levels (repeatable read, cursor stability, . . .), lock escalation, blocking of results and use of multiple indexes for a single table access (i.e. index AND/ORing). They identify some of the pieces of information relating to locking that must be available to the optimizer for it to make intelligent decisions. They also identify some situations in which locking can be avoided by taking advantage of the isolation level of the query being executed.<>