Friday, March 28, 2014

How to Diagnose a Locking Issue

A user ran an update statement and noticed that it did not seem to be doing anything:
 
SQL> conn gordon/bennett
Connected.
SQL> update andrew.emp
  2  set ename = 'COLIN'
  3  where ename = 'BRIAN'
  4  /
 
I looked up his SID in V$SESSION:
 
SQL> select sid from v$session
  2  where username = 'GORDON'
  3  /
 
       SID
----------
      393
 
SQL>

I ran the following SQL, waited 10 seconds and ran it again:
 
SQL> l
  1  select event, time_waited/100
  2  from v$session_event
  3  where sid = 393
  4  and wait_class != 'Idle'
  5* order by 2 desc
SQL> /
 
EVENT                          TIME_WAITED/100
------------------------------ ---------------
enq: TX - row lock contention           249.58
Disk file operations I/O                   .01
SQL*Net message to client                    0
 
SQL> /
 
EVENT                          TIME_WAITED/100
------------------------------ ---------------
enq: TX - row lock contention            258.6
Disk file operations I/O                   .01
SQL*Net message to client                    0
 
SQL> 

This showed me that there was an ongoing locking issue with this user. I looked up the SID of the blocking session like this:
 
SQL> l
  1  select blocking_session from v$session
  2* where sid = 393
SQL> /
 
BLOCKING_SESSION
----------------
             101
 
SQL>
 
I saw that it belonged to Fred:
 
SQL> l
  1  select username from v$session
  2* where sid = 101
SQL> /
 
USERNAME
------------------------------
FRED
 
SQL>

I found some SQL on the Internet and hoped that it would tell me what Fred was doing but it did not work:
 
SQL> l
  1  select b.sql_text
  2  from v$session a, v$sqlarea b
  3  where a.sql_address = b.address
  4* and a.sid = 101
SQL> /
 
no rows selected
 
SQL>
 
I wondered if Fred had run his SQL but not commited it and changed my SQL as follows:
 
SQL> l
  1  select b.sql_text
  2  from v$session a, v$sqlarea b
  3  where a.prev_sql_addr = b.address
  4* and a.sid = 101
SQL> /
 
SQL_TEXT
--------------------------------------------------
update andrew.emp set enum = 2 where enum = 1
 
SQL> 

Then I looked in the EMP table and saw that both Fred and Gordon were trying to update the same row:
 
SQL> select * from andrew.emp
  2  /
 
ENUM  ENAME
----- ----------
1     BRIAN
 
SQL>
 
I asked Fred to COMMIT his UPDATE statement:
 
SQL> update andrew.emp
  2  set enum = 2
  3  where enum = 1
  4  /
 
1 row updated.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>

This allowed Gordon’s UPDATE to finish:
 
SQL> conn gordon/bennett
Connected.
SQL> update andrew.emp
  2  set ename = 'COLIN'
  3  where ename = 'BRIAN'
  4  /
 
1 row updated.
 
SQL>

No comments: