Skip to main content

Auto generated sequences, the silver bullet for concurrency ?

Ingres incorporated automatic generated sequences, a feature that was built into other dbms products, into version 9 onwards.

So are automatic generated sequences just for convenience or are they really useful?

They have many advantages which are speed, especially when caching is enabled, scalability, and elimination of concurrency issues associated with unique key generation.

But the drawback is that they can introduce gaps in the sequence in a variety of ways :
since auto generated sequences are an object that lives in the server, on server failure
it is lost. The same is true in case of caching since it can age out of the shared pool.

Gaps can also be introduced programmatically e.g. by means of a rollback:
user A is getting a number from the sequence let's say number 8 and commits
user A is getting the next number from the sequence, number 9
user B is getting the next number from the sequence, number 10
user A rolls back and user B commits thus a gap was effectively introduced between numbers 8 and 10

So really do you want to use auto generated sequences?
It depends on the system at hand. Does is it require gapless sequences with low
concurrency in return?(since the unique key must be generated and committed before generating a new one thus employing locking) or can it live with gaps and be highly concurrent and scalable ?

Although the sequences eliminate the concurrency issues associated with unique key generation, the key generated has to eventually be inserted into the database and as Mr. Roy Hann points out, depending on the storage structure and locking scheme employed there will be small or large concurrency issues and that Ingres prepares a solution to this problem with the introduction of unordered sequences which in essence will "randomize insertion into B-trees and ISAM tables, eliminating concurrency problems."


Artemus said…
Gapless sequences of numbers are rarely required in real business processes, and in those few where they are, one also usually needs very sophisticated protocols to ensure the numbers are kept in sync with other entities such a pre-printed cheques and certificates. There are other less obvious problems with gapless sequence numbers; for instance no new transaction can use the "next number" until the previous number has be used and committed. This has the effect of making the application strictly single-user--it doesn't scale at all. There are innumerable other problems too.

The general rule of thumb is that designs that depend on gapless sequences should be avoided at almost any cost. In fact, there are good reasons to prefer unique values generated in pseudo-random order. There are efficient algorithms for doing that and one was implemented in Ingres during the 2008 code sprint in London.

Popular posts from this blog

Book Review : How To Create Pragmatic, Lightweight Languages

At last, a guide that makes creating a language with its associated baggage of lexers, parsers and compilers, accessible to mere mortals, rather to a group of a few hardcore eclectics as it stood until now.

The first thing that catches the eye, is the subtitle:

The unix philosophy applied to language design, for GPLs and DSLs"
What is meant by "unix philosophy" ?. It's taking simple, high quality components and combining them together in smart ways to obtain a complex result; the exact approach the book adopts.
I'm getting ahead here, but a first sample of this philosophy becomes apparent at the beginnings of Chapter 5 where the Parser treats and calls the Lexer like  unix's pipes as in lexer|parser. Until the end of the book, this pipeline is going to become larger, like a chain, due to the amount of components that end up interacting together.

The book opens by putting things into perspective in Chapter 1: Motivation: why do you want to build lan…

Deep Angel-The AI of Future Media Manipulation

Undeniably, we live in the era of media manipulation. Such powerful and accessible tools exist today that nearly everyone can do it. Now add to this collection Deep Angel, an artificial intelligence that can erase objects from photographs and videos.

I was notified of Deep Angel around the time I was watching Kill Switch, a futuristic and dystopic movie about our Earth getting cloned in order to suck the resources of the cloned planet, something that would sustain our world's energy needs for at least another millennium. To cut a long story short... full article on

SAP's Creating Trustworthy and Ethical Artificial Intelligence

With the ink hardly dry on the pages of the EU Ethical AI Guidelines manifest, a free online course exploring the issues they raise is already in prospect on the openSAP platform. Run by members of the very same group, the European Union’s High-Level Expert Group on Artificial Intelligence, who wrote the guidelines and in cooperation with SAP's online education platform, a course with the titleCreating Trustworthy and Ethical Artificial Intelligence has been made accessible to anyone with an interest on AI or ML:
full article on