Blocking sessions-locks in Oracle

Did you ever wanted to quickly find which sessions are blocking each other in Oracle?

In this post I will introduce an SQL script, which would quickly tell you if there is blocking between the sessions and which would also show you what SQL these sessions are using.

To see the script working, first create a dummy table and insert some test data.




SQL> create table t (a char(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;


Then select some rows from the dummy table for update.



SQL> select * from t where a='z' for update;

A
-
z



In second session try to update the rows which you have selected above. Due to locks this will block! ACID and serialization kicks in.


SQL> update t set a='x' where a='z';


It will just hung!

To see what is blocking, run this query in a third session as SYSDBA.


SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1,
v$session s1 ,
v$lock l2 ,
v$session s2 ,
v$sql sqlt1 ,
v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
/


BLOCKING_STATUS
--------------------------------------------------------------------------------
15-JAN-2009 07:41:27 User hero@world.com ( SID= 144 ) with the statemen
t: select * from t where a='z' for update is blocking the SQL statement on hero@world.com ( SID=147 ) blocked SQL -> update t set a='x' where a='z'