RE: Sequences in transaction

Not even to ensure order.  Some databases allow sequence caching (something we should perhaps look at, if we don't already) which means that when a session requests it's first number from a sequence a whole bundle are actually given to the session.  It may not even use them all, and certainly, if another session is also allocated a bundle of sequence numbers from the same sequence, then different transactions could commit them in any combination of orders.  However, the principle of uniqueness will not be violated.

Cheers...

MikeA

-----Original Message-----
From: Mike Castle [mailto:dalgoda@ix.netcom.com]
Sent: 04 December 2000 23:30
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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@ix.netcom.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

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************