Change Data Capture inconsistency

  • 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!

  • 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