2011-11-17

Skip locked


SQL SELECT SKIP LOCKED not mentioned in new features part of the SQL document. Seems to be there already in 11gr1.


SQL> create table skiplocked as select  level n from dual connect by level < 3;

Table created.

SQL> select * from skiplocked where n=1 for update;

         N
----------
         1


At the same time another session


SQL> select * from skiplocked;

         N
----------
         1
         2

SQL> select * from skiplocked for update skip locked;

         N
----------
         2

As the 11gr1 have been around for a while. Rob Van Wijk has written something about the feature.
http://rwijk.blogspot.com/2007/12/parallellism-in-skip-locked-scenario.html
http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
"when using FOR UPDATE SKIP LOCKED, records are locked when they are fetched, not when the cursor is opened" Need to read those again when the need for this feature comes...

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.