Handling Deadlocks With SELECT FOR UPDATE SKIP LOCKED In Oracle

In my previous post I have mentioned sample deadlock scenario in Oracle database.
For data integrity, oracle locks the row which you tend to update with FOR UPDATE keywords in SELECT statement. There is an undocumented feature of oracle that skips locked rows. It can be useful to pevent deadlocks. Look at example below:


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> drop table a;

Table dropped

SQL> create table a( x number, y varchar2(1) );

Table created

SQL> INSERT INTO a VALUES (1, 'X');

1 row inserted

SQL> INSERT INTO a VALUES (2, 'Y');

1 row inserted

SQL> commit;

Commit complete

SQL>

------

--SESSION1
SQL> SELECT * FROM a WHERE x = 1 for update;
X          Y
---------- -
1          X

--SESSION2
SQL> SELECT * FROM a WHERE x = 2 for update;
X          Y
---------- -
2          Y

SQL> SELECT * FROM a WHERE x = 1 for update;
--Now SESSION2 waits...

--SESSION1
SQL> SELECT * FROM a WHERE x = 2 for update;
X          Y
---------- -
2          Y
--Now SESSION1 also waits...

--SESSION2
--Oracle selects SESSION2 victim and terminate.

SELECT * FROM a WHERE x = 1 for update

ORA-00060: deadlock detected while waiting for resource

Other is using SKIP LOCKED as described below:
--SESSION1
SQL> SELECT * FROM a WHERE x = 1 for update skip locked;
X          Y
---------- -
1          X

--SESSION2
SQL> SELECT * FROM a WHERE x = 2 for update skip locked;
X          Y
---------- -
2          Y

SQL> SELECT * FROM a WHERE x = 1 for update skip locked;
--No WAIT. But return no rows!
X          Y
---------- -

--SESSION1
SQL> SELECT * FROM a WHERE x = 2 for update skip locked;
--No WAIT. But return no rows again!
X          Y
---------- -