Re: using DBI for mutual exclusion across multiple servers

Ok. Sounds like this is more expensive than I expected (at least in  
Oracle's case).

Does it save any cost that the update is a "dummy" update, "update id  
= 1 where id = 1"? That is, I'm only using the update to reserve the  
row, not actually changing any data.

Jon

On Mar 10, 2010, at 2:22 PM, Jared Still wrote:

>
>
> Sorry, I am a little late to the party, as I have not been keeping  
> up on email so well.
>
> Comments inline:
>
> On Fri, Feb 12, 2010 at 3:57 PM, Jonathan Swartz <[email protected]>  
> wrote:
>     ...  # mutual exclusion guaranteed in here
>
>     $dbh->commit();   # or $dbh->rollback() - not sure which is  
> cheaper
>  }
>
>
> Neither is very cheap, but I would guess that in most cases
> the commit is cheaper.
>
> I'm only really familiar with Oracle, in which case the commit is  
> may be
> cheaper in the short term, but still expensive in the long term as  
> activity
> scales up in the app.
>
> I would not call either inexpensive.
>
> In general:
>
> update/commit:
> update the current cache block i
> write old data to undo
> update the redo buffer
> post the log writer to write redo to disk
> data in datafile may or may not be written to disk at this time
>
> update/rollback:
> update the current cache block
> write old data to undo
> read the old data back from undo
> update the current cache block
> there may or may not be an update to the redo buffer here, can't  
> recall.
>
> If there are many users doing this simultaneously there will be
> some hot blocks in the mutexes table.  This may cause some
> scaling issues. In Oracle a solution to that would be to ensure
> that each row is in a different database block.
>
> If very many commits are being performed for this, it will kill your  
> database performance.
>
> Then there is the issue of resolving how the table is cleaned up  
> when the client
> disconnects without issuing a rollback or commit.  The default may  
> be either, but
> your program will need to know how to deal with it.
>
> I guess what I'm trying to say is there is probably a better way to  
> do this.  :)
>
>
> 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.
>
>
> Have you taken care to test in a environment that will push it as  
> hard as production?
>
> Have you made sure that some clients disconnect at inopportune times?
>
> Just providing a few things to consider.  :)
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
>