Sybase treats unique key violation diffeerently for individual vs group updates

[simon]

Let's put a primary key on the table_name for columns (key_field,
key_field2)

THIS WILL WORK:

begin tran
update table_name set key_field = key_field + 1 where key_field2 =
some_value
rollback tran
go
-------------------------------------

THIS WILL ABORT with an error "attempt to insert duplicate key"

begin tran
update table_name set key_field = 2 where key_field = 1 and key_field2 =
some_value
update table_name set key_field = 3 where key_field = 2 and key_field2 =
some_value
rollback tran
go
-------------------------------------

The second writeup is what the first statement is usually unfolding to
during replication.
Makes you wonder why the first statement would then work.
It appears what works in production for a stand-alone ASE would need code
rewrites to accomodate replication.





--- news://freenews.netfront.net/ - complaints: ***@netfront.net ---

[Leonid Gvirtz]

Hi Simon

Since the condition specified in constraint is checked after each DML
statement, the behavior that you see is pretty much expected.
Unfortunately, it is not possible to defer the check of constraint
condition until the end of transactions in ASE. In general, changing
the values of primary key columns in replicated environment is not a
good practice and I can't think about any direct solution to the
problem like this. Possible workarounds:

1. Wrap the update of the primary key into a stored procedure and
replicate it instead.

2. In very recent versions of ASE (15.0.3) and RS (15.2), statement
replication is available. I have not tried it yet, though.

3. Just don't replicate updates of primary keys. Instead perform such
updates on all relevant dataservers outside the replication. Not sure
if it can be applicable in your specific case.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

Post by simon
Let's put a primary key on the table_name for columns (key_field,
key_field2)
begin tran
update table_name set key_field = key_field + 1 where key_field2 =
some_value
rollback tran
go
-------------------------------------
THIS WILL ABORT with an error "attempt to insert duplicate key"
begin tran
update table_name set key_field = 2 where key_field = 1 and key_field2 =
some_value
update table_name set key_field = 3 where key_field = 2 and  key_field2 =
some_value
rollback tran
go
-------------------------------------
The second writeup is what the first statement is usually unfolding to
during replication.
Makes you wonder why the first statement would then work.
It appears what works in production for a stand-alone ASE would need code
rewrites to accomodate replication.