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.Read on for a portable fix called optimistic locking.Anyway, I will poke at it some more.
Pseudocode follows. I assume you are doing something like this:
select ham from table where user = $user;
$ham++;
update table set ham = $ham where user = $user;
You can instead do something like this:
while (!success) {
select ham from table where user = $user;
$newham = $currentham + 1;
update table set ham = $newham where user = $user and ham = $currentham;
success = (getRowsUpdated() > 0);
}
What this does is update the row only if the value of table.ham has not
changed. The updated row count is checked. If it is greater than 0
(your row was indeed updated) then you set the success flag and
continue. If the updated row count is 0 you know the data has changed
unexpectedly and must redo the entire read/modify/write cycle.
Usually this approach is done by adding a separate version column that is tested and updated, but in this case the ham or spam counters can be substituted. It is called optimistic locking because you assume (optimistically) that your update will usually succeed; eg: that nobody else has updated the data without you knowing. It's not really locking, but rather a concurrent update detection mechanism which the application then must handle programatically.
signature.asc
Description: OpenPGP digital signature
