Ask Your Question
1

MS SQL CDC origin play dead

asked 2019-07-20 20:58:50 -0500

lichanghong gravatar image

updated 2019-07-31 09:56:39 -0500

metadaddy gravatar image

Hello , I use ms sql cdc as the origin ,when I connect to the slave database,the pipeline always play dead ,that is the pipeline run smoothly a little while ,then everything is ok, but no data pass from the pipeline,at first I think It's because there is no more new data changed in the database,after checking the database, I found otherwise.

when I use pipeline to connect to the master database , it often occurs dead lock in the database .The cleanup work was blocked by the pipeline's query work ,and the capture work was blocked by the cleanup work。 The result is that CDC table has no new changed data .

how should I figured out this problem? I use the version of 3.8.1

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-07-23 21:47:04 -0500

lichanghong gravatar image

Aftere a few night's check , I figured out what's happening in the master sqlserver database .sqlserver cdc has two work process. one is capture data process,the other is cleanup stale data process. the cleanup stale data process runs at 2:00 am every day,the capture data process alwayrs run 。when I run the sqlserver cdc pipeline, that is the query process. there is no conflict between the query process and the capture data process . But when the cleanup stale data process begin to work , It will need executive lock in some resource,while this resource may be locked(shared lock) by our query process。then the cleanup stale data process will be blocked by our query process(sqlserver cdc pipeline)。By far ,everything is ok,the cleanup stale data process just need to acquire the executive lock in some resource after the query process finish query。

Then the problem occurs , the query process always occupy the shared lock. then cleanup stale data process always blocked。 Finally I found there is a config in the sqlserver cdc origin advanced tag. the auto commit tag, this tag's default value is disable , I don't know why the default value is disable, to figure out above problem , I checked everything I can think . At last To figure out this I just need to change a default config . So I advise that this config should be enable by default In case other people run into the same problem and spend a large amount time to tackle it.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-20 20:58:50 -0500

Seen: 121 times

Last updated: Jul 23