Deadlock Overview and Discussion

Title:    Oracle Rdb: Deadlock Overview and Discussion 

Product:  Oracle Rdb - Version 4.1 and above (tested to version 7.0.5)

Op/Sys:   OpenVMS  

Purpose:

========

When upgrading OpenVMS and/or Rdb/VMS, deadlock handling and frequency

can change.  This article discusses deadlocks and gives enough

background to understand OpenVMS and Rdb/VMS deadlock interaction.

Contents:

=========

1.  What is a Deadlock?

2.  How are Deadlocks Handled?

3.  Why are Deadlocks Bad?

4.  Conclusions.

5.  Single Resource Deadlock Examples.

Oracle Rdb: Deadlock Overview and Discussion:

=============================================

1.  What is a Deadlock?

-----------------------

Deadlock is a term used to describe the situation where transaction A

is holding a resource in an incompatible lock mode that transaction B 

needs, and transaction B is holding a resource in an incompatible lock 

mode that transaction A needs.  Neither transaction can successfully 

continue without first acquiring the resource held by the other transaction.

  DEADLOCK situation

                               *

        Transaction A          *            Transaction B

                               *

      -------------------      *          ------------------

   |->|  has resource a |      *  |-----&gt | has resource b |

   |  -------------------      *  |       ------------------

   |                           *  |

   |     needs resource b  ----*---       needs resource a 

   |                           *                   |

   ----------------------------*--------------------

A single transaction can not deadlock itself.  If you are seeing a

deadlock, at least two transactions are involved.  This means that 

there are at least two database attaches.  A single transaction does 

not withhold resources from itself.

It is possible to have a deadlock situation involving more than two

transactions.  For example, if tranactions A, B and C hold, respectively,

resources a, b and c, then if transaction A requires resource b, 

transaction B requires resource c, and transaction C requires resource a,

then all three transactions are deadlocked waiting on each other.

If a transaction is started with the NOWAIT keyword, there will not 

be any deadlocks.  A NOWAIT transaction does not wait for a resource 

to be released, and so returns a lock conflict immediately, instead of 

deadlocking.  WAIT transactions hold resources, so with them there is 

the possibility of deadlocks.

When only READ ONLY transactions are used, you do not get deadlocks

if snapshots are enabled.

Deadlocks can occur on a single resource.  This happens when both

transactions hold locks on a resource, and both attempt to upgrade

the lock to an incompatible mode.  For example, if both hold a PR lock 

on a record because they have read it, and both want an EX lock to

update.  Since neither upgrade request can be granted until the

other transaction releases their lock, and neither will release

their lock until their upgrade request is granted, there is a

deadlock.

Some deadlocks are internal to Rdb and are resolved automatically.

They are not reported back to the user application as deadlocks.

They can still have a serious impact on performance.

2.  How are Deadlocks Handled?

------------------------------

Rdb uses the OpenVMS lock manager to detect deadlock situations.  

A deadlock search is performed by the lock manager after a lock

request has been waiting for a resource DEADLOCK_WAIT (a SYSGEN

parameter) seconds.  The default is ten seconds.  The deadlock

victim is the youngest transaction involved in the deadlock

situation.  Searching for deadlocks is a resource intensive

operation.

3.  Why are Deadlocks Bad?

--------------------------

There are several reasons why deadlocks are bad.  

o  At least one transaction will be waiting DEADLOCK_WAIT seconds.  

   Assuming the default deadlock wait of ten seconds, this means that 

   the oldest transaction takes n + ten seconds to complete, where 

   n is the normal transaction time.  The other transaction(s) involved 

   need to start over.

o  While the transactions involved in the deadlock are waiting to be 

   detected, they are holding locks on all their resources.  Ten seconds 

   is a long time to hold resources in a database.  Other transactions 

   that need these resources have to wait.  

It is easy to see how a deadlock can cause performance degradation across 

the whole database if critical resources are held by one of the deadlocked 

transactions.

DEADLOCK_WAIT can be made smaller in order to decrease the time taken 

for the lock manager to detect deadlock conditions.  Unfortunately, in 

a highly concurrent environment, there can be a lot of overhead on the 

system if DEADLOCK_WAIT is made too small.  It has been reported that on 

some systems that are aleady CPU-bound, reducing DEADLOCK_WAIT does not 

help performance problems.

4.  Conclusions.

----------------

Avoid deadlock situations.  They have a major impact on performance

and tend to propagate to other transactions because of the length of

time deadlocked resources are held.  Reducing deadlock wait for

deadlock prone systems may improve transaction response time by

detecting deadlock situations more quickly, although, this is at

the expanse of system CPU resources because searches are performed

more often.  It may not be effective for highly utilized systems.

The examples below show these problem occurring on update statements.  

Deadlocks are not limited to update statements.

5.  Single Resource Deadlock Examples.

--------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84307/，如需轉載，請註明出處，否則將追究法律責任。