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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy