Previous | Table of Contents | Next

Page 630

Now if session 28 rolls back or commits the transaction, session 29 can acquire the lock it is waiting for. If we examine the entries in v$lock for session 29, we get two records after session 28 has committed the transaction. The following are the observations:

      SID TY      LMODE    REQUEST        ID1        ID2


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


        29 TM          3          0       7590          0


        29 TX          6          0     327680      10834

          Select name

         From v$rollname
          where usn     =     trunc(327680/65536)
  NAME
           ------------
   RB03

Therefore, session 29 starts writing the rollback information in rollback segment RB03.

Case 3: A Session Trying to Update an Updated Row by Another Session

This particular locking case is the most common scenario in everyday locking situations. When two sessions simultaneously try to update the same row, the session that first updates the row gets the lock, and the second session will wait behind the first session to acquire the lock. The lock acquired by the first session will be released only when the first session

Page 631

commits or rolls back the transaction. In this example, session 29 (the first session) has updated the employee name in a record of the employee table to TOM and has not yet committed the change. Session 30 (the second session) now tries to update the same row. To analyze v$lock, you can use the same select as in the last example, but replace the sid column with the relevant sid values:

       SID TY      LMODE    REQUEST        ID1        ID2

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


        29 TM          3          0       7590          0


        29 TX          6          0     327680      10834

        30 TM          3          0       7590          0

        30 TX          0          6     327680      10834

Based on the output of the select, the following observations can be made:

Monitoring Locks on the System

Now that you are familiar with the concepts of locking and the associated system tables, it should be easy to detect locks on the scripts. With the information in the previous section,
the user will be able to write his or her own lock detection scripts, but some lock monitoring scripts are given here nevertheless. This section will contain some useful scripts for detecting and monitoring locks.

The script in Listing 25.1 will help give you an idea of the number of objects locked in the database. It will report on all objects that are currently locked with their lock modes, and it will also report all the sessions waiting to acquire any locks.

Page 632

Listing 25.1Simple Script to Check Locks on the System
Select s.username,

        s.sid,

        l.type,

        l.id1,

        l.id2,

        l.lmode,

        l.request,

        p.spid PID


From v$lock l,


    v$session s,


    v$process p

Where s.sid = l.sid


And   p.addr = s.paddr


And   s.username is not null

Order By id1, s.sid,request;


USERNAME      SID TY       ID1       ID2     LMODE   REQUEST  PID


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


    OPR1       39 TM      4573         0         3         0 19271
    OPR2       41 TM      4573         0         2         0 20155
    OPR3      116 TM      4573         0         2         0 19914
    OPR4      125 TM      4573         0         2         0 19906
    OPR4       95 TM     12547         0         2         0 19906
    OPR5       95 TM     15397         0         3         0 20364
    OPR3      116 TM     15397         0         2         0 19914
    OPR4       39 TM     15397         0         2         0 19906
    OPR2       41 TX     65548     91271         6         0 20155
    OPR4      125 TX     65548     91271         0         6 19906
    OPR1       39 TX    196626    107701         6         0 19271
    OPR5       95 TX    262156    118264         6         0 20364
    OPR3      116 TX    327699    200758         6         0 19914

This output is taken from a live production database. Notice that all the locks are of the type TM(2, 3) and TX(6). As previously mentioned, these are the most commonly acquired locks in any database.

A quick scan through the output leads to the following conclusions:

Previous | Table of Contents | Next