It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What I’m referring to here is the default mechanism for locking in Oracle.
If you try access a row that is locked by someone else, you will wait. Don’t get me wrong … that’s a very good thing. The alternative – of letting two people change the same data at the same time is equivalent to saying “I dont care about my data at all”.
And how long will we wait ? Well… we’ll wait forever. We will never stop waiting, until that lock has been released.
I don’t know about you … but for me… forever is long time. A really long time. I cannot recall ever seeing any kind of Service Level Agreement for an application’s response times as stating “Yup…forever is cool. Take as long as you want”
So perhaps consider that when you’re building your applications in Oracle. Yes, the default is to wait forever, but a little code (which has been available since version 9) can really go a long way.
Rather than just coding:
select * from T where …for update
delete from T where …
You can opt to wait for a nominated amount of time to attempt to get that lock (and then report something sensible back to the user).
SQL> select * from T for update wait 60; (60 seconds pass) select * from T for update wait 60 * ERROR at line 1: ORA-30006: resource busy; acquire with WAIT timeout expired
Note that the error code is not the same as the error you could get with a NOWAIT clause, which is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
But you don’t even need to stop there. Why not catch that error with a SERVERERROR trigger – and then do some session level probing to get more information. You could even empower the users to take their own action 🙂
SQL> create or replace trigger lock_expiry 2 after servererror on database 3 declare 4 l_err varchar2(4000); 5 begin 6 if ( is_servererror(30006) ) then 7 for i in ( 8 select /*+ leading(lk sess) */ sess.sid, sess.username, sess.module 9 from v$lock lk, 10 v$session sess, 11 v$session s 12 where lk.id1 = s.row_wait_obj# 13 and lk.type = 'TM' 14 and lk.sid = sess.sid 15 and s.sid = sys_context('USERENV','SID') 16 ) 17 loop 18 l_err := l_err || 'Session '||i.sid||' who is '||i.username||' using '||i.module || chr(10); 19 end loop; 20 raise_application_error(-20000,l_err); 21 end if; 22 end; 23 / Trigger created. SQL> select * from t for update wait 60; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20000: Session 717 who is MCDONAC using SQL*Plus ORA-06512: at line 18 ORA-30006: resource busy; acquire with WAIT timeout expired
Just for completeness, note that if you having locking issue, V$SESSION gives you direct access to find out who is blocking you.
SQL> select sid, last_call_et, 2 nvl2(lockwait,'BLOCKED',status) status, 3 blocking_session 4 from v$session s; SID LAST_CALL_ET STATUS BLOCKING_SESSION ---------- ------------ ---------- ---------------- 39 376 INACTIVE 40 412 INACTIVE 41 412 INACTIVE 44 421 INACTIVE 46 340 BLOCKED 39 49 4 ACTIVE 50 453 INACTIVE 51 453 INACTIVE
DBA_WAITERS is a little known and seldom mentioned view that I use a lot to find out who is locking who.
A couple of comments.
1. Not all databases have catblock.sql run against them to create DBA_WAITERS.
2. Connor’s trigger code needs to be updated to run against gv$ views for RAC databases. I’ve seen enough “lock monitoring” tools that fail to return cross instance locks. v$session does contain blocking_instance so no need for gv views in the bottom code snippet, just an extra column.
Mind telling the Oracle guys who maintain Hyperion code about this?
Since release 11 the number of Deadlocks I’m seeing is astounding!
High time that got fixed…