Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

DBI and PostgreSQL advisory locks

by techcode (Hermit)
on Nov 20, 2009 at 22:22 UTC ( #808537=perlquestion: print w/replies, xml ) Need Help??
techcode has asked for the wisdom of the Perl Monks concerning the following question:

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);

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.

Replies are listed 'Best First'.
Re: DBI and PostgreSQL advisory locks
by roboticus (Chancellor) on Nov 21, 2009 at 17:34 UTC

    Wrap all your operations in a single transaction and let the database do the work for you. More like:

    $db->{AutoCommit}=0; $db->begin_work(); $db->do(operation 1); $db->do(operation 2); $db->do(operation 3); $db->commit();

    Once you start a transaction, the database should prevent you from updating the same rows until you complete the current one.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://808537]
Approved by johngg
Front-paged by Arunbear
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2017-08-19 05:47 GMT
Find Nodes?
    Voting Booth?
    Who is your favorite scientist and why?

    Results (310 votes). Check out past polls.