tag:blogger.com,1999:blog-17792779621574615972024-03-08T08:05:12.363-08:00Double barell noteshydrobionthttp://www.blogger.com/profile/09559478251685977754noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-1779277962157461597.post-25819278864118384432011-07-12T02:34:00.001-07:002011-07-12T02:43:05.537-07:00All Oracle 11gR2 docs in one page<a href="http://www.oracle.com/pls/db112/portal.all_books">http://www.oracle.com/pls/db112/portal.all_books</a><br />
<br />
Quite useful link indeed ;-)hydrobionthttp://www.blogger.com/profile/09559478251685977754noreply@blogger.com0tag:blogger.com,1999:blog-1779277962157461597.post-33143547775307210302011-06-28T02:41:00.000-07:002011-06-28T02:44:39.472-07:00select for update … skip locked in PostgreSQL<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div style="background-color: transparent; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; text-align: left;"><span id="internal-source-marker_0.5250634115654975" style="background-color: transparent; font-size: 11pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><span class="Apple-style-span" style="color: black; font-family: Arial;">At </span><a href="http://www.pgcon.org/2011/">PGCon’11</a><span class="Apple-style-span" style="color: black; font-family: Arial;"> I’ve </span><a href="http://www.pgcon.org/2011/schedule/events/286.en.html">told</a><span class="Apple-style-span" style="color: black; font-family: Arial;"> that </span><b><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: #274e13;">select for update … skip locked</span> </span></b><span class="Apple-style-span" style="color: black; font-family: Arial;"> is highly needed in PostgreSQL specially for using with various async messaging queues. Well, there is still no <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2060739900346201280">Oracle-like skip locked</a></span></span><span style="background-color: transparent; font-size: 11pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"><span class="Apple-style-span" style="color: black; font-family: Arial;">, but transactional behavior for advisory locks was introduced in PostgreSQL 9.1 - <a href="http://www.postgresql.org/docs/9.1/static/explicit-locking.html">http://www.postgresql.org/docs/9.1/static/explicit-locking.html</a> </span></span></div><div style="background-color: transparent; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; text-align: left;"><span class="Apple-style-span" style="font-family: Arial;"><span class="Apple-style-span" style="font-size: 15px; white-space: pre-wrap;"><br />
</span></span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">In version 9.0 and earlier advisory locks were not fully transactional: once acquired within transaction, lock will still be held even after calling rollback. So now looks possible to loop through your records trying to acquire advisory lock meaning if it fails your row already locked and you need to skip it.</span></div></div>hydrobionthttp://www.blogger.com/profile/09559478251685977754noreply@blogger.com0