View Issue Details

IDProjectCategoryView StatusLast Update
0003901SymmetricDSBugpublic2019-04-24 12:22 Toelong 
Status feedbackResolutionopen 
Product Version3.10.0 
Target VersionFixed in Version 
Summary0003901: MSSQL: Updating multiple rows in source table results in reversed rows in destination table
DescriptionThe update triggers are being joined by row_number() instead of Primary Key ID.

When we update several rows in a table in one UPDATE, under some circumstances SymmetricDS changes the row data for all affected primary keys. e.g.
for the table: ClientID (PK), Practice ID, Name
1, 23, 'Jeff'
2, 25, 'Andrew'
3, 24, 'John'
If we run Update Client SET PracticeID = PracticeID, and the bug gets triggered, we end up with:
1, 24, 'John'
2, 25, 'Andrew'
3, 23, 'Jeff'

This consistently happens when we update a large number of rows (for example "Update Client SET PracticeID = PracticeID" on a 29162 row table triggers this every time), but we've seen it intermittently when updating as few as 2 rows.

We have found that this has been occuring on 3.9.10 as well as 3.10. Databases that were upgraded from Symmetric 3.8.30 to 3.9.10 don't seem to have these problematic triggers.

We've narrowed it down to this part of the generated update trigger:
FROM (SELECT ClientID, row_number() OVER (ORDER BY (select 1)) as __row_num FROM inserted) AS inserted
INNER JOIN (SELECT ClientID, row_number() OVER (ORDER BY (select 1)) AS __row_num FROM deleted) AS deleted ON (inserted.__row_num = deleted.__row_num)

Databases upgraded from 3.8.30 have the following clause, which does not cause problems:
FROM inserted inner join Client orig on orig.ClientID=inserted.ClientID inner join deleted on deleted.ClientID=inserted.ClientID

Tagsdialect: sql-server, trigger



2019-04-15 16:41

developer   ~0001410

The problem is SQL-Server uses statement-level triggers without a way to match old and new rows for multi-row updates. So, we have some workarounds in place to deal with it.

The join that you say is working is based on the configuration of sym_trigger.use_handle_key_updates = 0. It joins the old and new rows on primary key (PK). However, that trigger it is susceptible to losing changes when the PK is updated. For example, if you update the PK from 1 to 2, it will join inserted and deleted tables and get no rows to capture.

When you set use_handle_key_updates = 1, it joins the old and new rows on the row_number(). Every old row gets a new row. Now, they might be matched up incorrectly, but it shouldn't matter on most systems -- all the old rows will be updated and replaced by the new rows, almost like it deleted the old rows and inserted the new rows.

I tried your unit test but the rows came out correctly in the target database.

create table client (
  client_id integer primary key,
  practice_id integer,
  name varchar(50)

insert into client values (1, 23, 'Jeff');
insert into client values (2, 25, 'Andrew');
insert into client values (3, 24, 'John');

update client set practice_id = practice_id;

I can try inserting, updating, deleting with 30,000 rows to see if I can get something to happen. Are there any other settings you can think of that I should know about. Are you using any parameters that change trigger behavior? Are you using transformations?

Issue History

Date Modified Username Field Change
2019-04-04 19:35 New Issue
2019-04-15 16:41 elong Note Added: 0001410
2019-04-15 16:41 elong Assigned To => elong
2019-04-15 16:41 elong Status new => feedback
2019-04-24 12:22 admin Tag Attached: dialect: sql-server
2019-04-24 12:22 admin Tag Attached: trigger