Re: concurrent updates problem

From: <mwaples(at)waples(dot)net>

> I have on a web application
> update threads set views = views + 1 where forum_id = 1 and thread_id =
> 1
> just to record view for that page - stupid I know but people want it.
> Problem comes when I use apache's ab to test it.
>
> It just doesnt produce the correct result with 10 or so concurrent
> connections. The views ends up being completely out.(no failed queries)

So the views total is less than you'd expect.

> I read up on transaction Isolations , using select for update etc
> but can't get it to work correctly - I thought serilizable might do the
> trick but only few queries were rolled back -
> Can anyone point me in the right direction to solve this ?

Can't think what difference it should make how many connections you have.
The update you show will take place in its own transaction so should always
be consistent. The query should block if it needs to wait on another process
updating.

I can only think that you might not be checking for success/failure at some
point in the code. If you can boil it down to a small Perl/PHP script I can
try and duplicate it here - if not, post the relevant code and we'll see if
more eyeballs can't spot something.

- Richard Huxton