Sybase adaptor may generate duplicate primary keys for multiple application instances
Apple reference # 2299035
PROBLEM:
The Sybase adaptor uses a stored procedure called "eo_pk_for_table" to generate primary key values. Because this stored procedure does not run in a transaction, if two application instances request a primary key simultaneously, they could be assigned the same primary key.
RESOLUTION:
To prevent duplicate primary keys from being generated, you'll need to replace the definition for the "eo_pk_for_table" stored procedure, created in your database by EOModeler, with one that includes a transaction. The original definition will look like this:
create procedure eo_pk_for_table @tname varchar(32) as
begin
declare @max int
update eo_sequence_table set counter = counter + 1
where table_name = @tname
select counter from eo_sequence_table where table_name = @tname
end
Replace it with the corrected version below:
create procedure eo_pk_for_table @tname varchar(32) as
begin
begin transaction
update eo_sequence_table set counter = counter + 1 where table_name
= @tname
select counter from eo_sequence_table where table_name = @tname
commit
end
The exact commands you'll use to make the change will depend on the database access tool you're using. Assuming you're using isql at the command line, your session should look something like this:
1> drop procedure eo_pk_for_table
2> go
1> create procedure eo_pk_for_table @tname varchar(32) as
begin
begin transaction
update eo_sequence_table set counter = counter + 1 where table_name
= @tname
select counter from eo_sequence_table where table_name = @tname
commit
end
2> 3> 4> 5> 6> 7> 8> 9> go