Modelling Database Concurrency Control Algorithms using a general Purpose Performance Evaluation Tool

deposit_hagen

Publikationsserver der Universitätsbibliothek

Mathematik und

Informatik

Informatik-Berichte 42 – 02/1984

Heinrich A. Scholten

Modelling Database Concurrency

Control Algorithms using a general

Purpose Performance Evaluation Tool

Modelling Database Concurrency Control Algorithms using a general Purpose

Performance Evaluation Tool

(•)

University of Dortmund Dept. of Camputer Science PO Box S00 500

D-4600 Dortmund SO W. Germany

Abstract

J

anuary 1984

Reinhard Augustin UI r i eh Praede 1

Heinrich A. Schotten

(*)

(+) (*)

(+)

University of Hagen

Dept. of Computer Science PO Box 940

D-S800 Hagen 1 W. Gennany

We present a performance evaluation study comparing two often-mentioned con- c u r r e n c y c o n t r o I a I g o r i t hm s , n am e I y ( r , x ) - 1 o c k s a n d OP T I M I ST I C c o n c u r r e n c y control as proposed by Kung and Robinson. This study was conducted by applying a g e n e r a I p u r p o s e p e r f o r m a n c e e v a I u a t i o n t o o 1 , COP E. Th e r e s u I t s s h o w t h a t OPTIMISTIC concurrency control leads to worse database management system performance than LOCKII\G methods under a wide variety of workload conditions.

The appl ication of COPE pointed out how such a study in a very special ized field can be supported by a general tool, and what a general tool is missing for an appl ication to special ized problems.

This study was partial ly supported by IBM, Germany.

1. lntroduction

The study presented in this paper was conducted with two main intentions:

First to evaluate the performance of several often-used concurrency control algorithms with respect to changing workload conditions, and second to apply a general performance evaluation tool to the very specialized field of database per formance.

Previously published papers on this area have shown that the first aspect has been dealt with several times (see, for example, /MeNa82/, /DeCa82/, /SaSp83/, /KiLa83/), whereas the second aspects appears tobe negl ecfed. Each study considered an own, special view of the problem, a unifying approach (such as queueing networks for the evaluation of computing systems) still lies ahead.

Although we don¹t claim to close this gap, we hope that the appl ication of the general purpose performance evaluation tool COPE wil I help to gain some in- sights into the nature of the problem world.

This paper is organized as fol lows: The next chapter wi 11 introduce the envi- ronment that we assumed for our eval uation of the concurency control al go- rithms. These algorithms wi 11 be discussed briefly with the purpose of clari- fying their representation in the simulation. Chapter three wi 11 present those performance aspects that we considered important for comparing the two algo- rithms and for evaluating them under various changing workload conditions.

Chapter four will show the two models underlying the simulation, separated into the static (system) and dynamic (workload) description. Sections five and six wil I discuss the results gained from several series of experiments and the experiences from applying a general purpose performance evaluation tool to the very special ized problems of concurrency control performance evaluation.

Modelling Concurrency Control Algorithms Page 2

2. System Environment

Concurrency Control is the central modul within the database management system (DBMS) to manage parallel access of users to the database.

In the last years many techniques of controlling the parallel access to the database have been developed. They can be classified in three groups:

- locking /EGLT76/, /Gray79/

- Time s t amp in g

- Optimistic Concurrency Control /KuRo79/, /UnPS83/

In this section we want to discuss the terms, which are necessary to under- stand this paper.

The transaction is the central concept within concurrency control. A transac- tion is a sei f-contained sequence of atomic operations, the so-cal I ed actions.

The action is the smal lest unit of work within the. database management system (e.g. ¹¹read an object x^{11) .} Transactions have to preserve the consistency of the system: they have to transfor~ one consistent state of the database into a n e w c o n s i s t e n t s t a t e. Th e r e f o r e a t r an s a c t i o n h a s t o b e a t om i c i n t h e s e n s e that when al I actions are done, then the transaction wi 11 comnit, or none of the effects of the transaction wi 11 survive, then the transaction wi 11 abort.

Uncontrol led parallel access of transactions could violate the consistency of the"database /EGLT76/. Therefore ruf es for the scheduf ing of the transactions ha ve to be def i ned. For th i s reason the not i on of ser i a I i zabi I i ty has been defined. The parallel execution of the actions of transactions

r

_{1 ••• T}0 is said tobe serializable, if there is a serial sequence of execution of their actions, which has the same effects for the database and produces the same results.

The serial izabi I ity of parallel transactions is guaranteed by synchronisation algorithms.

In the next section we want to present the LOCKlf\JG method and the OPTIMISTIC concurrency control. Timestamp based methods are used in distributed database systems and therefore not discussed here.

The most popular method for preserving the serial izabi I ity is the LOCKING method /Gray79/. Every transaction has to lock the database objects it wants to work with. lf it cannot get the lock, the transaction has to wait until the lock has been released. The rule of aquisition and release of locks is cal led lockprotocol. The use of the two-phase-lockprotocol produces only serial izable sequences of execution of actions of parallel transactions, so-cal led sche- dules. A lockprotocol is cal led two-phased, if every transaction consists of two phases: the growing phase and the shrinking phase. Locks can be aquired within the growing phase. Once a lock has been released, a transaction enters

its shrinking phase, where no further locks can be aquired.

There are some degrees of freedomwithin the two-phase-lockprotocol. All locks may be aquired at the start of the transaction (preclaim) or gradual ly within the growing phase (claim-as-needed). Correspondingly al I locks may be released at the end of the transaction or gradual ly during the shrinking phase.

Some combinations of lock aquisition pol icy and lock release pol icy could cause problems. Therefore the notion of strict two-phase-lockprotocol has been developed. Within this protocol al I locks of a transaction are released in one a c t i o n a t t h e end o f t h e t r an s a c t i o n , t h e a q u i s i t i o n o f t h e I o c k s rna y b e d o n e gradual ly during the growing phase.

One problem for the LOCKING strategies is the deadlock problem. Adeadlock occurs, if some transactions are mutual ly waiting for locks, which are held by another transaction. To break up the deadlock, at least one transaction has to be aborted, cancel ted, rol led back and restarted.

Modelling Concurrency Control Algorithms Page 4

This paper discusses the performance of the basic (r,x)-lockprotocol. Every transaction uses readl ocks (r-locks) for the objects it wants to read and exclusive locks (x-locks) for al I objects it wants to modify. R-locks for an object may be granted for many transactions, x-tocks only for one at a time.

As the strict two-phase-lockprotocol is considered, the system has to deal w i t h de ad I o c k s • T o b r e a k u p a d e ad I o c k , t h e t r an s a c t i o n w i t h t h e I e a s t n umb e r of held r- and x-locks wi 11 be cancel led (see /MuKr77/). The waiting transac- tions are scheduled according to the FIFO-rule.

Kung and Robinson /KuRo79/ notice that LOCKING may be too costly in some cases. They propose OPTIMISTIC concurrency control, where the only means of preserv i ng the cons i stency of the database i s backup and restart of transac- t i ons. We want to give abrief description of the basic OPTIMISTIC concurrency cont ro I scheme.

A transaction consists of a read phase, a validation phase, and a write phase.

In the read phase the transaction reads the required database objects, write o p e r a t i o n s a r e p e r forme d o n I o ca I c o p i e s o f t h e da t a. 1 n t h e v a I i da t i o n p h a s e a serial izabi I ity check is performed. lf val idation succeeds, the objects mo d i f i e d b y t h e t r an s a c t i o n a r e ma de g I o b a 1 • 1 f v a I i da t i o n f a i I s , t h e t r an s a c -

tion is rol led back and restarted. In the basic mechanism val idation and write forma critical (exclusive) section.

Validation proceeds as fol lows: For each transaction Ti concurrency control keeps track of the set R; of objects read by Ti, and the set W; of the objects written. lf a transaction validates successfully, it is assigned a transaction number t(T;)• To this purpose a transaction counter is maintained. Let tstart be the highest transaction number TNC at the start of Ti and tfinish the highest transaction number at the beginning of val idation. Then Ti performs t h e fo 1 1 ow i n g t e s t :

v a I i ^{d :} = t r ue;

f o r t ( T i ) f r om t s t a r t + 1 t o t f i n i ₅ h d o if

R-n

^W·

^f

^1/) then valid:= false;

J ¹

i f v a I i d t h e n w r i t e ; t ( T j ) : = TNC; . TNC : = TNC + 1 ; el se rol lback;

Further details can be found in /KuRo79/, where also somemodifications of this basic mechanism are suggested. This paper analyses the performance of this basic OPTIMISTIC concurrency control method. We did not consider poten- tial improvements I ike better val idation schemes (/UnPS83/).

3. Performance Criteria

The use of the simulation part of the performance evaluation tool COPE (/COPE84/) opened up the possibi I ity to eval uate many desired aspects of our model. We restricted our interest to those quantities that we considered the most appropriate to describe the quan·titative and qualitative effects of a concurrency control algorithm. A second aspect of our choice was the goal of compar i ng our study wi th others in the same area.

Our performance criteria can be divided in two major groups. ¹¹External ¹¹ mea- sures are those that can be observed directly by the user of the database system, such as turnaroundtime, and ¹¹internal ¹¹ measures, such as probabi'I ity of deadlock or nonval idation. The internal measures are of a great interest to the designer of a concurrency control mechanism and e.g. the instal lation manager of a DBMS applying this mechanism. Whereas "external" measures display the overal I behavior of a system, ¹¹ internal ¹¹ measures can help to identify weak points of an algorithm and/or a parameter setting. Nonetheless, internal measures are strongly related to the external measures since the internal behavior (e.g. probabi I ity of deadlock in a system using LOCKll'G) is the basis for the external behavior (e.g. turnaroundtime).

Modelling Concurrency Control Algorithms Page 6

3.1 Discussion of external Measures

Our first measure is the transaction turnaroundtime, i.e. the time between start and termination of a transaction, consisting of the time waiting for access to the DBMS, time within the DBMS itself and terminal think time. This quantity is directly visible to the users of a DBMS and often used as their criterion to describe the ¹¹qual ity of service¹¹ by a DBMS. Further on, the turnaroundtime may wel I be compared to the resul ts of other studies (e.g.

/MeNa82/, /DeCa82/) or even to analytical studies with queueing network model s of the DBMS. These queueing network models only offer the external measures as results.

The second external measure of our DBMS model is the transaction throughput of the model led system, i.e. the number of transactions processed per time unit.

This is a very important indicator for the manager of a DBMS instal lation, because it shows whether the capacity of the system is used wel I or whether some parts of it are over-/underloaded.

3.2 Discussion of internal Measures

While some other studies use only external measures to evaluate aDBMS, we have come to the conclusion that internal measures may be even more valuable as results of a DBMS analysis, because these results indicate directly the weak points of a system, which may be some parameter setting, the capacity of a processor or similar quantities. Since the final goal of any performance evaluation study should be the improvement or optimization of the system under consideration, the detection of weak points is a very helpful step in that direction.

Our first internal measure is the probabi I ity of deadlock (in case of LOCKII\G) or nonval idation (in case of OPTIMISTIC concurrency control). However, these

probabilities should not be used directly to compare LOCKING to OPTIMISTIC c o n c u r r e n c y c o n t r o 1 , b e c a u s e t h e e f f e c t s o f a c o n f I i c t a r e d i f f e r e n t fr om e a c h other, depending on the concurrency control method used. In our version, one of the transactions of the deadlock cycle is selected and cancel led, thus releasing al I the resources previously held and restarted. In case of optimis- tic concurrency control, a nonval idation causes the rol lback of the transac- tion and its restart, so that this transaction is. executed once again by the system (and can cause additional conflicts, naturally). More important than comparison to other studies, this quantity al lows a direct observation of the concurrency control mechanism. Thus, it deserves a special interest, because this probabi I ity can be considered as the basis of most of the other measures such a s turn a r o und t i me o r t ran s a c t i o n t h r o u g h p u t.

Th e n ext o n e o f o ur i n t er n a I m e a s ur es i s t h e de g r e e o f p a r a 1 1 e I i s m.

J

u s t a s the other internal measures, this quantity can be used to evaluate the effects of model inputs, such as the distribution of transaction types, distribution of transaction sizes or the distribution of a transactions accesses over the database. Given some fixed setting of these parameters, this quantity al lows a comparison to other evaluation studies, where similar results were reported (e.g. /DeCa82/, /Kila83/). lt should be noted that the degree of parallelism a I o n e i s not a me a s ur e f o r t h e qua I i t y o f s er v i c e b y a c o n c ur r e n c y c o n t r o 1 algorithm. A high degree of parallel ism can be very dangerous to a system if the conflict rate is high, because in this case the throughput wil I decrease considerably.

Our last measures are the lock waiting times and the number of transactions waiting for a lock (for the LOCKll'G-based concurrency control} or the waiting time for a val idation and the average number of transactions waiting for v a I i d a t i o n ( i n c a s e o f OP T I M I ST I C c o n c u r r e n c y c o n t r o 1 ) • 0 n c e a g a i n , t h e s e results should not be used to favorise one method against the other, because the effects of repeated waiting for a lock are different from those of waiting

Modelling Concurrency Control Algorithms Page 8

for val idation, because a transaction wi 11 usual ly request a considerable number of locks, whereas it wi 11 val idate a few time-s at most (in case of repeated nonval idation).

4. Description of the Simulation

4.1 Description of the Model

We have to discuss two different models in this chapter, because of the fact that we are investigating two concurrency control mechanisms:

- first the two-phase LOCKING (PESSIMISTIC) strategy together with the lock aquisition pol icy ¹¹claim-as-needed^{11 ,}

- and second the OPTIMISTIC strategy.

The results of the simulations, gained by several series of experiments, have.

to be comparab I e, therefore the hardware components and the work I oad must be t h e s ame i n bot h mo de I s.

The computing system, on which the database system is running, is not repro- duced in detai 1, because there is no need for an accurate model: the purpose of our investigation is to compare two concurrency control algorithms, and not to bui lt a very exact computer system model with al I its hard- and software components, or in other words our domain of interest are performance criteria such as transaction turnaroundtime, concurrency, lock waiting times and not CPU-, D I SK- o r c h an n e I u t i I i z a t i o n s a s des c r i b e d i n t h e p r e v i o u s c h a p t er.

The hardware components of the two models are:

- CPU

- disk storage drives - t ermi na I s

The service discipline for CPU- and disk subsystem is ¹¹processor-sharing¹¹ with an amo u n t o f o v e r h e ad i n c r e a s i n g w i t h t h e n umb e r o f t r an s a c t i o n s I o c a te d I n the unit at a time (state-dependent service). The database itsel f with al I its entities is another unit in our models. lts entities are numbered from 1 to D, defining the size of the database; they can be accessed by the transactions according to different access probabi I ities. A locking mechanism in con- junction with a deadlock detection algorithm for the PESSIMISTIC model and a set comparison procedure is implemented for the OPTIMISTJC approach.

Figure 1 shows the system description of the two model s, the workload speclfl- cations wi 11 be presented later. The separation of a model into a system and a workload specification is typical for the performance evaluation tool COPE

(/COPE84/), which is used to do the simulations.

SOURCE , - - - SINK

DATA BASE

' ( ) ~

TERMINALS

[

'( >-7

• 1

, ~ ( CPU ) - - - ' ISK ~RIVES ^{h . i}

l

C •

~

Figure 1: System description of tbe „dels

Let us now dlscuu the load paturns of our n,dells, idlefirmfirmi; 11:lhle •«l'irlk!lt01atlli t01tf the

syste111s

The

overal I workload of the PESSIMISTIC

appirt!lla"h muislt ~e e~IUlaP 11:@

the OPTIMISTIC one, the load patteirns are ditffeirermtt t!lltf "®llllirse-A.11 II urwiiit:e

Modelling Concurrency Control Algorithms Page 10

Both model s are represented as open networks, that means: transactions arrive in a model component named ¹¹source¹¹ and disappear in another component named

11s ink^11, when they have comp I eted or they are cance 1 1 ed due to a dead I ock in the PESSIMISTIC model. The cancel led transactions have another try, they wi 11 restart with a new birth, and wi 11 repeat al I of their service requests.

The same procedure is implemented for the OPTIMISTIC approach: transactions t h a t c an n o t v a I i d a t e a r e r e s t a r t e d , wh i c h i s a I i t t I e h an d i c a p fo r OP T I M I ST I C because of the fact that the amount of CPU-time and Disk-1O desired by the transaction is completely satisfied, in contrast to PESSIMISTIC, where the t r an s a c t i o n i s r e s t a r t e d a t t h e mome n t o f de ad I o c k , wh i c h ma y b e r a t h e r e a r I y

in the transaction I ife.

The time between the arrivals of the transactions is negative exponential ly distributed in both model s. There are two different types of transactions, so- cal led READER- and UPDATER transactions. The proportion of, READERs and UPDA- TERs is determined by their respective arrival rates. READER transactions request their desired database entities for read operations only, they don't write any data back onto the database in contrast to UPDATER transactions:

these transactions read their desired entities and write al I of them (!) back to the database. In case of PESSIMISTIC a two-phase locking protocol wi 11 be appl ied, and in case of OPTIMISTIC al I written entities are made global after a successful validation, which is an exclusive phase in the transaction life.

The maximum number of active transactions is I imited by another model parame- ter. lf the maximum number is reached, arriving and restarted transactions are queued, until the actual number of active transactions decreases to less than the maximum number. Let us now describe the I ife of a transaction in both models which is shown in figures 2 and 3.

--- --~.:. --~~. - . ~- - ·_1 __ _

( 1

>-·-•-·>

Birt.h ~--·-•),/ max. paral- f·-•)' Wait in

1 . _ _ _ / lelisrn 7 . 'i I a queue

' / '

~ - --r- - .

^{L _ _ ______ _}

; N I

1

V

Initial terminal think time

1

Wait in 1

a queue

!(----{

1

3 )- __ -: Wait in a queue

0 ^A

.

N ^{\,,;, 1 N}

1_.J___ Not ;---"---

/ Dead- / / Test /

I lock?

.< ..

^{••••1· x-LOCK?} _/ ¹ _r-LOCK? ^Test

_{/~~e-e -}

^{/ Not} _{-L_ _} ^Dead-l ~ ' : Y ^/

/ · free 1 /

'---..--·'

(*) Y (*) free ¹ free (*) (*)¹ y

....;.., - 1 - - - - 1

! Cancel · (*)

( 1 )

v

-..//

__

~

... · · · ~ u

i

1

~

1 Write to

; data base

l )/

^Another

/2' .. · • •~(

²

1 • . . . .

7

^{cycle ?}

f- - - (

³

' - - - '

. '

.' N '

r

'

_'-

1 Main terminal

~hink time

... ~- .· k---7

S(

¹

; Releas~ x-LOCK

(*) •

....

^{.... ·1--}

/Release r-LOCK / ¹

(*) 1

_ _ _ _ .J

y y

. . . ^. . . ^{.. ...} ---·

1

N 1...- -

---,

1 1

V'

Cancel

( 1 ) \V

Final terminal think time

~ ' - ' ~ 1

~ t h

~---1

Figure 2: Life cf a transaction in case cf LOCKING

- - way for READERS · • way for UPDATERS, , - , -way for both

(*) costs CPU-time (see Table 1)

Modelling Concurrency Control Algorithms

.- ·---·,

( 1 )- . - . - .

~

Birth \ . _ • _.

~ - - . . . : : , i L - - - ~ - - - ,

y Wait in a queue

( *) costs CPU-time

l

Compute transac- tion startnumber

N

Initial terminal think time

. ··---~

1

Wait in a queue

write to local data

y Any transac- tion valida- ting (exclu- sive phase) ? /

N

Read from data base

··-8

Another cycle?

N , N

y

--~

Main and terminating terminal think time

, .. 1-

·f-. - · - · -· -·-

Data base available j_

for exclu-

/N · - ·

si ve phase ? / y

LOCK!

data base 1

1

~ - - -

c ; l i d a t e 1

(*) '.

~

^{Wait1 in}

l a queue

1

( 1 )

11'-

$--"·

data base ^RELEASE

• 7

^{Cancel _}

_I

.

j RELEASE

1 \ data base

, 1

,,,

Compute transaction number, increment global transac- tion counter, make local write set global _(UPDATE~)

(see Table 1)

1

'I(

Death

Keep write set as long as neccessary

Death

Figure 3: Life of a transaction in case of OPTIMISTIC

- - way for READERS, , • way for UPDATERS, • _. -way for both

Page 12

We have used two special functions to compute the transaction size (= number of different entities accessed) and the probabi I ity of access to certain objects in the simulation.

In both cases (PESSIMISTIC and OPTIMISTIC) the number of entities accessed by the transaction is determined by the number of executions of a cycle:

- Compute an entity number - Read this entity

- Request CPU

Write this entity (only in case of UPDATER transactions)

At the end of each cyc I e the probab i I i ty for another cyc I e i s computed by the function

P(D;X;S)

In this function Dis the database size, Xis the number of entities al ready held by the transaction, and S is a user-definable parameter of tbe model to influence the transaction size. Figure 4 shows the effect of this ¹¹shape parameter¹¹ on the distribution of the random variable L which gives the trans- action size. For high values of S we have a very narrow curve, indicating that al I transactions wi 11 have simi lar and smal I sizes, whi le transaction size tends to vary more and more with decreasing S.

Modelling Concurrency Control Algorithms

ILII

~-

TR~NS.~Ci lON SIZE

^~-

^~~

^~-

1 s ^<

'I

26

.

9

.

4 .8 2. 9

.

2

.

U .b5 a:

0. 8 : 1.3 ~

1.8 % 2. 3 :(

2 .e ^~

4, 9 ::

!•.•

1

_____ J

1

Page 14

Figure 4: Probabi I ity of transaction size for varying shape parameter S (Database s i ze: 1000)

1 n o r d e r t o a v o i d s i mp I i f y i n g a s s ump t i o n s ab o u t t h e p r ob ab i I i t y o f a c c e s s t o the database entities such as an uniform distribution or some x-y-rule (x% of t h e a c c es s es a r e d i r e c t e d t o y% o f t h e e n t i t i es ) , we h a v e i n c I u de d i n o ur model a function to compute the probabi 1 ity of access to the database enti-

ties, which is abl e to cover a wide range of access distributions. A random number is drawn from anormal distribution with standard deviation V, which is another parameter of the mode 1, and mean D/2, and then rounded to an integer E. lf E is less than 1 or greater than D or the entity corresponding with Eis already held by the transaction, another random number is drawn, unti I the number of an available entity Eis computed. So the parameter V will control the variance of the access to the database entities, as shown in figure 5.

lll al

l'!

..

llt

oj

..

III

CS, ~

Vl

.

!:I!

<J'loj

...

u u

<

!:!;lll

~-

"'

!!!

..

!!!

..

~-

^{NC. OF-}

^~-

^{rnrm ;}

~-

^lil

^~-

1

V bü

ao

100

V 125

250 500

71.a BLa 911.a llLII

Figure 5: Probabil ity of access to the database entities dependent on para- meter V (Database size: 1000)

4.2 Parameters of the Model - possible Experiments

We can distinguish two different classes of model parameters: system- and workload parameters. We changed nothing but the workload parameters for the series of experiments, because the purpose of our investigati_on is to study two constant database system model s, with a LOCKING concurrency control algo- rithm and an OPTIMISTIC one on the other side, under different workloads.

The system parameters, held constant in al I experiments, are shown in table 1 together with their respective values. Some of them have already been discus- .sed in this paper.

Modelling Concurrency Control Algorithms Page 16

System Parameter Val ue

===================================================+==========================

1) Number of disk storage drives, uniform 3 distribution of disk access

---+---

2) Maximum degree of parallel ism in the DBMS 15

---+---

3) Number of entities in the database (D) 1000

---+---

4} * CPU-Time for a LOCK or UNLOCK operation 0.001 seconds

---+---

5)

*

CPU-Time for a deadlock test 0.005 seconds

---+---

6)

*

CPU-Time for a cancel and restart operation 0.025 seconds in case of a deadlock

---+---

7) + CPU-Time for comparing two elements of writesets

0.001 seconds

---+---

* PESSIMISTIC approach only + OPTIMISTIC approach only

Table 1: Systemparameters of the models

In contrast, the workload parameters are al tered in the series of experiments, which are discussed in chapter 5. Table 2 specifies the different workload p a r ame t er s i n de t a i 1 •

Variable Description

===============+==============================================================

V Variance of the access to the database entities, see fig. 5

---+---

5-R, S-U Transaction size shape parameters, see figure 4. Separate for READER and UPDATER transactions.

---+--- ---

ARR-R, ARR-U Mean time between the arrival of two transactions, negative exponential ly distributed. Separate for READER and UPDATER transactions.

---+---

TERM Mean terminal think time (initial, main and terminating) for READER transactions, negative exponential ly distributed.

---+---

CPU-R, CPU-U Mean CPU time between the access of two data base entities, negative exponential ly distributed. Separate for READER and UPDATER transactions.

---+---

10-R, 10-U Mean disk 10 time between the access of two database enti- ties, negative exponential ly distributed. Separate for REA- DER and UPDATER transactions.

---+---

Table 2: Workload parameters of the mDdels

5. Simulation results and interpretation

We have conducted _several series of experiments with this system. To discuss al I of them is prohibitive within the constraints of this paper. Therefore we will analyse three series of experiments, that clarify the flexibility of our approach and give an impression of the behavior of both concurrency control algorithms under different workloads.

The presentation wil I be organized as fol lows: one table wil I give the charac- teristic parameter settings for the series, the resul ts wi 11 be shown in several diagrams.

Al I the series have a co1TTT1on simulation length of 5000 seconds ofmodel led time, so that between 1000 and 1500 transactions could be processed. The CPU- time for those runs varied from 5000 to 50000 seconds on an IBM 3031 under VM!

The first experiment investigated the influence of large read-transactions on concurrency control. For this purpose, the proportion of read-transactions was changed from 0% in the first run by 5% up to 25% in the sixth run.

+---+---+---+---+---+---+---+---+---+

1 Var.: V I S-R I S-U I TERM I CPU-R I CPU-U 1 10-R 1 10-U 1

+=========+=====+=====+=====+======+=======+=======+======+======+

1 Val ue 1 250 1 0.651 210 1 20 0.01 1 0.05 1 0.05 1 0.25 1

+---+---+---+---+---+---+---+---+---+

Table 3: Fixed parameters for the first experiment

+---+---+---+---+---+---+---+

1 Run No. 2 3 4 5 6

+==========-==+=======+=======+=======+=======+=======+=======+

1 ARR-R 1 110.0 1 55.0 1 36.7 1 27.5 1 22.0 1

+---+---+---+---+---+---+---+

1 ARR-U 5.5 1 5.8 1 6 .1 1 6.5 1 6.9 1 7.3 1

+-=---==-===--+---+=-=--==+=======+=======+=======+=======+

1 % READER * 1 0 5 10 1 5 20 25

+---+---+---+---+---+---+---+

1 % UPDATER * 1 100 95 90 85 80 75

+---+---+---+---+---+---+---+

. .._ control led by variables ARR-U, ARR-R

Table 4: Changing parameters for the first experiment

Modelling Concurrency Control Algorithms Page 18

The dynamic behavior of both algorithms under the varying load is quite diffe- rent. Whereas the turnaroundtime of UPDATERs remains constant for an increa- sing number of READERS under OPTIMISTIC concurrency control and increases by a f a c t o r o f 2. 5 und er LOCK I NG ( s e e f i g. 6 .1 ) , t h e c o n t r a r y i s t h e ca s e f o r READERs. Their turnaroundtime is constant under LOCKING and increases by a factor of 4 under OPTIMISTIC concurrency control (see fig. 6.2).

Th i s behav i or can be exp I a i ned by wa i t i ng s i tuat i ans und er LOCKING and the risk of inval idation under OPTIMISTIC concurrency control. In a system con- t r o 1 1 e d b y o u r OP T I M I ST I C m e t h o d , t h e r e i s p r a c t i c a 1 1 y n o w a i t i n g f o r t h e val idation of the. own or some other transactions (see fig. 6.7).

In the LOCKll'-G-control led system the probabi I ity of waiting increases as does the average waiting time for locks. This is especial ly obvious for x-locks. lt should be mentioned, that this behavior has a (arge effect because it happens with each attempt of obtaining a lock. The reason for the increasing turn-

aroundtime of READER-transactions under OPTIMISTIC concurrency control are inval idations. Whereas deadlocks are neglectable in LOCKll'-G (see figs. 6.3 and 6.4), the probabi I ity of inval idation for a READER increases up to 0.7 inde- pendent from the number of READ-transactions (see fig. 6.4)!

OPTIJIJSTIC/LOCKINC

T 5 r n 4

' 0 3 n d 2 l

• 1 '

Run

LOCKING: • OPTIMISTIC: C

Fig. 6.1: Turnaroundtime UPDATER

Se,-1e 1

OPTIMISTIC/LOCKINC ^{Serie l}

0 020

0 0 015

.

C ₁

0 010

p ,J3-,

0 0 005

-,,_

'0---tJ b

0 000~===~===*===;;:;:===:;:::===~

Run LOCKING: .• OPTIMISTIC: C

Fig. 6.3: Cancel-Probab. UPDATER

OPTIJIISTJC/LOCKINC

0 20

0 15

1.1 0 10 9 h

•

u l 0.05

.... -o--- .. --- - --- -~- ---- - - - --'{3 ..

Run LOCKING; • OPTIMISTIC; c

Fig. 6.5: Throughput UPDATER

OPTIJIISTIC 0 10

u

o 0.08

n 0.06 g / u a 0 0•

Ser- te 1

1 0 02

. .

- c---0---~---...o---~

0 00-l=-=-=~=-~=-=;_-=-;;.3._.::-;-·~·::-·:-_-:-:.;-a-f:-_-:.:-._-:-:-.~.~-~-~--o;_:-:.-.-..;._.~---~-~---~-;Er;-:._-~-:-~-~-:-_-~-j-o

Run

OATABASE : Transactions woiting: • Wcittime: •

VALIDATION; Transactions woitinq; A Woittime: •

Fig. 6.7: Transactions waiting/Waittime

OPTIJIISTIC/ LOCKJNC Ser-1• 1

300

T 250

' _n 200 _{_.EI,._}

' 0 150 '-e-···

d 100 n 50

Run LOCKING: • OPTIMISTIC: c

Fig. 6.2: Turnaroundtime READER

OPTIMISTIC/LOCKINC Ser ,e 1

1 .0

C 0 8

:r;a ---;;._

0 6

'-0

, 0 4

0 2

0 01~===:;::;:::===~==;:::;:;===~==~

Run LOCKING: • OPTIMISTIC: •

Fig. 6.4: Cancel-Probab. READER

OPTIJIISTIC/LOCKINC Ser ,. 1

T

:~ ^.,..

h r 0. 03

h 0.02

•

u l 0 01

.o·

0 0

Run LOCKING: . • OPTIMISTIC; c

Fig. 6.6: Throughput READER

LOCKINC _{Ser-1e !}

1. 25

Run

SHARE: Tronsoctions waiting: • Woittime: O LOCK : Transoctioos waiting: 6 Waittime: •

Fig. 6.8: Transactions waiting/Waittime

Modelling Concurrency Control Algorithms Page 20

The aim of the second experiment was to show how increasing probabi I ities of confl ict affect the performance of concurrency control. Therefore we modified the variance of distribution of accesses to the database, control led by parameter V.

+---+---+---+---+---+---+---+---+---+---+

1 Var.: 1 5-R 1 5-U I ARR-R I ARR-U I TERM I CPU-R I CPU-U 1 10-R 1 10-U 1

+=========+=====+=====+=======+=======+======+=======+=======+======+======+

1 Val ue 26 1 26 1 8 8 0 0.05 1 0.05 1 0.25 1 0.25 1

+---+---+---+---+---+---+---+---+---+---+

Ta.hie 5: Fixed pa.rameters for the second experiment

+---+---+---+---+---+---+---+

1 Run No. 2 3 4 5 6

+===========+=======+=======+=======+=======+=======+=======+

IV 500 250 125 100 80 60

+---+---+---+---+---+---+---+

Tab I e 6: · Cha.nging pa.rameters for the second exper iment ( see f i gure 5)

The turnaroundtime of the transactions in the LOCKlf\G system remained constant during the experiment. There are two reasons for this result. First the proba- bi I ity of deadlocks was zero, second the increasing waiting times did not a ff e c t t h e mo de I b e ca u s e o f t h e I ow n umb er o f da t ab a s e ob j e c t s per t ran s a c- tion. The waiting times wi 11 affect the system, if the transaction size wi 11 increase.

The turnaroundtime of the transactions in the OPTIMISTIC case remained nearly constant, too, but on a higher I evel than in the LOCKING system. The main r e a s o n i s t h e h i g h e r p r o b ab i I i t y o f i n v a I i da t i o n. An i n t e r e s t i n g a s p e c t i s t h e fol lowing: for the first time there was noticeable waiting in the OPTIMISTIC case. The average waiting time for val idation raised up to 0.3 seconds. This did not affect the performance because of th.e fact that waiting for val idation takes place only one time per transaction.

: : :~

10 S 10 0

-. ·-e-·

____ .0- •.• ·---- . i:J

1

n

----·---o-· - - - ~ - - - - ·

6 0

nF ----

s s

S 0

~

:

^~]

l 3 5]

3 0

• 2 5

• 2 0 1 S 1 0 0 5

0 0 . ~ _ . . . _.,.-~--,-,-~--,.--r---r---~,

Run LOCKING OPTIMISTIC: •

Fig. 7.1: Turnoroundtime UPDATER

OPTIJIISTIC/LOCK/NG ^Ser,e2

0 20

-- --0

_;,r---

a 0 ,s ^ß-.

_'15.

0 10

---o·

0 05

Run cOCKING. • OPTIMISTIC: •

Fig. 7.3: Concel-Probob. UPDATER

OPT/Jl!STIC/LOCKJNG ^{Ser, e} 2

0 20

0 15 T

h

---&---:;,-;;,;·---

u 0 10 g h p u l 0 05

Run LOCKING: • OPTIMISTIC: •

Fig. 7 .5: Throughput UPDATER

OPTIJIISTIC

0 •0 a 0 35 l 0 30 n 0 25

•

/ 0 20

ij 0 0 15

0 10 , 0 05

----

^{,,,,---/ / ,,1)--_}

^{---~-- --4-- -}

Ser 1e ~

---::-~~~~~--a----

---<]- --- --- --E>--- -. -~

• 0001i,..,_.,._.,._.,._...,,,._~--r---,-.--,-,-~T

Run

O~TABA$E. : Transactians wOiting: • Woittime! tl VALIDATION: Transactians woiting: A. Waittime: <>

Fig. 7.7: Transactions waiting/Waittime

OPTJ.\i!STIC/LOCK/NC s~,.. 1e ;

LOCKING. • OPTIMISTIC: tl

Fig. 7.2: Turnaroundtime READER

OPTIJl{STIC/ LOCK/NC ^{Ser 1e} 2

0 201

1

"

: ^{::"[' ....}

~···•

__ ,' J3.,________ ,---··

,·· ·-,,,-·

0 0 05 ,-·•'

b 0 00

t ··---~-•

Run LOCKING: • OPTIMISTIC: •

Fig. 7.4: Cancel-Probab. READER

OPTIJl{ST!C/LOCKING ^{Ser ,e} 2

0 15

---0--- ---0---~---

V 0 10 9 h u l 0 05

Run LOCKING: • OPTIMISTIC: •

Fig. 7.6: Throughput READER

LOCKJNC 0.1011

w

0 0 075

Q I 0, w

Q

. .

0.

Run

SHARE: Transactions woiting; • Waittime: tl LOCK : Transactions waiting: .6 Woittime: •

s.,. .• ²

Fig. 7 .8: Transactions waiting/Woittime

Modelling Concurrency Control Algorithms Page 22

Th e I a s t e x p e r i m e n t w e w an t t o d i s c u s s i n t h i s s t u d y e x am i n e d t h e e f f e c t o f increasing size of UPDATER transactions. For this reason we modified the UPDATER transaction size shape parameter S.

+---+---+---+---+---+---+---+---+---+---+

1 Var.: V 1 5-R I ARR-R I ARR-U I TERM I CPU-R I CPU-U 1 10-R 1 10-U 1

+=========+=====+=====+=======+=======+======+=======+=======+======+======+

1 Val ue 1 250 1 26 1 18 18 0 0.05 1 0.05 1 0.25 1 0.25 1

+---+---+---+---+---+---+---+---+---+---+

Ta.hie 7: Fixed parameters for the third experiment

+---+---+---+---+---+---+---+

1 Run No. 2 3 4 5 6

+================+=======+=======+=======+=======+=======+=======+

1 5-U 210 26 9.0 4.8 2.9 2.0

+================+=======+=======+=======+=======+=======+=======+

Mean transac- 0.28 0.8 1.3 1.8 2.3 2.8

1 tion size in% 1 1 of DB-size 1

+---+---+---+---+---+---+---+

Table 8: Changing para.meters for the third experiment {see figure 4)

This experiment shows the superiority of the LOCKII\G approach, too. Whi le both methods perform equal ly in the case of smal I UPDATERS, the turnaroundtime and cancel probabi I ity increased by growing UPDATER transactions with different rates. The OPTIMISTIC system reached a critical point at a size of 2.3% of the database objects of the UPDATER transactions. Beyond this point the rate of cancel led transactions exceeded the number of new transactions! This led to the situation that transactions waited to enter the system and increased the turnaround time.

500 450 T 400 u „ 350 n

0 300

: 250 j

n 200~

t

^150~

100~

@._

• 50~ _________ .-ci

~~~~:::==::::==:

Run LOCKING • OPTIMISTIC; •

Fig. 8. 1 Turnoroundtime UPDATER

0PTIMISTIC/LOCKING

1 0 0 9 C 0 B n 0 7

0

0 6 0 5 0 4 b 0 3 b 0 2 0 1

.o·

__ {] ___ .---•

LOCKl'<G. • OPTJ>;ISTIC; •

Ser, e 3

13---~

Fig. 8.3: Concei-Probab. UPDATER

OPTIMISTIC/LOCKING Ser ,e 3

0 10 0 09 0 08

T 0 07 o_

h , 0 06 0 05 g

h 0 04 13---{J

p u 0 03 l

0 02 0 01 0 00

Run

LOCKING: • OPTIMJSTIC: •

Fig.

8.5:

Throughput UPDATER

OPTlJ/lSTlC

2 .00

0 1 75 l 1 50 n 1 25 g / 1 00 u

Run

DATABASE : Tronsoctions woiting: • Woittime: t::

Se,- ,e 3

Fig.

VALIDATION: Tronsactior,s woiting: 6 w·aittlme: o

8. 7: Transactions waiting/Waittime

Ser •• )

1251

100

75~

[J

50j.

25 ,

0-~::.:.::.:.~~:.::.:.::~:::::::~~'.'.:.:.::.:.:.:.::.:::.:::

Run LOCKING. • OPTIMISTIC; tJ

Fig. 8.2: Turnoroundtime RE.1\DER

OPTIAIISTIC/ LOCKING

0

51

,

C 0 4-~

1

0 3~

0 2

0

b a 1

__ -l!f

---0---a---

Run LOCKING. • OPTIMlSTIC: ::t

Fig. 8.4: Concel-Probab. READER

0PT!MISTIC/L0CK!NG

: ~:i·

0 08 0 07

h 0 06+---..

0 05 9 h 0 04

~

0 03i

0 02 0 01

--•---0·

"

Run

c- · LOCKING. • OPTIMISTIC: •

, 19.

8.6: Throughput READER

L0CKlNC

0 5

u 0 0 4

n 0 3 9 I

u

0 0 2

0 1

Run

SNARE: Transoctions woiting: • Woittime. c

Se,., e

·o

Ser-, • 3

s.,-1. J

A

Fig. LOCK : Transactions ·Noiting: 6 Woittime: •

8.8: Transactions woiting/Waittime

Modelling Concurrency Control Algorithms Page 24

Sumnarizing the resul ts of the three experiments one can say that the LOCKII\G a p p r o a c h b e a t s t h e OP T I M I ST I C c o n c u r r e n c y c o n t r o I i n v e s t i g a t e d i n t h i s s t u d y under al I workloads we have examined. The main reason for this is the high risk of inval idation in the OPTIMISTIC scheme. Performing val idation at the end of every transaction increases the loss time in the OPTIMISTIC case. New val idation schemes (/UnSP83/) and techniques avoiding repeated restart of inval idated transactions could increase the performance.

6. Conclusions

As mentioned in chapter one, we have used a general purpose performance eva- 1 uation tool, COPE, for this study, in order to (1) get flexible models that are easy to construct, understand and change and (2) to reduce th!l time needed for such an investigation.

Ho w e v e r , w e s o o n f o u n d o u t , t h a t COP E i n i t s s t a t e o f t h a t t i m e w a s I a c k i n g some essential language constructs needed for its appl ication to the field of concurrency control. Therefore we had to implement some additional features (e.g.managing lock tables and a deadlock detection algorithm), whichmean- whi I e have been used to extend the standard of COPE. These necessary exten.;,.

sions of COPE cost us a great part of the time we hoped to save by applying a tool instead of implementing a "hand made" simulation model.

COPE has been built as general purpose tool, which was supposed to support the user by a high level of abstraction with respect to performance evaluation in v es t i g a t i o n s. S i n c e o ur mo de I s n e e de d s y s t em f u n c t i o n s such a s c omp a r i n g sets for the val idation in OPTIMISTIC concurrency control, the high level of abstraction was more troublesome than helpful for this Special purpose, resul- ting in a high complexity of the OPTIMISTIC model.

F i n a 1 1 y , i t s h o u I d b e m e n t i o n e d t h a t t h e s i mu I a t i o n r u n s c o s t h u g e amo u n t s o f CPU-time. This is due to the fact that general performance evaluation tools

often perform worse than simulation programs, which are special ly ¹¹tai lored¹¹ to a given problem.

The bad performance of the OPTIMISTIC concurrency control method investigated in this study indicates that further research has tobe done eval uating im- proved versions of this method, such as proposed in /UnPS83/. Further on analytical studies are necessary to gain additional insights in the behavior of concurrency control algorithms and to obtain results at lower costs.

Literature

/COPE84/ Bei lner, H.; Maeter,

J.:

COPE: Past, Presence and Future; tobe presented at: International Conference on Model I ing Techniques and Tools for Performance Analysis, Paris, France, May 1984

/DeCa82/ Devor, C.; Carlson, C.: Structural Locking Mechanisms and their Effect on Data Base Management System Performance; in: Information Systems, Vol. 7, No. 4, 1982

/EGLT76/ Eswaran, K.P.; Gray, J.H.; Lorie, R.A.; Traiger, L.I.: The Notions of consistency and predicate locks in a data base system; in: CACM, Vol.

19, No. 11, Nov. 1976

/ G r a y 7 9 / G r a y , J • H. : No t e s o n D a t a b a s e O p e r a t i n g S y s t e m s ; i n : O_p e r a t i n g S y s - tems: An Advanced Course; Edt. by Bayer, R.; Graham, R.M.; Seeg- muel ler, G.; New York 1979

/Kila83/ Kiessling, W.; Landherr, G.: A quantitative Comparison of Lockproto- cols for Centralized Data Bases; in: Proc. VLDB 1983, Florence, ltaly /KuRo79/ Kung, H.T.; Robinson, J.T.: On Optimistic Methods for Concurrency

Control; in: Proc. VLDB 1979, Rio de Janeiro, Brazil

/MeNa82/ Menasce, D.A.; Nakanishi, T.: Optimistic versus Pessimistic rency Control Mechanisms in Data Base Management Systems; in:

m a t i o n S y s t e m s , V o 1 • 7 , No • 1 ·, 1 9 8 2

Concur- lnfor-

/MuKr77/ Munz, R.; Krenz, G.: Concurrency in Data Base Systems - A Simulation Study; in: ACM SIGMOD Annua I Conference 1977

/SaSp83/ Saal, A.; Spaniol, 0.: Performance Evaluation of a Data Base Manage- ment System; in: Proc. International Seminar on Model I ing and Perfor- mance Evaluation Methodology, Vol. 1, Paris, France, Jan. 24-26, 1983, INRIA

/UnPS83/ Unland, R.; Praedel, U.; Schlageter, G.: Design Alternatives for Optimistic Concurrency Control Schemes; in: Proc. of the Second International Conference on Data Bases, Cambridge, Great Britain, Sept. 1983