Note that the invoice_nr column is no longer an identity column, but a ordinary column of datatype numeric. The identity column has moved to table invoices_keytable , which contains just this one column and nothing else. The purpose of this identity column, named dummy_key , still is to generate key values for new invoices, but in a slightly different way than before.
When creating a new invoice, first a new invoice number is generated by inserting an "empty" row into invoices_keytable . The identity value assigned to the dummy_key column in this row is then automatically available through the global, session-specific variable @@identity . This invoice number is then used to insert the actual new invoice data into the invoices table:
/* insert an "empty" row to generate new invoice number */
insert invoices_keytable values ()
/* use identity value as key value for new row */
insert invoices (invoice_nr, customer_nr, amount)
values (@@identity, @new_customer, @new_amount)
This two-step way of inserting a new invoice is functionally identical to the "classical" situation where the invoice_nr column in the invoices table would have the identity property. Also, identity gaps can still occur in this design, with the same consequences for the application as before. However, once this happens, this new approach offers a much better way to repair the identity gap. A DBA should then take the following steps:
Update the invoices table using a normal update statement:
update invoices
set invoice_nr = 10032
where invoice_nr = 5000002
Contrary to the "classical" approach, this update will work because the invoice_nr column is not an identity column, but a normal column (NB: a similar statement is required for correcting invoice 5000003).
Drop and re-create invoices_keytable . No data is lost here, because the data rows in this table do not contain any useful information.
Reset the identity column value in invoices_keytable with the following statements:
set identity_insert invoices_keytable on
insert invoices_keytable (dummy_key) values (10033)
set identity_insert invoices_keytable off
The effect of these statements is that for the next row that will be inserted, the identity value generated will be 10034, which is exactly what the next invoice number should be. This is because the statement set identity_insert ... on allows an explicit identity value to be inserted in dummy_key column of invoices_keytable . If this value is higher than the highest identity value issued, the identity value is adjusted upwards. Because this mechanism doesn’t work in a downward direction, the table must be re-created first to make this trick work.
Of these three steps, the last two will always be very fast; these should take no more than a few seconds. The first step (updating the invoices table) should normally not take much time either; in case there are many invoice numbers that need to be corrected, this should still not take longer than a few minutes in the worst case.
The obvious way of implementing this reparation procedure is to put these actions in a stored procedure. In case an identity gap is found, the DBA just needs to execute this procedure and the problem will be fixed automatically. An example of such a stored procedure can be found below
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.
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
