This was first published on https://blog.dbi-services.com/oracle-an-unexpected-lock-behaviour-with-rollback (2014-10-26)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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
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 2Then 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 2Now 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 0What? 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
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.
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 0My ‘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 0So 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