Analysis of locking behavior in three real database systems

Abstract.Concurrency control is essential to the correct functioning of a database due to the need for correct, reproducible results. For this reason, and because concurrency control is a well-formulated problem, there has developed an enormous body of literature studying the performance of concurrency control algorithms. Most of this literature uses either analytic modeling or random number-driven simulation, and explicitly or implicitly makes certain assumptions about the behavior of transactions and the patterns by which they set and unset locks. Because of the difficulty of collecting suitable measurements, there have been only a few studies which use trace-driven simulation, and still less study directed toward the characterization of concurrency control behavior of real workloads. In this paper, we present a study of three database workloads, all taken from IBM DB2 relational database systems running commercial applications in a production environment. This study considers topics such as frequency of locking and unlocking, deadlock and blocking, duration of locks, types of locks, correlations between applications of lock types, two-phase versus non-two-phase locking, when locks are held and released, etc. In each case, we evaluate the behavior of the workload relative to the assumptions commonly made in the research literature and discuss the extent to which those assumptions may or may not lead to erroneous conclusions.

[1]  Samuel DeFazio,et al.  Diversity in database reference behavior , 1989, SIGMETRICS '89.

[2]  Juan Rodriguez-Rosell,et al.  Empirical Data Reference Behavior in Data Base Systems , 1976, Computer.

[3]  Alan Jay Smith,et al.  Characterization of Contention in Real Relational , 1991 .

[4]  Alexander Thomasian,et al.  Analysis of database performance with dynamic locking , 1990, JACM.

[5]  Lawrence A. Rowe,et al.  Cache consistency and concurrency control in a client/server DBMS architecture , 1991, SIGMOD '91.

[6]  Miron Livny,et al.  Load control for locking: the “half-and-half” approach , 1990, PODS '90.

[7]  Philip S. Yu,et al.  Buffer Analysis for a Data Sharing Environment with Skewed Data Access , 1994, IEEE Trans. Knowl. Data Eng..

[8]  Alan Jay Smith,et al.  Sequentiality and prefetching in database systems , 1978, TODS.

[9]  Miron Livny,et al.  Distributed Concurrency Control Performance: A Study of Algorithms, Distribution, and Replication , 1988, VLDB.

[10]  C. J. Date.  An Introduction to Database Systems, Volume I, 5th Edition , 1986 .

[11]  Michael Stonebraker,et al.  The Performance of Concurrency Control Algorithms for Database Management Systems , 1984, VLDB.

[12]  Michael Stonebraker,et al.  Performance analysis of a relational data base management system , 1979, SIGMOD '79.

[13]  Philip S. Yu,et al.  On the analytical modeling of database concurrency control , 1993, JACM.

[14]  J. Stapleton.  Introduction to Probability Theory and Statistical Inference , 1970 .

[15]  Leonard Kleinrock,et al.  Theory, Volume 1, Queueing Systems , 1975 .

[16]  Donald E. Knuth,et al.  The art of computer programming, volume 3: (2nd ed.) sorting and searching , 1998 .

[17]  Y. C. Tay,et al.  Locking performance in centralized databases , 1985, TODS.

[18]  J. T. Robinson,et al.  On coupling multi-systems through data sharing , 1987, Proceedings of the IEEE.

[19]  Gerhard Weikum,et al.  Conflict-driven load control for the avoidance of data-contention thrashing , 1990, [1991] Proceedings. Seventh International Conference on Data Engineering.

[20]  Alexander Thomasian,et al.  On a more realistic lock contention model and its analysis , 1994, Proceedings of 1994 IEEE 10th International Conference on Data Engineering.

[21]  Miron Livny,et al.  Concurrency control performance modeling: alternatives and implications , 1987, TODS.

[22]  Andreas Reuter,et al.  Transaction Processing: Concepts and Techniques , 1992 .

[23]  Dennis Shasha,et al.  The performance of current B-tree algorithms , 1993, TODS.

[24]  Philip S. Yu,et al.  On Coupling Partitioned Database Systems , 1986, ICDCS.

[25]  M. F. Fuller,et al.  Practical Nonparametric Statistics; Nonparametric Statistical Inference , 1973 .

[26]  Jim Gray,et al.  The convoy phenomenon , 1979, OPSR.

[27]  Wing Shing Wong,et al.  Performance Analysis of Locking and Optimistic Concurrency Control Algorithms , 1985, Perform. Evaluation.

[28]  C. J. Date.  An Introduction to Database Systems, Volume II , 1980 .

[29]  Hamid Pirahesh,et al.  ARIES: a transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging , 1998 .

[30]  Alexander Thomasian,et al.  Two-phase locking performance and its thrashing behavior , 1993, TODS.

[31]  A. Inkeri Verkamo.  Empirical results on locality in database referencing , 1985, SIGMETRICS 1985.

[32]  Philip S. Yu,et al.  Modelling of centralized concurrency control in a multi-system environment , 1985, SIGMETRICS '85.

[33]  Michael Stonebraker,et al.  Locking granularity revisited , 1979, ACM Trans. Database Syst..

[34]  Gerhard Weikum,et al.  Conflict-driven load control for the avoidance of data-contention thrashing , 1990, [1991] Proceedings. Seventh International Conference on Data Engineering.

[35]  A RoweLawrence,et al.  Cache consistency and concurrency control in a client/server DBMS architecture , 1991 .

[36]  C. J. Date,et al.  A guide to DB2 (4th ed.) , 1993 .