Subject RE: [ib-support] SELECT COUNT(*) ... - slow
Author Ann Harrison
At 01:16 PM 4/9/2002 +0300, mikko.laiho@... wrote:

>How to guarantee that there is only one transaction at the
>same time updating the count?

That's easy - only one transaction at a time will succeed.
You probably don't want to deal with all the deadlock errors
that will cause.

A deadlock free way to handle the count is to insert
values rather than updating a single value. At the
start of the day, you might have a single value "32767".
During the day, various transactions will store their
changed value (e.g -4, +10, -32768) To get the count,
request the sum of those values. From time to time,
run a program that computes the sum, updates one record,
and deletes all the others. If it deadlocks, roll it
back and try again.