tag:blogger.com,1999:blog-7395977411859619892.post692162724929834662..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: FOR UPDATE SKIP LOCKEDRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-7395977411859619892.post-5268091420435904352016-08-16T20:28:25.442+02:002016-08-16T20:28:25.442+02:00Nice Article !
This is my pleasure to read your ar...Nice Article !<br />This is my pleasure to read your article.<br />Really this will help to people of PostgreSQL Community. <br /><br />I have also prepared one article about, PostgreSQL 9.5 FOR UPDATE SKIP LOCKED option to select only committed records.<br />You can also visit my article, your comments and reviews are most welcome. <br /><br />http://www.dbrnd.com/2016/08/Anonymoushttps://www.blogger.com/profile/01365030762397385247noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-35346536977538933132013-08-22T11:47:16.938+02:002013-08-22T11:47:16.938+02:00Please refer great example with different scenario...Please refer great example with different scenarios.<br />http://www.ghanchiasif.blogspot.in/2013/06/some-good-things-to-learn-for-update-in.htmlAsif Ghanchihttps://www.blogger.com/profile/07769607380863603617noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-9458929240162502352009-10-20T22:21:51.567+02:002009-10-20T22:21:51.567+02:00Rob,
Yes, you are correct. Rows get locked after f...Rob,<br />Yes, you are correct. Rows get locked after fetch and locks are held until a commit/rollback occurs.<br />I don't know what I was thinking!<br />Thank you.Developernoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-13263992200408323942009-10-18T10:28:30.868+02:002009-10-18T10:28:30.868+02:00Developer,
> What I see is that the rows are n...Developer,<br /><br />> What I see is that the rows are not locked when you open the cursor.<br /><br />That's what this blogpost is about: when using FOR UPDATE SKIP LOCKED, records are locked when they are fetched, not when the cursor is opened.<br /><br />> They are not even locked when you fetch them. They are locked when you update them and the lock is released right away even Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-38288706345021090312009-10-15T18:26:23.511+02:002009-10-15T18:26:23.511+02:00... Continued.
-- Now run following block in one ...... Continued.<br /><br />-- Now run following block in one session.<br />SQL> l <br /> 1 DECLARE<br /> 2 CURSOR c<br /> 3 IS<br /> 4 SELECT id, name<br /> 5 FROM t<br /> 6 FOR UPDATE SKIP LOCKED;<br Developernoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-52509707966771382002009-10-15T18:25:36.322+02:002009-10-15T18:25:36.322+02:00Rob,
Here is a test case for SKIP LOCKED behavior...Rob,<br /><br />Here is a test case for SKIP LOCKED behavior.<br /><br />-- create a table T<br />CREATE TABLE t (id NUMBER, name VARCHAR2 (10), hire_time timestamp);<br /><br />-- Force use for binds.<br />ALTER SESSION SET cursor_sharing=force;<br /><br />INSERT INTO t VALUES (1, 'a', NULL);<br />INSERT INTO t VALUES (2, 'a', NULL);<br />INSERT INTO t VALUES (3, 'a', Developernoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-23395635781873918742009-04-09T23:56:00.000+02:002009-04-09T23:56:00.000+02:00Hi Randolf,I think I now understand what you mean....Hi Randolf,<BR/><BR/>I think I now understand what you mean. Probably the sentence "If a SELECT FOR UPDATE statement is used when defining a cursor ..." should be read as "If a SELECT FOR UPDATE statement is used when Oracle defines a cursor under the covers like in any SQL statement ...", rather than your explanation.<BR/><BR/>Different behaviour for SQL statements depending on their calling Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-40026526648606259762009-04-09T22:14:00.000+02:002009-04-09T22:14:00.000+02:00Rob,sorry for the confusion. As mentioned in my co...Rob,<BR/><BR/>sorry for the confusion. As mentioned in my comment there is nothing mentioned about the SKIP LOCKED option in particular. The part I was wondering about is the following:<BR/><BR/>"If a SELECT FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked when the cursor is opened (before the first fetch) rather than being locked as they are fetched fromRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-26110309903527149102009-04-09T21:58:00.000+02:002009-04-09T21:58:00.000+02:00Hi Randolf,Thanks for your comment, but unfortunat...Hi Randolf,<BR/><BR/>Thanks for your comment, but unfortunately I don't understand your point, even after reading it multiple times :-).<BR/><BR/>I cannot find the quote you refer to in that paragraph and I can't find a piece of text hinting to the behaviour described in this blog post. I thought the chapter was clear.<BR/><BR/>Can you explain what part caught your attention?<BR/><BR/>Regards,<BRRob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-31787317385717954352009-04-06T10:11:00.000+02:002009-04-06T10:11:00.000+02:00Rob,I recently stumbled upon this interesting para...Rob,<BR/><BR/>I recently stumbled upon this interesting paragraph in the 11g Advanced Application Developers Guide (which was already there in the 10g and 9i versions of the manuals, so not really new):<BR/><BR/>http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_sqlproc.htm#i1025003<BR/><BR/>It doesn't explicitly mention the different behaviour of the SKIP LOCKED option but someRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-83741476001952389182009-02-09T21:41:00.000+01:002009-02-09T21:41:00.000+01:00Rob,thanks for the clarification, and yes, you're ...Rob,<BR/><BR/>thanks for the clarification, and yes, you're right, I was referring to the cursor-for-loop optimization but that doesn't apply to your single fetch call. Good catch!<BR/><BR/>Best Regards,<BR/>RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-17800822861625343152009-02-09T21:27:00.000+01:002009-02-09T21:27:00.000+01:00Hi Randolf,I've never seen Oracle automatically fe...Hi Randolf,<BR/><BR/>I've never seen Oracle automatically fetch 100 rows when issuing a single fetch call. When does that happen? I have seen the PL/SQL optimization from version 10 onwards transforming a cursor-for-loop into a 100-row bulk fetching loop under the covers.<BR/><BR/>When I leave out the fetch call in the for update scenario, the results are the same: two records locked.<BR/><BR/>ItRob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-31928132402250607022009-02-09T21:21:00.000+01:002009-02-09T21:21:00.000+01:00Hi Chen,I see Randolf already answered/explained t...Hi Chen,<BR/><BR/>I see Randolf already answered/explained the situation well. When waiting for a lock, the SQL statement already started and the results are retrieved as from this time.<BR/><BR/>The code reminded me of <A HREF="http://rwijk.blogspot.com/2008/01/sequence-within-parent.html" REL="nofollow">this old blog post of mine</A>. And as you said: you should be using a sequence. And if you Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-13705419755558799572009-02-09T14:29:00.000+01:002009-02-09T14:29:00.000+01:00Chen,by the way: Your first example regarding the ...Chen,<BR/><BR/>by the way: Your first example regarding the "race" condition represents a design flaw. That's not the way the FOR UPDATE is meant to be used in order to generate a unique ID. Obviously using a sequence is the most efficient approach, although it's not guaranteed to be gap-free, if this was the original intention of that code snippet.<BR/><BR/>Regarding your MERGE example: This hasRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-70117049881284645292009-02-09T12:15:00.000+01:002009-02-09T12:15:00.000+01:00Rob,very interesting and a fundamental difference ...Rob,<BR/><BR/>very interesting and a fundamental difference in behaviour.<BR/><BR/>One particular detail that shouldn't change the outcome: Your first attempt using the normal "FOR UPDATE" clause actually fetches from the cursor opened, whereas the "FOR UPDATE SKIP LOCKED" code doesn't before sleeping for 10 secs.<BR/><BR/>I'm asking this because in recent releases by default Oracle fetches bulksRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-25720380366146910262009-02-08T19:29:00.000+01:002009-02-08T19:29:00.000+01:00I had a nice race condition yesterday, when I actu...I had a nice race condition yesterday, when I actually used "for update"!<BR/><BR/>Consider:<BR/>session 1 runs:<BR/>select id into max_id from my_table where id=(select max(id) from my_table) for update;<BR/><BR/>insert into my_table values (max_id+1,some_data);<BR/><BR/>session 2 runs same code<BR/>session 1 commits;<BR/>session 2 commits;<BR/><BR/>first session got max_id of 1, and locked the Chen Shapirahttps://www.blogger.com/profile/14535067086703072776noreply@blogger.com