thm the server uses to generate identity column values: essentially, a counter is kept in server memory, holding the identity value most recently issued. When a new row is inserted, this counter is incremented and the resulting value is assigned to the identity column in that row. While the new data row itself is written to disk, the new value of the in-memory counter is not. Only when the server is shut down in a normal way, this value is saved on disk. This algorithm makes the identity feature very fast, because no I/O is required to generate a new value. On the other hand, should the value of the in-memory counter be lost, as is the case for a server crash or a "shutdown with nowait", it is not possible to continue at the next identity value, because the last issued value was not saved anywhere. In this case, the server will continue generating identity values starting at some much higher value - which is what creates the identity gap. Exactly at which higher value the server picks up, is determined by the configuration parameter "identity burning set factor", which, to a certain extent, can be used to limit the maximum possible size of an identity gap. However, because this a server-wide setting, it is not possible to apply this to individual tables. See the ASE System Administration Guide and Technical Document #20113 at http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=20113 for a detailed description of how to use this configuration parameter. In view of these underlying technicalities, the risk of running into identity gaps could be seen as the price one has to pay for the high performance offered by the identity column feature. Also, it is clear that this risk cannot be completely excluded. DBA’s should be aware of this, and be prepared to perform recovery procedures.
Fixing "identity gaps" the slow, classical way Let’s assume that, once an identity gap has occurred, it should be repaired as soon as possible. Basically, this involves the following two actions:
Updating those rows which have received very high values in their identity columns to the correct values that should have been generated instead. In the above example, 5000002 and 5000003 should be changed to 10032 and 10033, respectively. Resetting the value of the identity column downwards, so that a correct value generated when the next row is inserted (10034 in the example).
Unfortunately, none of these actions can be performed directly. The DBA is not allowed to update the value of an identity column in an existing row, nor can the value of future identity values be modified downwards. Therefore, the only available procedure to fix a situation where identity gaps have occurred has been the following:
Switch on the identity_insert option for the invoices table with the following statement:
set identity_insert invoices on
This will allow an explicit value to be specified for the identity column in an insert statement. Note that this option can be enabled for only one table at a time.
For all rows with problematic 'high' identity values, delete the row and re-insert it with the proper value one would have liked to see there in the first place, effectively performing an update. In the example, this would update invoice numbers 5000002 and 5000003 to 10032 and 10033, respectively.
BCP the invoices table to a file, say invoices.bcp .
Drop and re-create the invoices table. Note that dropping the table will implicitly switch off the identity_insert option.
BCP the invoices.bcp file back into the invoices table using the BCP -E option (for identity insert).
Rebuild any indexes on the table, if applicable.
Applying this procedure to a multi-million row, real-life-size application table could well take a few hours, during which the invoicing application is unavailable. In many cases, end-users and management would probably find this situation unacceptable and ask their DBA unsettling questions as to why this problem could not have been avoided. Even though this will hopefully remain a rare incident, there is always a possibility that such a repair procedure may have to be performed, because the risk of identity gaps just cannot be fully excluded. For this reason, DBA’s or developers sometimes choose not to use identity columns at all for applications with high availability requirements, because this could lead to unacceptable application downtime. The above is not a far-fetched or hypothetical scenario: with a certain regularity, cries for help are posted in the Usenet newsgroup comp.databases.sybase by DBA’s suddenly facing an identity gap and who are desperate for a quick solution. Unfortunately, Sybase has not felt it necessary to implement additional functionality for making the process of fixing identity gaps easier, leaving DBA’s with nothing but the rather clumsy procedure described above.
A better way of repairing identity gaps We will now look at a database design approach that allows DBA's to fix identity gaps quickly, in a matter of seconds or, in the very worst case, minutes.
The first step to achieve this is to use two database tables instead of one: the application table invoices plus a separate keytable named invoices_keytable. create table invoices_keytable (dummy_key numeric(10,0) identity)
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
