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>