February 26, 2015 at 4:23 pm
Has anyone ever updated a unique index field with Change Data Capture on ? What we're seeing is somewhat unusual. Basically, CDC is recording a insert, then a delete, for some of the updates. I know that the behavior is described as an delete/insert when you update a unique index, but CDC is recording the operation in a different order. For example, we updated a unique index with two rows, ID=5 and ID=6, and we see in the log:
00000021:00001223:0001LOP_BEGIN_XACTLCX_NULL
00000021:00001223:0002LOP_BEGIN_UPDATELCX_NULL
00000021:00001223:0003LOP_DELETE_ROWSLCX_MARK_AS_GHOST Delete ID 5
00000021:00001223:0004LOP_SET_BITSLCX_PFS
00000021:00001223:0005LOP_INSERT_ROWSLCX_CLUSTERED Insert ID 5
00000021:00001223:0006LOP_SET_BITSLCX_PFS
00000021:00001223:0007LOP_INSERT_ROWSLCX_CLUSTERED Insert ID 6
00000021:00001223:0008LOP_DELETE_ROWSLCX_MARK_AS_GHOST Delete ID 6
00000021:00001223:0009LOP_SET_BITSLCX_PFS
00000021:00001223:000aLOP_MODIFY_ROWLCX_INDEX_LEAF
00000021:00001223:000bLOP_MODIFY_ROWLCX_INDEX_LEAF
00000021:00001223:000cLOP_END_UPDATELCX_NULL
00000021:00001223:000dLOP_COMMIT_XACTLCX_NULL
(bold comments added)
It matched what we see in our capture instance:
SELECT [__$start_lsn]
,[__$seqval]
,[__$operation]
,[TestVictim_ID]
,[ParentVictim_ID]
FROM [REPO_CDC_ISSUE].[cdc].[dbo_TEST_VICTIM_CT]
where __$start_lsn=0x0000002100001223000D
__$start_lsn __$seqval __$operationTestVictim_ID
0x0000002100001223000D0x00000021000012230003 1 5
0x0000002100001223000D0x00000021000012230005 2 5
0x0000002100001223000D0x00000021000012230007 2 6
0x0000002100001223000D0x00000021000012230008 1 6
The operations are swapped, with the operation=1=delete second, in the CT table for the second ID.
Any thoughts on this one? It seems like a bug in how SQL Server records the operations in the capture instance. The delete operation shouldn't be last. This is SQL Server 2012, SP1.
Thanks!
February 27, 2015 at 7:48 am
So I wanted to post what we found in case this helps anyone in the future. In this particular case, the clustered unique index includes a date field. As we learned in a great article by Fabaino Amorim (https://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---split,-sort,-collapse/), SQL Server will sort based on this index while performing the update.
What we have is:
old values:
5, 201301 (row 1)
6, 201302 (row 2)
Update to:
5, 201301 (+ other changes) (row 1new)
6, 201301 (row 2new)
Sorting, what happens is
delete row 1 (5,201301)
insert row 1new (5,201301)
insert row 2new (6,201301)
delete row 2 (6,201302)
CDC is recording exactly what happened. It is still odd that CDC is recording the internals of an update that can be open to misinterpretation via the get_net_changes MSFT provided function, but it isn't wrong, strictly speaking.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply