Is there some little known trick when you wan't to use PostgreSQL's advisory locks from Perl?

I first created a function that does:
1. get a lock (exclusive)
2. get some stuff from a table that's not marked as taken, update those rows as taken, and with info what pid took them, with a couple of other things for which I can't lock the rows or their id's and need basically a semaphore
3. unlock

And when I realized that more than one process is ending up with same table row as taken by them (writing to their output table was failing with duplicate key error) - I though, doup, the lock is released (and next process in queue gets the lock) before the transaction is commit-ed and there you go a racing condition.

So I changed it to something like (in reality function call is wrapped in eval, and there is a if($@) ...rollback/commit after that): 

my $prepared_lock = $db->prepare("SELECT pg_advisory_lock(?)");
my $prepared_unlock = $db->prepare("SELECT pg_advisory_unlock(?)");

....
$prepared_lock->execute(123);
$db->{AutoCommit} = 1;
$db->begin_work();
$prepared_function_call->execute(....);
$db->commit();
$prepared_unlock->execute(123);

[download]

Well if that was working - I wouldn't be writing here :) Is there something I missed in some document or somewhere? I checked my sanity by trying out the things manually with several psql's open, and from there it's working - second lock is blocked until first is released. I'm in process of trying to $db->do("SELECT pg_ad...."); instead of prepare/execute. And if that doesn't change the way it's working - I'm planning on writing a dead simple test code that will try to get two locks on two separate DB connections. Any suggestions?
Have you tried freelancing/outsourcing? Check out Scriptlance - I work there since 2003. For more info about Scriptlance and freelancing in general check out my home node.

In reply to DBI and PostgreSQL advisory locks by techcode

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.