15 May 2013

When is a Sequence Number Not Sequential?

A sequence number or an identity column guarantees the following:

  1. Number served is unique.
  2. Number served is sequential (ascending order) - Identity columns guarantees this; sequence number in Oracle RAC mode doesn't, unless ORDER is used.
  1. It does not guarantee the sequence is gap-free
  2. Sequence number/ identity columns do not partake in transactions
Number loss/ gaps happen due to the following:
  1. Served number does not get utilised (typically due to transaction rollback)
  2. Server restart/ failures - database servers tend to cache sequences in memory for performance reasons. E.g. Oracle Sequence number set-up to be CACHEd in memory.

According to Oracle 11gR2 Database Documentation: for the Sequence database concept, use of Oracle Sequence does not guarantee gap-free set of numbers.
If your application requires a gap-free set of numbers, then you cannot use Oracle sequences. You must serialize activities in the database using your own developed code.

According to the Microsoft TechNet library for SQL 2012: for the Identity SQL database property, in the events of server restart/ failures or transaction failures resulting in rollback, consecutive values cannot be guaranteed and therefore, gaps may be present.

  • Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.
  • Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.
if you cannot use identity values because of these restrictions, create a separate table holding a current value and manage access to the table and number assignment with your application.

Serialization appears to be the only solution. However, serialization would be non-scalable and would incur a huge performance impact.


Priya Anand said...

Nice post. Oracle is a relational database management system produced by oracle corporation. Nowadays most of the multinational companies used this oracle database for storing and managing their data's and programs. So learning Oracle Training Chennai is one of the best idea to make a bright career.

kiran kumar said...

Nice blog... I got very useful information. Java training Training In Chennai

Luckperson said...

You have shared informative details with me. Thanks for your effort.
DBA course syllabus | Oracle dba course