Previous | Table of Contents | Next

Page 627

Analyzing v$lock

Oracle stores all information relating to locks in the database in the dynamic status table v$lock . This section analyzes various scenarios in the database when resources are locked and examines the v$lock table to see how Oracle reports the lock.

The following is a description of the structure of v$lock:

ADDR RAW(4)

KADDR RAW(4)

SID NUMBER

TYPE VARCHAR2(2)

ID1 NUMBER

ID2 NUMBER

LMODE NUMBER

REQUEST NUMBER

CTIME NUMBER

BLOCK NUMBER

The important columns of interest, which would be of use when analyzing locking situations, are as follows:

sidThis is the session identifier.

TypeThis is the type of lock acquired or waiting by the session. Example values are

TX Transaction

TM DML or Table Lock

MR Media Recovery

ST Disk Space Transaction

As said previously, this chapter covers only TX and TM locks.

lmode/requestThis column contains the mode of the lock. The possible values are:

0 None

1 Null

2 Row Share (RS)

3 Row Exclusive (RX)

4 Share (S)

5 Share Row Exclusive (SRX)

6 Exclusive (X)

If the column lmode contains a value other than 0 or 1, it indicates that the process has acquired a lock. If the request column contains a value other than 0 or 1, it indicates that the process is waiting for a lock. If lmode contains a value 0, it indicates that the process is waiting to acquire a lock.

The following select is a quick way to check whether there are any sessions waiting to acquire locks on any table.

     Select count(*)
     From v$lock
     where lmode = 0

Page 628

If the select returns a value that is greater than zero, there are locks sessions on the systems currently waiting to acquire locks.

id1Depending on the type of lock, the value in this column can have different meanings. If the type of lock is TM, the value in this column is the ID of the object that is to be locked or waiting to be locked, depending on the context. If the type of lock is TX, the value in this column is the decimal representation of rollback segment number.

id2If the type of lock is TM, the value in this column is zero. If the type of the lock is TX, it is the representation of the wrap number, that is, the number of times the rollback slot has been reused.

Using this information, you can now examine various scenarios and analyze the values stored in this table in different cases. The next section covers detailed case analyses of commonly occurring locks on the database and how to read this information from v$lock view.

Case 1: A Table Locked Exclusively

For purposes of discussion, let's assume that the table that is going to be locked is the employee table and the session id is 28. Assume that one user issues the following statement:

Lock table employee in exclusive mode;

This statement will lock the table in exclusive mode. If a table is locked exclusively by a user, the only SQL statement that can be used on the table by another user is the select statement. Insert, update, delete or any DDL operation on this table by the other user's will not be permitted until the lock is released.

For examining the records in the v$lock table, use the following select:

Select sid,
      type,
      lmode,
      request,
      id1,
      id2
From v$lock
where sid = 28;

When you execute the select, you get the following output:


SID TY LMODE REQUEST ID1 ID2

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

28 TM 6 0 7590 0

The following are the observations:

Page 629

   Select name

          From sys.obj$

          where obj#     =     7590;

          Name

          --------

         Employee

Case 2: Session Updating a Row of an Exclusively Locked Table

In Case 2, the table is locked exclusively by a session, and another session is trying to update a row of the same table. Here is the SQL issued by the session trying to update the table:

Update employee
Set Name = `TOM'
where emp_id     =     `1086';

In this case, the entry for the first session in v$lock remains the same as in Case 1, but the entries for session two are very interesting. Let us assume the session id of the second session is 29. Now if you execute the following select:

Select sid,

      type,
      lmode,
      request,
      id1,

      id2
From v$lock
where sid in (28,29);

this is the output:


SID         TY      LMODE    REQUEST        ID1        ID2


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


28         TM          6          0       7590          0


29         TM          0          3       7590          0

The following are the observations in this case:

Previous | Table of Contents | Next