Re: using DBI for mutual exclusion across multiple servers

OK - now I'm *really* late for this....

But for Sybase you'll have to make sure that the table is created in "datarows" locking if you want to use this technique - otherwise the update will lock the entire page, and you won't have the granularity that you wish for.

The actual update/commit will be very cheap as your table fits on a single page.

As Sybase doesn't do multi-version concurrency the update simply writes the new page to the log. I would use a rollback instead of a commit here, as that will simply invalidate the log page rather than copying the new log page to the actual location of the table.

Note that if you have to connect() to the dataserver each time you need to set the mutex the connect() overhead will be much worse than the actual update....

Michael


On Feb 13, 2010, at 12:57 AM, Jonathan Swartz wrote:

> I need to guarantee that only one process at a time enters a subroutine foo() for a particular argument.
> 
> That is, if one process is in a call to foo(1), another call to foo(1) will block, but a call to foo(2) could proceed.
> 
> This needs to be guaranteed across multiple servers, as the calls to foo() manipulate multiple shared objects in the database.
> 
> Even though foo() isn't directly associated with one database table (and thus I can't rely on database transactions directly), I figured I could use the database to enforce the mutexes.
> 
> My idea was to create a mutexes table with, say, 1024 rows:
> 
>  create table mutexes (id int);
>  insert into mutexes values (0);
>  ...
>  insert into mutexes values (1023);
> 
> Then on a call to foo, I hash the argument to an integer in  0..1023 and reserve that row with an dummy update:
> 
>  sub foo {
>     my ($id) = @_;
> 
>     my $hash = $id % 1024;
>     my $dbh = DBI->connect(..., AutoCommit => 0);
>     $dbh->prepare("update mutexes set id = ? where id = ?", $hash, $hash);
> 
>     ...  # mutual exclusion guaranteed in here
> 
>     $dbh->commit();   # or $dbh->rollback() - not sure which is cheaper
>  }
> 
> I'm aware of the deadlocking potential of mutexes, but will avoid that by only reserving one row per process at a time. I'm also aware that some unnecessary serialization may occur due to hash collisions, but I'm not too worried about it and can always increase the # buckets if needed.
> 
> This seems to work in testing. Just wanted to find out if it makes sense, if there's a CPAN module that already does this (couldn't find one), or if there are problems that could cause this to blow up.
> 
> Thanks!
> Jon
> 
> 

--
Michael Peppler
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

"A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson