question Re: Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded in Support Questions

Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded

RenoldSingh — Fri, 24 Nov 2023 05:57:37 GMT

I'm encountering occasional Exception when using PUTSQL processor in apache Nifi.

I have already Increased innodb_lock_wait_timeout But I'm still encountering the error occasionally. How can I fix this error? And what are the optimal configurations when using putsql processor to insert large volume of records into mariadb?

Variable_name;Value
innodb_lock_wait_timeout;300

Error Snippet:
2023-11-24 18:04:45,837 ERROR [Timer-Driven Process Thread-51] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c7255d17-018b-1000-143e-fc839881330c] Failed to update database due to a failed batch update, java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction. There were a total of 1 FlowFiles that failed, 14 that succeeded, and 0 that were not execute and will be routed to retry;
java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)

Re: Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded

VidyaSargur — Fri, 24 Nov 2023 07:10:25 GMT

@RenoldSingh, Welcome to our community! To help you get the best possible answer, I have tagged in our NiFi experts @SAMSAL @cotopaul @ckumar @TimothySpann who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

Re: Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded

ckumar — Fri, 24 Nov 2023 13:56:25 GMT

The reported behavior with Lock wait timeout in Mysql/MariaDB is not in control of NiFi PutSQL, You have to check your DB Table for possible issues.

Thank you

Re: Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded

steven-matison — Tue, 28 Nov 2023 14:02:22 GMT

@ckumar is Correct. For large volume ingestion into the receiving host, you need a maria db host that can handle 1000s of transactions per second. Nifi can easily exceed default configurations for total connections. With a well tuned highly available mariadb, you can crank up execution time and concurrency on nifi and get 10,000s+ transactions per second.

Re: Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded

VidyaSargur — Fri, 08 Dec 2023 10:03:17 GMT

@RenoldSingh, have any of the replies helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.