By Franck Pachot

.
Here is an odd Oracle behavior I observed when a transaction that acquired a lock is rolled back. Note that this is related to a specific combination of locks that should not be encountered in production. So it’s not a bug. Just something unexpected.

 

First session

In my first session I lock the DEPT table in share mode (RS)
20:56:56 SQL1> lock table dept in row share mode;
Table(s) Locked.

My first session (SID=53) has acquired a TM lock in mode 2 (row share):
20:56:56 SQL1> select object_name,session_id,LOCKED_MODE from v$locked_object join dba_objects using(object_id);
 
OBJECT_NAME SESSION_ID LOCKED_MODE
----------- ---------- -----------
DEPT 53 2

 

Second session

In my second session I lock the DEPT table in share + row exclusive mode (SRX). This is compatible with the RS.
20:56:59 SQL2> lock table dept in share row exclusive mode;
Table(s) Locked.

My second session (SID=59) has acquired a TM lock in mode 5 (share + row exclusive):
20:56:59 SQL2> select object_name,session_id,LOCKED_MODE from v$locked_object join dba_objects using(object_id);
 
OBJECT_NAME SESSION_ID LOCKED_MODE
----------- ---------- -----------
DEPT 59 5
DEPT 53 2

Then I rollback my transaction
20:56:59 SQL2> rollback;

My lock was released and I’ve only the one from Session 1 (SID=53):
OBJECT_NAME SESSION_ID LOCKED_MODE
----------- ---------- -----------
DEPT 53 2

Now comes the funny part. I run exactly the same SSX lock, which was immediately acquired before:
21:14:30 SQL2> lock table dept in share row exclusive mode wait 5 ;

But now it hangs. Let’s see the blocking tree with utllockt.sql:
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
53 None
59 DML Exclusive Row-S (SS) 94228 0

What? My session wants to acquire an Exclusive lock? I’ve never requested that.
And finally it fails because my Session 1 has a RS lock which prevents exclusive locks.
lock table dept in share row exclusive mode wait 5
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

Third session

But there is worse. I disconnect my second session and try to do the same from a third one. And I even try only a RS lock:
21:15:20 SQL3> lock table dept in row share mode wait 5 ;

And I’m blocked again. It seems that because a session had acquired a SRX lock and has rolled back, while another session held a RS one, then any new transaction that wants to acquire any lock must acquire temporarily an exclusive one before.

 

Event 10704 “Print out information about what enqueues are being obtained”

In order to get further I traced the locks with event 10704 (see how in a previous post).

Here is the trace when it failed, filtering on ‘00017014’ which is the object_id of DEPT in hexadecimal:
ksqgtl *** TM-00017014-00000000-00000000-00000000 mode=2 flags=0x400 timeout=5 ***
ksqrcl: TM-00017014-00000000-00000000-00000000
ksqgtl *** TM-00017014-00000000-00000000-00000000 mode=6 flags=0x400 timeout=5 ***
ksqcmi: TM-00017014-00000000-00000000-00000000 mode=6 timeout=5
ksqcmi: returns 51
ksqgtl: RETURNS 51
ksqrcl: returns 0

My ‘lock table dept in row share mode’ acquires a mode 2 (which is the ‘row share’ mode) but then released it and tried to acquire a mode 6 (which is exclusive)

And then here is a trace when it was successful, after the transaction in Session 1 has been committed:
ksqgtl *** TM-00017014-00000000-00000000-00000000 mode=2 flags=0x400 timeout=5 ***
ksqrcl: TM-00017014-00000000-00000000-00000000
ksqgtl *** TM-00017014-00000000-00000000-00000000 mode=6 flags=0x400 timeout=5 ***
ksqcmi: TM-00017014-00000000-00000000-00000000 mode=6 timeout=5
ksqcmi: returns 0
ksqgtl: RETURNS 0
ksqgtl *** TM-00017014-00000001-00000000-00000000 mode=3 flags=0x400 timeout=5 ***
ksqgtl: RETURNS 0
ksqcnv: TM-00017014-00000000-00000000-00000000 mode=2 timeout=5
ksqcmi: TM-00017014-00000000-00000000-00000000 mode=2 timeout=5
ksqcmi: returns 0
ksqrcl: TM-00017014-00000001-00000000-00000000
ksqrcl: returns 0

So it it did the same, but that time the mode 6 can be acquired. Then we see a conversion to mode 2 which is the RS we wanted.

Finally I tried with all other combinations of locks, but it seems that only that one (RS then rolled back SRX) show that behavior. I tried also with DML instead of ‘lock table’ statement but the Share lock acquired by DML (the non-indexed foreign key case) is released immediately so I cannot rollback it. And a failure in the statement do not trigger the same behaviour.

Final note: the only reference I’ve find for that behavior is this post on oracle-l