|Subject||RE: [ib-support] SELECT COUNT(*) ... - slow|
> 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.
I have used the "update single count value" method
and have experienced that occasionally the count
value is slightly in error (no deadlocks though).
I should change the logic to your algorithm.