Re: Sequences in transaction

On Mon, Dec 04, 2000 at 05:56:04PM -0500, Camm Maguire wrote:
> Greetings! I've run into this too, and it appears to me not to roll
> back either. A pity, as it forced us to make a separate table to hold
> the last sequence value and update the table in a trigger.

This is a dangerous thing if you ever have more than one update going on.

One transaction reads the value, increments it, saves it back. Another
transaction does the same thing. Now you have two transactions trying to
use the same value. Sure, one will probably fail a uniqueness constraint,
but then you have to increment again. Meanwhile, another transaction comes
in and steals the next number, and one of the first two clashes again. And
again. And again. And again. You have NO way of guaranteeing that
starvation will never be an issue.

Solution:

Don't require that your sequence values be absolutely sequential. They're
there to ensure uniquness and order. Not to be used as counters.

mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda(at)ix(dot)netcom(dot)com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen