INSERT ... ON EXISTING UPDATE causes error SQLE_PRIMARY_KEY_NOT_UNIQUE

[Former Member]

Hello,

Sometimes, when multiple clients are trying to insert records into the same table simultaneosly, some clients get error (SQLCODE=-193)

Primary key for table 'TableX' is not unique: Primary key value ('28')

Clients are executing many statements like:

INSERT INTO TableX ([PrimaryKeyColumn_Integer], [OtherColumn1], [OtherColumn2]) ON EXISTING UPDATE VALUES (28, 'Y', 'N');

Error happens on SQL Anywhere 16 EBF 2207 Windows 64bit. Error never happened on EBF 2127 and some older EBFs, but it might be just lucky case.

My question: why does INSERT with "ON EXISTING UPDATE" not detect that record already exists? Does it depend on isolatation level or something else?

[former_member186998]

Hi Leonid,

SQL Anywhere 16 fixed the problem of the INSERT ON EXISTING UPDATE statement by build 2119.

============(Build #2119 - Engineering Case #782421)============
The server did not return an error if an INSERT ON EXISTING UPDATE statement
violated unique indexes on the insert table and silently deleted violating
rows. This has been fixed.

2246503 - SQLAnywhere 16.0 SP45 Build 2207 Releaqse Notes Information

I think that this phenomen may be related to this correction.
When you have an official account for support, you shoud ask this question using the account.(You must present the repro step of this phenmenon)
You may obtain useful information.

Thanks,

Atsushi

[Former Member]

Hi Atsushi,

thank you, I've submitted incident to SAP support. Unfortunately I have not found a stable way to reproduce the issue, it happens sometimes, but quite rarely.

Regards,

Leonid