Analysis of locking policies in database management systems

Quantitative analysis of locking mechanisms and of their impact on the performance of transactionnal systems have yet received relatively little attention. Although numerous concurrency mechanisms have been proposed and implemented, there is an obvious lack of experimental as well as analytical studies of their behaviour and their influence on system performance. We present in this paper an analytical framework for the performance analysis of locking mechanisms in transactionnal systems based on hierarchical analytical modelling. Three levels of modelling are considered: at level 1, the different stages (lock request, execution, blocking) transactions of through during their life-time are described; the organization and operations of the CPU and I/O resources are analysed at level 2; transaction's behaviour during their lock request phase is analysed at modelling level 3. This hierarchical approach is applied to the analysis of a physical locking scheme involving a static lock acquisition policy. A simple probabilistic model of the transaction behaviour is used to derived the probability that a new transaction is granted the locks it requests given the number of transactions already active as a function of the granularity of the database. On the other hand, the multiprogramming effect due to the sharing of CPU and I/O resources by transactions is analysed using the standard queueing network approaches and the solution package QNAP. In a final step, the results on the blocking probabilities and the multiprogrammin effect are used as input of a global performance model of the transactionnal system. Markovian analysis is used to solve this model and to obtain the throughput of the system as a function of the data base granularity and other parameters. The results obtained provide a clear understanding of the various factors which determine the global performance, of their role and improtance. They also raise many new issues which can only be solved by further extensive experimental and analytical studies and show that two particular topics deserve special attention: the modelling of transaction behaviour and the modelling of locking overheads.