Oracle programmer's blog

I support this blog for my own fun. It is dedicated to the Oracle programming aspects I believe worth to publish and keep in memory.

Blog Archive

Wednesday, March 4, 2009

SKIP LOCKED and the locking moment

SELECT ... FOR UPDATE statement locks data at the moment of a cursor opening.
It's a conceptual thing because SELECT ... FOR UPDATE has to return locked (serialized) consistent snapshot. Really, let's open FOR UPDATE cursor in transaction A:

SQL> REM Transaction A
SQL> var rc refcursor
SQL> exec open :rc for select ename from emp where ename in ('KING','ALLEN') for update;

PL/SQL procedure successfully completed.

Then in transaction B we can't lock 'KING' row until commit or rollback in transaction A:

SQL>REM We wait for A completion
SQL> select ename from emp where ename = 'KING' for update;
...

FOR UPDATE SKIP LOCKED voilates the concept of serialized snapshot - and now we
don't need to try to lock rows before fetch:

SQL>REM Transaction A
SQL> exec open :rc for select ename from emp where ename in ('KING','ALLEN') for update skip locked;

PL/SQL procedure successfully completed.

We can lock 'KING' in another transaction (no lock before fetch):

SQL>REM Transaction B
SQL> select ename from emp where ename = 'KING' for update;

ENAME
----------
KING

Fetching of SKIP LOCKED cursor ignores 'KING'...

SQL>REM Transaction A
SQL> print rc

ENAME
----------
ALLEN

...and locks 'ALLEN' row at the moment of fetching (we are waiting for commit/rollback in A):

SQL> REM Transaction B - we are waitnig for A completion
SQL> select ename from emp where ename = 'ALLEN' for update;
...

No comments: