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.
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:
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 SystemSelect 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: