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
