2013/12/12

FOR UPDATE SKIP LOCKED

One new thing I`ve learned from recent PL/SQL Challenge quiz is about how SELECT FOR UPDATE SKIP LOCKED works.

It does not really lock rows on EXEC phase of query execution (open cursor), when ordinary FOR UPDATE does.

Here are two excerpts from SQL Trace:
PARSING IN CURSOR #47354360485408 len=72 dep=1 uid=63 oct=3 lid=63 tim=1386839485248389 hv=4069083409 ad='539eab670' sqlid='csw3rbvt8kk8j'
SELECT * FROM PLCH_EMPLOYEES ORDER BY EMPLOYEE_ID FOR UPDATE SKIP LOCKED
END OF STMT
EXEC #47354360485408:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1212233107,tim=1386839485248389

PARSING IN CURSOR #47085989084664 len=60 dep=1 uid=63 oct=3 lid=63 tim=1386839807239479 hv=3207034820 ad='539de1f98' sqlid='aqqrp16zkfxy4'
SELECT * FROM PLCH_EMPLOYEES ORDER BY EMPLOYEE_ID FOR UPDATE
END OF STMT
PARSE #47085989084664:c=3000,e=2655,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=1212233107,tim=1386839807239478
EXEC #47085989084664:c=0,e=199,p=0,cr=7,cu=4,mis=0,r=0,dep=1,og=1,plh=1212233107,tim=1386839807239715 

No comments: