Hi Team,
We are trying to process approx. 7Million records in a table.
We are getting Lock request time out period exceeded.
While the initial snapshot getting the below error:
Before the error here are the configs what I have used:
CONFIG:
{
"name": "TABLE_NAME_jde",
"config": {
"connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
"tasks.max": "1",
"topic.prefix": "TABLE_NAME",
"topic.creation.enable": "true",
"topic.creation.default.replication.factor": "-1",
"topic.creation.default.partitions": "1",
"database.hostname": "",
"database.port": "",
"database.user": "",
"database.password": "",
"database.names": "DB_NAME",
"table.include.list": "SCHEMA_NAME.TABLE_NAME",
"database.dbname": "DB_NAME",
"key.converter.enhanced.avro.schema.support": "true",
"key.converter.schemas.enable": "true",
"value.converter.enhanced.avro.schema.support": "true",
"value.converter.schemas.enable": "true",
"config.action.reload": "restart",
"config.action.reload.interval.ms":"6000",
"snapshot.mode":"initial",
"snapshot.fetch.size": "50000",
"snapshot.max.threads":"2",
"group.id": "jde_cdc_decimal",
"decimal.handling.mode": "string",
"producer.override.batch.size": "50000",
"producer.override.linger.ms": "2",
"producer.override.compression.type": "lz4",
"transforms": "flatten",
"transforms.flatten.type": "org.apache.kafka.connect.transforms.Flatten$Value",
"transforms.flatten.delimiter": "_",
"database.lock.timeout.ms": "60000",
"errors.retry.delay.max.ms": "60000"
}
}
ERROR:
[2025-02-14 15:42:28,474] ERROR [TABLE_NAME 5|task-0] Error during snapshot (io.debezium.relational.RelationalSnapshotChangeEventSource:179)
com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:920)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:814)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:736)
at io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource.lockTablesForSchemaSnapshot(SqlServerSnapshotChangeEventSource.java:152)
at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:142)
at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:92)
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:250)
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:234)
at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:82)
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:137)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:830)
[2025-02-14 15:42:28,506] INFO [TABLE_NAME 5|task-0] Snapshot - Final stage (io.debezium.pipeline.source.AbstractSnapshotChangeEventSource:104)
[2025-02-14 15:42:28,506] WARN [TABLE_NAME 5|task-0] Snapshot was not completed successfully, it will be re-executed upon connector restart (io.debezium.pipeline.source.AbstractSnapshotChangeEventSource:115)
[2025-02-14 15:42:28,549] INFO [TABLE_NAME 5|task-0] Removing locking timeout (io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource:280)
[2025-02-14 15:42:28,613] ERROR [TABLE_NAME 5|task-0] Producer failure (io.debezium.pipeline.ErrorHandler:52)
io.debezium.DebeziumException: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:101)
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:250)
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:234)
at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:82)
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:137)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:830)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:920)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:814)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:736)
at io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource.lockTablesForSchemaSnapshot(SqlServerSnapshotChangeEventSource.java:152)
at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:142)
at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:92)
... 9 more
[2025-02-14 15:42:28,613] WARN [TABLE_NAME 5|task-0] Retry 4 of unlimited retries will be attempted (io.debezium.pipeline.ErrorHandler:125)
[2025-02-14 15:42:28,614] INFO [TABLE_NAME 5|task-0] Connected metrics set to 'false' (io.debezium.pipeline.ChangeEventSourceCoordinator:424)
[2025-02-14 15:42:28,870] WARN [TABLE_NAME 5|task-0] Going to restart connector after 10 sec. after a retriable exception (io.debezium.connector.common.BaseSourceTask:296)
[2025-02-14 15:42:28,870] INFO [TABLE_NAME 5|task-0] Creating thread debezium-sqlserverconnector-F4105_ADT-SignalProcessor (io.debezium.util.Threads:288)
[2025-02-14 15:42:28,870] INFO [TABLE_NAME 5|task-0] SignalProcessor stopped (io.debezium.pipeline.signal.SignalProcessor:127)
[2025-02-14 15:42:28,871] INFO [TABLE_NAME 5|task-0] Debezium ServiceRegistry stopped. (io.debezium.service.DefaultServiceRegistry:105)
[2025-02-14 15:42:28,871] INFO [TABLE_NAME 5|task-0] Connection gracefully closed (io.debezium.jdbc.JdbcConnection:949)
[2025-02-14 15:42:28,872] INFO [TABLE_NAME 5|task-0] [Producer clientId=F4105_ADT-schemahistory] Closing the Kafka producer with timeoutMillis = 30000 ms. (org.apache.kafka.clients.producer.KafkaProducer:1297)
[2025-02-14 15:42:28,872] INFO [TABLE_NAME 5|task-0] Metrics scheduler closed (org.apache.kafka.common.metrics.Metrics:693)
[2025-02-14 15:42:28,872] INFO [TABLE_NAME 5|task-0] Closing reporter org.apache.kafka.common.metrics.JmxReporter (org.apache.kafka.common.metrics.Metrics:697)
[2025-02-14 15:42:28,872] INFO [TABLE_NAME 5|task-0] Metrics reporters closed (org.apache.kafka.common.metrics.Metrics:703)
[2025-02-14 15:42:28,873] INFO [TABLE_NAME 5|task-0] App info kafka.producer for F4105_ADT-schemahistory unregistered (org.apache.kafka.common.utils.AppInfoParser:83)
[2025-02-14 15:42:28,873] WARN [TABLE_NAME 5|task-0] WorkerSourceTask{id=TABLE_NAME 5-0} failed to poll records from SourceTask. Will retry operation. (org.apache.kafka.connect.runtime.AbstractWorkerSourceTask:454)
org.apache.kafka.connect.errors.RetriableException: An exception occurred in the change event producer. This connector will be restarted.
at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:63)
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:144)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:830)
Caused by: io.debezium.DebeziumException: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:101)
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:250)
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:234)
at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:82)
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:137)
... 5 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:920)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:814)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:736)
at io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource.lockTablesForSchemaSnapshot(SqlServerSnapshotChangeEventSource.java:152)
at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:142)
at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:92)
... 9 more
[2025-02-14 15:42:28,873] INFO [TABLE_NAME 5|task-0] Awaiting end of restart backoff period after a retriable error (io.debezium.connector.common.BaseSourceTask:275)
[2025-02-14 15:42:29,399] WARN [TABLE_NAME 5|task-0|offsets] Couldn't commit processed log positions with the source database due to a concurrent connector shutdown or restart (io.debezium.connector.common.BaseSourceTask:366)