e 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 everything you need to run a demo of how
* to use a two-table database design technique that allows
* identity gaps to be fixed quickly using a stored procedure.
* This script set up the following objects:
*
* - a table 'invoices', holding application data
* - a table 'invoices_keytable', for generating invoice numbers
* - a procedure 'sp_fix_idgap_invoices' to repair identity gaps
* in the 'invoices' table
* - a procedure 'sp_insert_invoice' to simulate the application
* inserting invoices
*
/
if not exists(select * from master.dbo.sysdatabases
where name = "my_db")
begin
print "***************************************************"
print "*** You should edit this script first ! ***"
print "*** Change 'my_db' to the name of your database ***"
print "*** in which you want to run this script. ***"
print "***************************************************"
end
go
use my_db
go
/*
* create the application table holding invoice data
*/
if object_id("invoices") != NULL
begin
drop table invoices
end
go
create table invoices
(invoice_nr numeric(10,0),
customer_nr int,
amount money)
go
create unique index ix1 on invoices(invoice_nr)
go
grant all on invoices to public
go
/*
* Create a separate procedure for creating the keytable.
* This is required because it's not allowed to drop
* and recreate an object with the same name in one procedure
*/
if object_id("invoices_keytable") != NULL
begin
drop table invoices_keytable
end
go
if object_id("sp_create_invoices_keytable") != NULL
begin
drop procedure sp_create_invoices_keytable
end
go
create procedure sp_create_invoices_keytable
/* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */
as
begin
create table invoices_keytable
(dummy_key numeric(10,0) identity)
/*
* Partitioning only works in system 11.0 and later
* The number of partitions can be increased if there
* will be many concurrent inserts.
*/
alter table invoices_keytable partition 10
end
go
grant execute on sp_create_invoices_keytable to public
go
/*
* create the keytable
*/
exec sp_create_invoices_keytable
go
/*
* create a procedure to simulate application activity:
* first generate a new invoice number, then insert a new invoice
*/
if object_id("sp_insert_invoice") != NULL
begin
drop procedure sp_insert_invoice
end
go
create procedure sp_insert_invoice
/* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */
@p_customer_nr int,
@p_amount money
as
begin
insert invoices_keytable values&nbs上一页 [1] [2] [3] [4] [5] [6] 下一页 |
