Re: concurrent updates problem

Richard Huxton wrote:
>
> 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.

no views are actually more - for 20 access it can be 30 -40 views -
varies all the time, sometimes views is less.

> > 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.

I haven't been checking for success or failure - I thought I'll have to
test for failure - but the views were greater than then they should be.

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

Im using aolserver - but its Im writing it in php later too.
code is below

set sql "BEGIN WORK"
ns_db exec $db $sql

set sql "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
ns_db exec $db $sql

set sql "UPDATE threads set views = views + 1 WHERE forum_id = $forum_id
AND thread_id = $thread_id"
ns_db dml $db $sql

set sql "COMMIT WORK"
ns_db exec $db $sql

I ve also tried just

set sql "UPDATE threads set views = views + 1 WHERE forum_id = $forum_id
AND thread_id = $thread_id"
ns_db dml $db $sql

they both produces sometimes correct results, sometimes under what the
result should be and sometimes over it.
But 5 or 6 concurrent requests always produce the correct results, any
more than that things go wrong.