search

apache / flink-cdc

Flink CDC is a streaming data integration tool
https://nightlies.apache.org/flink/flink-cdc-docs-stable
Apache License 2.0
5.76k stars 1.96k forks source link

请问SQL server 2016 表锁超时是什么原因 #1669

Closed GanfengTan closed 9 months ago

GanfengTan commented 2 years ago

Environment :

To Reproduce Steps to reproduce the behavior:

  1. The test data :
  2. The test code : sql server connector
  3. The error : io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource  - Locking table SQLTEST.dbo.ods_erp_fnd_user 2022-10-28 10:15:13,008 WARN  60103 [80] com.ververica.cdc.debezium.DebeziumSourceFunction             - Consumer subtask 0 received confirmation for unknown checkpoint id 1 2022-10-28 10:15:14,184 INFO  60103 [90] io.debezium.pipeline.source.AbstractSnapshotChangeEventSource  - Snapshot - Final stage 2022-10-28 10:15:14,185 INFO  60103 [90] io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource  - Removing locking timeout 2022-10-28 10:15:14,188 ERROR 60103 [90] io.debezium.pipeline.ErrorHandler                             - Producer failure io.debezium.DebeziumException: com.microsoft.sqlserver.jdbc.SQLServerException: 已超过了锁请求超时时段。 at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:82) at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:110) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 已超过了锁请求超时时段。 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:693) at io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource.lockTablesForSchemaSnapshot(SqlServerSnapshotChangeEventSource.java:140) at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:115) at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:71) ... 6 more 2022-10-28 10:15:14,194 INFO  60103 [90] io.debezium.pipeline.metrics.StreamingChangeEventSourceMetrics  - Connected metrics set to 'false' 2022-10-28 10:15:14,613 INFO  60103 [88] io.debezium.connector.common.BaseSourceTask                   - Stopping down connector 2022-10-28 10:15:14,620 INFO  60103 [92] io.debezium.jdbc.JdbcConnection                               - Connection gracefully closed 2022-10-28 10:15:14,622 INFO  60103 [93] io.debezium.jdbc.JdbcConnection                               - Connection gracefully closed 2022-10-28 10:15:14,624 ERROR 60103 [88] com.ververica.cdc.debezium.internal.Handover                  - Reporting error: com.ververica.cdc.connectors.shaded.org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped. at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42) at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:127) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)
GOODBOY008 commented 2 years ago

@GanfengTan Because sqlserver cdc will lock tables when snapshot phase. You can run jobs when db is not bussy.

GanfengTan commented 2 years ago

@GOODBOY008 Thank you very much for your answer. But I created a new DB and a new table still exists this error.

guandatawangjialin commented 1 year ago

@GanfengTan Because sqlserver cdc will lock tables when snapshot phase. You can run jobs when db is not bussy.

你好，我想问一下sqlserver锁表是因为有其他的jdbc连接操作这个表导致的问题吗

GanfengTan commented 1 year ago

你好，我想问一下sqlserver锁表是因为有其他的jdbc连接操作这个表导致的问题吗

不是的，新建的一个数据库无其他任何的JDBC连接。

wsczm commented 1 year ago

@GOODBOY008 Lock the table when the flink task restarts,

Can restarting a task from checkpoint or savepoint avoid table locking?

wsczm commented 1 year ago

@GOODBOY008 Is there any way to avoid table locking issues

wsczm commented 1 year ago

@GOODBOY008 I have encountered three lock table issues in the production environment，It's a big trouble

GOODBOY008 commented 1 year ago

@GOODBOY008 I have encountered three lock table issues in the production environment，It's a big trouble

@wsczm Old version is lock base implement. You can bump cdc version to latest and use incremental config.

wsczm commented 1 year ago

@GOODBOY008 use cdc 2.4 and config 'scan.startup.mode'='latest-offset' ?

GOODBOY008 commented 1 year ago

@GOODBOY008 use cdc 2.4 and config 'scan.startup.mode'='latest-offset' ?

Use scan.incremental.snapshot.enabled

wsczm commented 1 year ago

@GOODBOY008 Hello, can you add your DingTalk friends. Currently, they are using MySQL, sqlserver, and Oarcle's CDC for production. There are some usage issues that I would like to consult with you. It is not very convenient to use them here

GOODBOY008 commented 1 year ago

@GOODBOY008 Hello, can you add your DingTalk friends. Currently, they are using MySQL, sqlserver, and Oarcle's CDC for production. There are some usage issues that I would like to consult with you. It is not very convenient to use them here

You can add DingTalk group.

wsczm commented 1 year ago

@GOODBOY008 i add dingtalk group but no person give me help ,thank you very much for give me a wechat friend or dingtalk friend

wsczm commented 1 year ago

@GOODBOY008 old version whether Can restarting a task from checkpoint or savepoint to avoid table locking?

PatrickRen commented 9 months ago

Closing this issue because it was created before version 2.3.0 (2022-11-10). Please try the latest version of Flink CDC to see if the issue has been resolved. If the issue is still valid, kindly report it on Apache Jira under project Flink with component tag Flink CDC. Thank you!