questions on error 2601 duplicate key

[jmtorres]

Hello,

I have some questions regarding the behavior of ASE RS when an attempt to insert a duplicate key at the primary site is done.

ASE 16.x -> RS15.7.1-> ASE 16.x

Assuming  :

ASE1->primary ASE

RS-Rep server

ASE2 -> replicated ASE

DB1 ->primary db

DB2 ->replicated db

Using Warm Standby between the 2 db.

Table t1 is identical in  ASE1 and ASE2 having a unique index , say on column c1(int)

At ASE1  :

insert into t1 values(1).

Then , trying the same insert again ( duplicated value/key )

ASE1 will reject the insert, but what is the correct behaviour at RS errorlog

-getting 2601 error

-then,  DSI from ASE2.DB2 shuts down and therefore it maps to STOP_REPLICATION

or

DSI  from ASE2.DB2  stays up ( no shutdown )  and only shutdowns if I try to insert a duplicate row/key at ASE2.DB2 ?

In which scenario DSI threads shutdown due to insert duplicate key.: at primary ASE, at Replicated or  both ?

Thank you

Regards

Jose

[c_baker]

As Mark says, try it out.  The duplicate key insert attempt at the primary should roll back the transaction and it should not even be replicated to the secondary.

Chris

[jmtorres · Accepted solution]

Thank you guys,

Sorry for all the confusion , Let me try to be more clear on this matter:

I'm just trying to understand how DSI works in a WS pair when an attempt of duplicate key insert on a table with unique index  or similar should happen at a primary ASE1.DB1 site .

- I'm clear that  the replicate site must not be modified( U;D,I) [ok]

- Load of secondary site must be done only by replication methods [ok]

-When an attempt to insert a duplicate key avle at the primary site  the Tx is rolled back and it's not read by the rep agent , thus it does not flow to the replicate site.

Then  as Mark mentioned and  assuming everything is working and set up correctly, this is :

Create new PDB/RDB databases; create identical/empty table 't1' in both databases; create identical unique index on both tables

- create brand new WS setup (create logical connection; rs_init ASE1.DB1 as active db; rs_init ASE2.DB2 as standby)

- verify replication is working by submitting rs_ticket in PDB and verify it comes out in the RDB; once replication if verified then ...

- insert first record into t1 in the PDB and verify it gets inserted to RDB's t1 table

- insert duplicate record into t1 in the PDB and see what happens

Then :

DSI for ASE1.DB1 stays up(running)

DSI ASE2.DB2 stays  up.(running)

Then the question is : Assuming that at the replicate site ASE2.DB2, no manual  modifications are done, under which possible circumstances DSIs to ASE2.DB2 would shutdown ?

Thank you

Regards

Jose

[former_member89972 · Accepted solution]

You mean other than the duplicate insert ?

If so there are a few cases (top of my head 🙂 )

1. Adding user to primary database without adding login on replicate server first

2. Transaction Log on replicate is full (if it is a shared database)

3. Locks run out ...

4. Replicate database is full

5. Any other error where a normal valid DML would fail run time

HTH

Avinash

[former_member182259 · Accepted solution]

As others have answered, there are many cases when the replicate DSI goes down.   Generally, it is due to valid issues at the replicate such as log full, wrong permissions on maint user, etc.   However, there are some documented instances in which replicated DML doesn't apply correctly.   For example, since this started on duplicate keys, let's say we have a table such as:

create table task_priority (

task_name,

priority_sequence,

due_date,

etc,

) primary key (priority_sequence)

....and we have rows such as

"mow grass", 1, ....

"take out garbage", 2, ...

"wash the car", 3, ....

....but when you get home, you find out that one of your kids has a science project due the next day, so you execute something like:

begin tran

update task_priority set priority_sequence=priority_sequence+1

insert task_priority values ("help with science project", 1, ...)

commit tran

Result is you now have a new #1 task....and everything has slid down one.    The problem is that using row-wise replication, this doesn't replicate well and you end up with duplicate keys at the replicate, because what SRS is sent (and sends to DSI) is something like:

begin tran

update task_priority set priority_sequence=2 where priority_sequence=1

update task_priority set priority_sequence=3 where priority_sequence=2

update task_priority set priority_sequence=4 where priority_sequence=3

insert task_priority values ("help with science project", 1)

commit tran

....the problem is at the replicate side, we would first attempt to update "mow grass" from priority 1 to 2.....but "take out the garbage" is at 2, so we get a dupe key.   In the old days forcing a descending index at the primary was a work-around - but today, you can use SQLDML to replicate the update statement when more than 1 row is impacted.

There are other situations - e.g. when you don't have a primary key and a float/real column exists as part of the where clause.   This can cause floating point interpretation differences at primary vs. replicate where 1.00000000000001 may be stored at primary but replicate has 0.999999999999999 or similar.   As a result, when you update something in the row, the where clause will have 'where float_col=1.00000000000001' which will return 0 rows affected and with dsi_rowcount_validation enabled, the DSI will suspend as well.

As a consequence, if the DSI suspends, generally current/newer versions of SRS print out in the error log the transaction that was attempted, the error message and what SRS did (e.g. suspend DSI).   It will also tell you which command failed - e.g. command 5 - and you simply need to count into the transaction to the 5th command  and look at the SQL plus look at the same row in the DBMS and try to find out what is causing the problem.   Generally dropping indexes is not the solution.  You may have to skip a transaction and execute it manually and implement the fix so that future transactions don't fail.....or if the fix is usable immediately, simply implement the fix and resume the connection.

[jmtorres]

Jeff,

Thank you very much again!

Regards

Jose

[jmtorres]

Mark , Avinash, Chris,

Thank you very much for the help , understanding , time and patience on answering this questions. You have been very kind and clear on every topic.

Thanks again

Greatly appreciated

Regards

Jose

[jmtorres]

Thank you Mark for the help, We'll keep testing, but to summarize:

Under normal operation and when an attempt to insert a duplicate key at the primary say insert to ASE1.DB1..t1 , RS  behaviour will be

DSI ASE1.DB1 up

DSI ASE2.DB2 up

Only if , attempting to insert a duplicate key value ate ASE2.DB2..t1  then ASE2.DB2 DSI will shutdown and if for any of the 4 reasons I comnedted..?

Regards

Jose

[c_baker]

I'm slightly confused.  Are are you also inserting the value into DB2 through a separate process and not through SRS only?

[former_member89972]

Only committed transaction gets replicated.  Second insert on primary table with error 2601 is rolled back i.e. NOT  committed. So it will not go to replicate table.

Ensure that you have not skipped any transactions and most importantly the replicate tables are populated by replication mechanism ONLY and other users have only "select" privilege on these tables.

HTH

Avinash

[jmtorres]

Hi Mark/Chris,

It's clear that that a transaction  attempting to insert a duplicate key value will be rolled back and nothing is going to flow from ASE1.DB1 to ASE.DB2.

So both DSI's , ASE1.DB1 and ASE2.DB2 shoud stay up when an attempt to insert a duplicate key value happens in the primary site (ASE1.DB1).?

Actually what we are seeing  is that for DSI  ASE2.DB2  is being shutdown( stop_replication ).

If we are sure that no  DML is taking place at ASE2.DB2 , which could be the reasons for this to happen:

(1) check for  having same type of indexes in primary and replicate

(2) check  replicating identity columns

(3) check for  having triggers enabled at replicate side ( but i understand this is disabled by default in a WS pair )

(4) check for  data  out of sync at replicate

I've always assumed that when an attempt to insert a duplicate value at ASE1.DB1 transaction will be rolled back , but   DSI for ASE2.DB2 should be  shutdown ..(this is not true then ?)

Thank you very much

Regards

Jose