• The Transaction (TX) Lock has not yet been acquired by session 29; only after the TM lock is acquired can session 29 acquire the TX lock, provided no other session is accessing or updating the same row.

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

• The lock entry for session 28 in v$lock is no longer present as it has committed the transaction.
• Session 29 is now able to successfully acquire the TM lock, and the value 3 in the request column moves to the lmode column, indicating the lock has been acquired by the session.
• Session 29 also acquires another lock on the table; this is the Transaction or TX lock. The value in the Type column for this entry is TX, and the value of lmode is 6, which is the Transaction Lock.
• The value in id1, 327680, is the number of the rollback segment. To obtain the name of the rollback segment, you can use the v$rollname view. The following select will give the name of the rollback segment where session 29 starts writing the rollback information.

          Select name

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

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

• Whenever a session has to acquire a TX lock, it first has to acquire a TM Table Lock. After the Table Lock is acquired, the session acquires the TX lock, provided no other session has already acquired the TX lock on the same row. In summary, the session has to acquire two locks.

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

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:

• Session 29 (the first session) will have two records locks in v$lock: a TM lock and the TX Transaction Lock.
• Session 30 will have one entry in this table corresponding to the TM Table Lock. In this particular case, session 30 is able to acquire the TM lock immediately, because no other session has locked the table exclusively.
• Session 30 has another record corresponding to the Transaction Lock (TX). This entry will have the request column with a value of 6. This means session 30 is waiting to acquire a row exclusive lock. The values in the id1 and id2 columns will have the same value as the id1 and id2 values corresponding to session 29.
• This is the most common locking scenario, when one row is locked by one session and another session is trying to update the same row.
• n If session 29 now rolls back or commits the transaction, then like in Case 2 session id 30 can acquire the lock, and the value in the request column for the lock entry will move into the mode column, indicating that the lock has been acquired.

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.

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:

• There are three table objects—4573, 12547, and 15397—that are currently locked in the database.
• Session 39 is locking 4573 in row exclusive mode.
• Session 41 is locking object 4573 in row share mode.
• Session 116 is locking object 4573 in row share mode.
• Session 125 is locking object 4573 in row share mode.
• Session 95 is locking object 12547 in row share mode.
• Session 95 is locking object 15397 in row exclusive mode.
• Session 116 is locking object 15397 in row share mode.
• Session 39 is locking object 15397 in row share mode.