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

[#1]

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)

[#2]

@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.

[#3]

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 

[#4 · Accepted solution]

@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.

[#5]

@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.