Rob Tanner <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
ZFx, Inc. www.zfx.com
PGP key available at wwwkeys.us.pgp.net
