You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Rob Tanner <rt...@onlinemac.com> on 2000/07/13 01:22:43 UTC
DBI:mysql question
I want to use advisory locks in MySQL. The functuion is GET_LOCK(), and
the way it should be used is SELECT GET_LOCK(). It return 1 is successful,
0 if timed out, and undef on error. The sequence:
my $db_lock = 'GET_LOCK("mylock", 5)';
my $result = $dbh->do($db_lock);
returns "1" regardless of whether the lock was obtained. According to the
cheetah book (Programming the Perl DBI), page 120/1, that's correct. The
$dbh->do statement only returns "0" on an error.
How do I execute the MySQL GET_LOCK function and get back the real result
(0 or 1). Always getting back a 1 as if I got the lock, whether I did or
not, kind of defeats the purpose.
What is the work-around?
Thanks,
Rob
_ _ _ _ _ _ _ _ _ _
/\_\_\_\_\ /\_\ /\_\_\_\_\_\
/\/_/_/_/_/ /\/_/ \/_/_/_/_/_/ QUIDQUID LATINE DICTUM SIT,
/\/_/__\/_/ __ /\/_/ /\/_/ PROFUNDUM VIDITUR
/\/_/_/_/_/ /\_\ /\/_/ /\/_/
/\/_/ \/_/ /\/_/_/\/_/ /\/_/ (Whatever is said in Latin
\/_/ \/_/ \/_/_/_/_/ \/_/ appears profound)
Rob Tanner
McMinnville, Oregon
rtanner@onlinemac.com
Re: [OT] DBI:mysql question
Posted by Ken Williams <ke...@forum.swarthmore.edu>.
chip@zfx.com (Chip Turner) wrote:
>A one-line example of this can be tested by running the following in
>two shell windows:
>
>$ perl -MDBI -e 'my $dbh = DBI->connect("DBI:mysql:mysql", "yy", "xx");
> my $sth = $dbh->prepare("SELECT GET_LOCK(\"mysql\", 5)"); $sth->execute;
> my ($lock) = $sth->fetchrow; print "lock: $lock\n"; sleep 10'
Is there a reason not to use selectrow_array for locks?
$ perl -MDBI -e 'my ($lock) = DBI->connect("DBI:mysql:mysql", "yy", "xx")
->selectrow_array("SELECT GET_LOCK(\"mysql\", 5)");
print "lock: $lock\n"; sleep 10'
------------------- -------------------
Ken Williams Last Bastion of Euclidity
ken@forum.swarthmore.edu The Math Forum
Re: DBI:mysql question
Posted by Chip Turner <ch...@zfx.com>.
Rob Tanner <rt...@onlinemac.com> writes:
> I want to use advisory locks in MySQL. The functuion is GET_LOCK(),
> and the way it should be used is SELECT GET_LOCK(). It return 1 is
> successful, 0 if timed out, and undef on error. The sequence:
>
> my $db_lock = 'GET_LOCK("mylock", 5)';
> my $result = $dbh->do($db_lock);
>
> returns "1" regardless of whether the lock was obtained. According to
> the cheetah book (Programming the Perl DBI), page 120/1, that's
> correct. The $dbh->do statement only returns "0" on an error.
>
> How do I execute the MySQL GET_LOCK function and get back the real
> result (0 or 1). Always getting back a 1 as if I got the lock,
> whether I did or not, kind of defeats the purpose.
>
> What is the work-around?
The trick with this is to read the results from the SELECT statement
using fetchrow. For instance:
my $sth = $dbh->prepare('SELECT GET_LOCK("mytable", 5)');
$sth->execute;
my ($lock) = $sth->fetchrow;
$lock is now 1, 0, or undef.
A one-line example of this can be tested by running the following in
two shell windows:
$ perl -MDBI -e 'my $dbh = DBI->connect("DBI:mysql:mysql", "yy", "xx");
my $sth = $dbh->prepare("SELECT GET_LOCK(\"mysql\", 5)"); $sth->execute;
my ($lock) = $sth->fetchrow; print "lock: $lock\n"; sleep 10'
Hope this helps,
Chip
--
Chip Turner chip@ZFx.com
ZFx, Inc. www.zfx.com
PGP key available at wwwkeys.us.pgp.net
Re: DBI:mysql question
Posted by Rob Tanner <rt...@onlinemac.com>.
Thanks Dana and Chip. That did it!
--On Wednesday, July 12, 2000 4:25 PM -0700 Dana Powers
<da...@quicknet.net> wrote:
> You'll need to use $sth = $dbh->prepare(...); and $sth->execute();
> Then ($lock) = $sth->fetchrow_array(); and $sth->finish();
> $dbh->do assumes that you are inserting or updating and so you dont
> expect any results back. A GET_LOCK does not work like that, as you have
> noted, so you will need to treat it like a select statement. I use these
> locks to simulate a row-level locking system and it works quite well.
> Dana
>
>
> On Wed, 12 Jul 2000, Rob Tanner wrote:
>> I want to use advisory locks in MySQL. The functuion is GET_LOCK(), and
>> the way it should be used is SELECT GET_LOCK(). It return 1 is
>> successful, 0 if timed out, and undef on error. The sequence:
>>
>> my $db_lock = 'GET_LOCK("mylock", 5)';
>> my $result = $dbh->do($db_lock);
>>
>> returns "1" regardless of whether the lock was obtained. According to
>> the cheetah book (Programming the Perl DBI), page 120/1, that's
>> correct. The $dbh->do statement only returns "0" on an error.
>>
>> How do I execute the MySQL GET_LOCK function and get back the real
>> result (0 or 1). Always getting back a 1 as if I got the lock, whether
>> I did or not, kind of defeats the purpose.
>>
>> What is the work-around?
>>
>> Thanks,
>> Rob
>>
>> _ _ _ _ _ _ _ _ _ _
>> /\_\_\_\_\ /\_\ /\_\_\_\_\_\
>> /\/_/_/_/_/ /\/_/ \/_/_/_/_/_/ QUIDQUID LATINE DICTUM SIT,
>> /\/_/__\/_/ __ /\/_/ /\/_/ PROFUNDUM VIDITUR
>> /\/_/_/_/_/ /\_\ /\/_/ /\/_/
>> /\/_/ \/_/ /\/_/_/\/_/ /\/_/ (Whatever is said in Latin
>> \/_/ \/_/ \/_/_/_/_/ \/_/ appears profound)
>>
>> Rob Tanner
>> McMinnville, Oregon
>> rtanner@onlinemac.com
_ _ _ _ _ _ _ _ _ _
/\_\_\_\_\ /\_\ /\_\_\_\_\_\
/\/_/_/_/_/ /\/_/ \/_/_/_/_/_/ QUIDQUID LATINE DICTUM SIT,
/\/_/__\/_/ __ /\/_/ /\/_/ PROFUNDUM VIDITUR
/\/_/_/_/_/ /\_\ /\/_/ /\/_/
/\/_/ \/_/ /\/_/_/\/_/ /\/_/ (Whatever is said in Latin
\/_/ \/_/ \/_/_/_/_/ \/_/ appears profound)
Rob Tanner
McMinnville, Oregon
rtanner@onlinemac.com
Re: DBI:mysql question
Posted by Dana Powers <da...@quicknet.net>.
You'll need to use $sth = $dbh->prepare(...); and $sth->execute();
Then ($lock) = $sth->fetchrow_array(); and $sth->finish();
$dbh->do assumes that you are inserting or updating and so you dont expect any
results back. A GET_LOCK does not work like that, as you have noted, so you
will need to treat it like a select statement. I use these locks to simulate a
row-level locking system and it works quite well.
Dana
On Wed, 12 Jul 2000, Rob Tanner wrote:
> I want to use advisory locks in MySQL. The functuion is GET_LOCK(), and
> the way it should be used is SELECT GET_LOCK(). It return 1 is successful,
> 0 if timed out, and undef on error. The sequence:
>
> my $db_lock = 'GET_LOCK("mylock", 5)';
> my $result = $dbh->do($db_lock);
>
> returns "1" regardless of whether the lock was obtained. According to the
> cheetah book (Programming the Perl DBI), page 120/1, that's correct. The
> $dbh->do statement only returns "0" on an error.
>
> How do I execute the MySQL GET_LOCK function and get back the real result
> (0 or 1). Always getting back a 1 as if I got the lock, whether I did or
> not, kind of defeats the purpose.
>
> What is the work-around?
>
> Thanks,
> Rob
>
> _ _ _ _ _ _ _ _ _ _
> /\_\_\_\_\ /\_\ /\_\_\_\_\_\
> /\/_/_/_/_/ /\/_/ \/_/_/_/_/_/ QUIDQUID LATINE DICTUM SIT,
> /\/_/__\/_/ __ /\/_/ /\/_/ PROFUNDUM VIDITUR
> /\/_/_/_/_/ /\_\ /\/_/ /\/_/
> /\/_/ \/_/ /\/_/_/\/_/ /\/_/ (Whatever is said in Latin
> \/_/ \/_/ \/_/_/_/_/ \/_/ appears profound)
>
> Rob Tanner
> McMinnville, Oregon
> rtanner@onlinemac.com