Basic Blocking Lock Detection

At work today we had a trouble ticket generated by BMC Patrol that complained of a ‘Locking Conflict’. Having no idea what this might mean, one of my colleagues (who is learning Oracle) asked how we’d detect a locking problem.

Because we were in a hurry, I fell back on a very old, clunky, but reasonably reliable method to see if there really was a locking problem. I was a bit surprised that no-one seemed to know how to do this, because it’s pretty basic stuff, so there must be people out there who don’t and this is for them.

Session 1

SQL> connect testuser/testuser
Connected.
SQL> create table test_tab as select * from all_objects where rownum <> desc test_tab
Name                                      Null?    Type
----------------------------------------- -------- -----------------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

SQL> select object_id, object_name from test_tab;

OBJECT_ID  OBJECT_NAME
---------- ------------------------------
258        DUAL
259        DUAL
311        SYSTEM_PRIVILEGE_MAP
313        SYSTEM_PRIVILEGE_MAP
314        TABLE_PRIVILEGE_MAP
316        TABLE_PRIVILEGE_MAP
317        STMT_AUDIT_OPTION_MAP
319        STMT_AUDIT_OPTION_MAP
605        MAP_OBJECT
886        RE$NV_LIST

10 rows selected.

SQL> select object_id, object_name from test_tab
2  where object_id = 316
3  for update;

OBJECT_ID  OBJECT_NAME
---------- ------------------------------
316        TABLE_PRIVILEGE_MAP

This row is now locked by session 1.

Session 2

SQL> select object_id, object_name from test_tab
2  where object_id = 316
3  for update;

This session hangs because it’s waiting for the row locked by session 1.

NOTE – If I’d added the NOWAIT keyword, I would have got this response instead

SQL> select object_id, object_name from test_tab
2  where object_id = 316
3  for update nowait;
select object_id, object_name from test_tab
                         *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified)

So we have a blocking lock problem – session 1 is blocking session 2 which is trying to lock the same row. The only way this situation will clear is if session 1 releases the lock (or a DBA kills the session ;-), which will achieve the same end).

How would we know about this? There are a number of dictionary views that can help here – this paper is much more detailed than a mere blog and is worth a look – but the way I learnt to do this is

SQL> connect / as sysdba
Connected.

Run the next script once, to create the additional dictionary views in this database if they don’t exist already.

SQL> @?/rdbms/admin/catblock

Now you can run utllockt.sql any time to see which sessions are blocking other sessions in a tree structure.

SQL> @?/rdbms/admin/utllockt

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD
----------------- ----------------- -------------- --------------
LOCK_ID1          LOCK_ID2
----------------- -----------------
147               None
  141            Transaction       Exclusive      Exclusive
589830            1475

Here we can see that the session that isn’t indented at all – session id 147, is blocking session 141 (which is indented). If I add a third session trying to lock the same row, you’ll see the difference

SQL> @?/rdbms/admin/utllockt

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD
----------------- ----------------- -------------- --------------
LOCK_ID1          LOCK_ID2
----------------- -----------------
147               None
  141            Transaction       Exclusive      Exclusive
589830            1475
  158            Transaction       Exclusive      Exclusive
589830            1475

Because sessions 141 and 158 are both waiting on session 147, they are indented to the same level. If I commit session 147, I’ll get this new output

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD
-----------
------ ----------------- -------------- --------------
LOCK_ID1          LOCK_ID2
----------------- ---------------
--
141               None

  158            Transaction       Exclusive      Exclusive
917518
      47

So now session 147 has released the lock, 141 has grabbed it and session 158 is waiting for it. If I commit in session 141, this is what I’ll get when I run utllockt

no rows selected

There are no blocked sessions now. Session 158 has the lock, but I’m not really interested in that if it’s not blocking anything

What I like about this approach is that it’s available on every version of Oracle you’ll work with, on every platform and so when I needed to think quickly today, I knew I could rely on (and remember!) this. It has it’s problems, as discussed in the paper, but it will work well enough in most situations.
Updated – and as Nuno pointed out in the comments, there’s DBA_WAITERS too but I’m not 100% sure what version that first appeared.