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;
...
Wednesday, March 4, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment