Oracle SQL - SELECT query locks index & blocks DML sessions

Question

We have some very weird locking happening in production. We have setup a PL/SQL script that finds objects that have been locked for more than 5 seconds and send us an alert e-mail.

Below is the cursor of that script:

select l.sid, trunc(l.id1 / power(2, 16)) rbs,
    bitand(l.id1, to_number('ffff', 'xxxx')) + 0 slot,
    l.id2, l.lmode, l.request, l.ctime, l.block,
    substr(v.osuser, 1, 12) osuser,
    substr(v.machine, 1, 15) machine,
    substr(v.module, 1, 12) module,
    decode(v.blocking_session_status||l.block, 'VALID0',
           dbms_rowid.rowid_create(1, v.row_wait_obj#, v.row_wait_file#,
                       v.row_wait_block#, v.row_wait_row#), '.') lrow,
    o.object_name,
    decode(v.sql_id, null, v.prev_sql_id, v.sql_id) sql_id,
    o.owner
from v$lock l, v$session v, all_objects o
where l.sid = v.sid
  and v.row_wait_obj# = o.object_id(+)
  and l.ctime > 5 and l.type = 'TX' and (l.request = 6 or l.block = 1)
order by 2, 3, 4, 8 desc, 7 desc;

And we got the alert today for a lock:

SID  TRANS-ID        L-TYPE  CTIME  BLOCK OSUSER     MACHINE         MODULE        SQLID            ROWID            OBJECT
---- --------------- ------- ------ ----- ---------- --------------- ------------ ------------- ------------------ --------------------
669  132,11,40475    6/0     70     1     userpr1     serv1023       userpr1-00002 fbnhs4gd9a7yn .                  IDX_005
1133 132,11,40475    0/6     62     0     userpr1     serv1023       userpr1-00000 f0gm2rx85qjja AAAgOuAAFAAD04TAAW ITEMST
924  132,11,40475    0/6     53     0     userpr1     serv1023       userpr1-00002 f0gm2rx85qjja AAAgOuAAFAAD04TAAW ITEMST
927  132,11,40475    0/6     27     0     userpr1     serv1023       userpr1-00001 f0gm2rx85qjja AAAgOuAAFAAD04TAAW ITEMST

So from the above, we can observe that sqlid fbnhs4gd9a7yn of session 669 has got a lock on index IDX_005 and blocking the rest of the remaining sessions.

Now for the most bizarre part:

1. SQLID fbnhs4gd9a7yn is just a SELECT query (not even a SELECT FOR UPDATE)
2. The index IDX_005 has no connection to table ITEMST but it blocks the remaining 3 sessions which updates ITEMST anyways

So my question is: How did [1] happen and why does it block update to the table ITEMST?

Could this be an bug in Oracle? We are using Oracle 11.2.0.4 Enterprise Edition, btw.

Answer 1

The sql_id that your query returns may or may not be related to the query that actually acquired the lock.

In SID 669, for example, if I update ITEMST and then run a query but I haven't committed my update, you'd see that 669 is running a SELECT statement and that it holds a lock. It was the earlier UPDATE (or INSERT or whatever) that the session had done that actually acquired the lock. There just isn't an easy way to see what earlier query the session had done that acquired the lock that other sessions are now waiting on.