Debezium: Lock request time out period exceeded

 INFO [sql-server-connection|task-0] Snapshot - Final stage (io.debezium.pipeline.source.AbstractSnapshotChangeEventSource:88)
[2022-03-08 07:15:47,766] INFO [sql-server-connection|task-0] Removing locking timeout (io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource:244)
[2022-03-08 07:15:47,770] ERROR [sql-server-connection|task-0] Producer failure (io.debezium.pipeline.ErrorHandler:31)
io.debezium.DebeziumException: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:153)
        at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:69)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:108)
        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: Lock request time out period exceeded.
        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:142)
        at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:106)
        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76)
        ... 8 more
[2022-03-08 07:15:47,776] INFO [sql-server-connection|task-0] Connected metrics set to 'false' (io.debezium.pipeline.metrics.StreamingChangeEventSourceMetrics:70)
[2022-03-08 07:15:48,017] INFO [sql-server-connection|task-0] WorkerSourceTask{id=sql-server-connection-0} Either no records were produced by the task since the last offset commit, or every record has been filtered out by a transformation or dropped due to transformation or conversion errors. (org.apache.kafka.connect.runtime.WorkerSourceTask:484)
[2022-03-08 07:15:48,019] ERROR [sql-server-connection|task-0] WorkerSourceTask{id=sql-server-connection-0} Task threw an uncaught and unrecoverable exception. Task is being killed and will not recover until manually restarted (org.apache.kafka.connect.runtime.WorkerTask:195)
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:115)
        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: io.debezium.DebeziumException: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:153)
        at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:69)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:108)
        ... 5 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
        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:142)
        at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:106)
        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76)
        ... 8 more
[2022-03-08 07:15:48,022] INFO [sql-server-connection|task-0] Stopping down connector (io.debezium.connector.common.BaseSourceTask:238)

Hi @nickbob . Please also mention the problem that you’re facing and what you’re trying to do.

i am getting Lock request time out period exceeded .while running the connect standalone with worker and connector file. How could i resolve this issue?

hi @nickbob, just like yesterday, please provide full details to help people answer your question :slight_smile: Thanks

all the connector and worker properties are same as yesterday.

connector.properties

name=sql-server-connection
connector.class=io.debezium.connector.sqlserver.SqlServerConnector
database.hostname=POCDB
database.port=51523
database.user=poc_dev_user
database.password=db_user_poc
database.dbname=user_poc
database.server.name=poc
table.whitelist==dbo.mbr,dbo.mbr_enc
database.history.kafka.bootstrap.servers=zoo1:9092,zoo2:9092,zoo3:9092
database.history.kafka.topic=dbhistory.sqlsever
decimal.handling.mode=string

worker.properties

offset.storage.file.filename=/tmp/connect.offsets
bootstrap.servers=zoo1:9092,zoo2:9092,zoo3:9092
offset.storage.topic=connect-offsets
config.storage.topic=connect-configs
status.storage.topic=connect-status
auto.offset.reset = latest
offset.flush.interval.ms=10000
rest.port=10082
rest.host.name=zoo2
rest.advertised.port=10082
rest.advertised.host.name=zoo2
internal.key.converter=org.apache.kafka.connect.json.JsonConverter
internal.value.converter=org.apache.kafka.connect.json.JsonConverter
internal.key.converter.schemas.enable=false
internal.value.converter.schemas.enable=false
key.converter.schemas.enable=true
key.converter=org.apache.kafka.connect.json.JsonConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
plugin.path=/home/gokafka/
group.id=sre1

i ran the connect-standalone with worker and connector file and getting the lock time exceed error how could i resolve the issue?

When you run the connector for the first time it will take a snapshot of all of the tables. To do this it takes a lock on the database, and it looks like this is failing:

SQLServerException: Lock request time out period exceeded

By default it uses repeatable.read, but you can change this in the config. I would speak to your DBA about this to establish what other work is happening on the database at the same time that could be preventing the lock being taken, and if a different isolation mode is more appropriate to use.

In which case link to your post from yesterday and don’t rely on people being telepathic :stuck_out_tongue: