Posts
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."
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."
Comments
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.