can someone shed more light into the new feature of auto generated
sequences?
are they just for convenience or do they eliminate the concurrency
issues associated with
unique key generation? because I see that they take logical locks

is it possible to introduce gaps between the sequence by means of
server failure, aging out of the shared pool in case of caching, or
programatically?

Hi Niko,

There certainly convenient, and less prone to lock problems. The
sequence largely lives in the server itself so a logical lock is taken
in there. So speed of access should be dramatic...unless of course you
set the nocache options.

Yes to all the above. But quite frankly gapless sequences are just not
worth the hassle.

Martin Bowes

nikosv wrote:

I wouldn't say they eliminate concurrency issues. They certainly reduce
the risk of lock contention (and hence queueing) on the last-value
table. For the knowledgeable programmer the benefit is just the
convenience of not having to write the small amount of code needed
to generate values. The big winners are the naive programmers who
didn't even know there was a problem.

But contention on the state table is only part of the problem.
Using monotonic physical key values to insert into a B-tree or an ISAM
table is still a massive concurrency problem in a busy system. You can
relieve it somewhat by using row-level locking, but there's limits to
how far even row-level locking will scale, and there is no guarantee you
will get row-level locking even if you ask for it.

Ingres Database 9.3 includes (or soon will include) "unordered"
sequences, which supply unique values in non-sequential order.
Using these values as physical keys will effectively randomize insertion
into B-trees and ISAM tables, eliminating concurrency problems.

You certainly still run the risk of gaps. But gapless sequences are a
perforance killer no matter how you generate the values. You can't
allocate the next value until the previous value has been used AND
committed. That makes your system essentially single-user. It will not
scale at all. The preferred solution (preferred by the programmer if
not the business) is auditable numbers. You generate a block of
numbers and store their current disposition in a table in the database;
you dole them out as a separate operation as needed. You don't
insist on using them up in strict order, but you do require them to be
traceable, so that you can supply information like: the numbers 9, 10,
11, and 12 have been generated, and the numbers 9,10, and 12 have been
used (and committed), but 11 is still in the pool waiting to be used,
and will be used before we generate another block of numbers.

The one time I have had to deal with genuinely in-order gapless
numbers in real life was when I was outputting onto pre-printed
certificates that already had a serial number, and I had to know the
number of each certificate I was printing. But in that case the
numbers were really a natural key. (The same problem arises with
pre-printed cheques.) But happily those are both single-user jobs so no
problem. Other than that, the need for gapless numbering is rare, I
find. If your users are trying to tell you different, I'd ask them
some hard questions before meekly giving in to their request.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Roy:

I need to contact you. Could you email me please?

Thanks
Roger
****************************************
Roger Hill
****************************************

rhill (AT) hillconsult (DOT) com wrote:

Hi Rog. Done.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

On 3 Öåâ, 13:53, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
great in-depth explanations, clarifies it