Skip to main content
donaldo71
donaldo71
Silver ⭐️⭐️
Oct 17, 2025 7:45 PM UTC
Question

How to avoid deadlocks in Nifi when put something in database? Error Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Hi! I have flows on NIFi and in many places, I have to save data to the database. I mainly use PutSql or PutdatabaseRacord processors. So far, everything was working fine, but today we wanted to test our data and issued 15 messages at once. Unfortunately, in several places, during data saving or updating, an error appeared - error.code 1205 error.message Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. error.sql.state 40001 - after which some data was saved or updated correctly, others unfortunately were not. How can I resolve this situation, and how can I protect myself against something like this?

 

1 reply

vafs
Gold ⭐️⭐️⭐️
vafs
Gold ⭐️⭐️⭐️
Oct 18, 2025 8:37 PM UTC

Hello @donaldo71, 

This looks like SQL is getting the deadlock because of the many records in once. 

You can try couple of this. 
First, enable the retry option on the PutSql or PutdatabaseRacord processors.

vafs_0-1760819691830.png

If the retries helped previously, this could also help in your case.

Also, decrease the concurrency and batch sizes to try to decrease the SQL load. 

Additionally, on the SQL side, if you can, use row versioning isolation to reduce locking: 

ALTER DATABASE DBNAME SET READ_COMMITTED_SNAPSHOT ON;
Translate
    Terms & ConditionsAccessibility statement