Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

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.

Comment on DBI and PostgreSQL advisory locks
Download Code
Re: DBI and PostgreSQL advisory locks
by roboticus (Canon) on Nov 21, 2009 at 17:34 UTC
    techcode:

    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.

    ...roboticus

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://808537]
Approved by johngg
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2014-08-02 08:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (55 votes), past polls