I have updated the INSERT code to use the "ON DUPLICATE KEY" feature of
MySQL. This feature insures that if the insert statement is executed
and another insert has already occurred for the changing day, an update
will take place instead. Hopefully, this will alleviate the largest
possibility of a race condition. However, this is a feature of MySQL
that is only available inside of MySQL 4.1 and greater. If you are NOT
running at least MySQL 4.1 you can get the old code from my website
listed below.
Where to get the changes -
http://wiki.apache.org/spamassassin/StatsPlugin
or
http://www.okeating.net/blosxom.cgi/2005/12/31#statsplugin-update
Let me know if you see any other issues or problems.
Thanks for everyone's input, more is always appreciated.
Thanks,
James
Jim C. Nasby wrote:
On Wed, Dec 28, 2005 at 02:19:51AM -0600, Chris Thielen wrote:
James Keating wrote:
Well the simplest fix is the one that I did not implement in the first
place, using "ON DUPLICATE KEY". However, I did not implement that
because of its only being in version 4.1 of MySQL and I still use
Debian stable for most production machines, which runs 4.0.x.
Anyway, I will poke at it some more.
Read on for a portable fix called optimistic locking.
Pseudocode follows. I assume you are doing something like this:
You assume wrong. :) The code is doing
UPDATE ... SET field = field + 1 ...
which is almost always a much better idea than doing the increment in
code.
The race condition is in checking to see if the row already exists, and
then using that information to decide if you should do an insert or an
update.
The example at the very bottom of http://lnk.nu/postgresql.org/5sl.html
is how you would do this in a function in PostgreSQL, though there's no
reason I can think of why you couldn't do that in perl instead (though
it would perform much slower).
