Technical considerations
The first feature that makes this design work is the use of the global variable @@identity , which always holds the identity value assigned most recently in the current session. Because this variable is session-specific, different user sessions can be inserting into the invoices_keytable concurrently, without influencing each other’s @@identity contents.
The table invoices_keytable is used here in an unconventional manner: its only purpose it to quickly obtain a new invoice number in @@identity by inserting an "empty" row. The inserted row itself is not of any interest: the table could be truncated regularly to stop it from growing too large, for example by putting the table on a separate segment and using a threshold procedure.
In this scenario, inserts are performed into two database tables instead of in one table as in the "classic" situation. The extra insert into invoices_keytable is the price to be paid for the increased recoverability of the application. Fortunately, this overhead is very small: first, there is no need for an index on invoices_keytable , because no data will ever be retrieved from this table. Second, the table can be partitioned so that concurrent users will be inserting on different data pages, thus avoiding lock contention. In practice, the extra overhead turns out to be hardly noticeable.
Another point worth mentioning is that the two insert operations do not need to be encapsulated in a transaction. Suppose that the two inserts are indeed part of one transaction, and for some reason it is decided to roll back the insert into invoices . While this will cause no data row to be inserted into invoices_keytable , this will not have any effect on the next identity value to be assigned: once an identity value is issued, it cannot be "given back" or re-used anymore, due to the underlying memory-based algorithm. Therefore, transactional consistency between these two tables is not relevant.
This design technique works in all Sybase versions from 10.0 onwards. Note that table partitioning is only available in version 11.0 and later.
Proactive reparation of identity gaps
Designing a database to allow quick reparation of identity gaps is a major improvement compared to the "classical" situation. Still, it might be preferable to ensure identity gaps will never strike an operational application at any time.
This can be achieved by always running a program directly after server startup, which performs the following actions:
It inserts an "empty" row in invoices_keytable to obtain the next identity value through @@identity .
It retrieves the highest existing key value from the invoices table; hopefully, there would be an index to support this query.
It then compares these two values. If everything is normal, then the difference between these two values is not more than a few units (small gaps can always exist because individual insert operations can have been rolled back). If the difference is bigger than, say 100 units, this means an identity gap exists. A reparation procedure could then be run automatically, which drops and recreates the invoices_keytable.
Note that this check will cause one invoice number value to be missing from invoices , in case no identity gap exists. If this is not desirable, a variation on this procedure is to always rebuild the invoices_keytable, using the highest invoice number from invoices.
Assuming these actions are performed directly after server startup, and before any applications are using the database, an identity gap (if present) will not yet have affected the values in the invoices table because no new invoices have been inserted yet.
The situation will now be corrected immediately before wrong invoice numbers are generated. This ensures that identity gaps do not get a chance to go unnoticed until the first serious application problems start to appear. The downloadable example stored procedure mentioned earlier also works for this situation.
Conclusion
It is possible to avoid the problems caused by identity gaps, the risk of which is implied when identity columns are used. Using the two-table design technique described in this article, identity gaps can be repaired quickly and even automatically, in contrast with the much more inefficient classical approach. This results in a significant improvement in application availability at a negligible performance cost.
存储过程sp_fix_idgap.sql
/*
* This script contains everythi
