Deadlocks and Auto-commit/Transection in Apache NiFi

What Is a Deadlock?

A deadlock is a situation where two or more transactions are each waiting for the other to release a resource, such as a database lock. As a result, none of the transactions can proceed, and the system halts execution for those sessions.

In NiFi:

Deadlocks most commonly arise in PutSQL, ExecuteSQL, or custom database interaction flows when:

Multiple concurrent processors access the same database/table.
Improper transaction boundaries leave locks open.
Auto-commit is disabled without proper commit handling.

Classic Deadlock Pattern (Circular Wait)

A deadlock often stems from circular waiting, illustrated below:

Processor	Holds Lock On	Waiting For
A	Resource X	Resource Y
B	Resource Y	Resource X

This mutual wait results in an indefinite block. In NiFi, this can happen when:

Two PutSQL processors insert/update the same tables but with overlapping or conflicting rows.
One processor disables auto-commit and holds a transaction open.
Another processor attempts to access rows locked by the first, and vice versa.

Transaction Management in PutSQL

The PutSQL processor in Apache NiFi allows configuration of Auto-commit behavior:

Auto-commit enabled (default):
- Each SQL statement (insert/update/delete) is committed immediately after execution.
Auto-commit disabled:
- SQL operations are executed within a transaction that remains open until explicitly committed—if not committed, this leads to open locks, resource contention, and risk of deadlocks.

Auto-commit Behavior Comparison

Mode	Behavior	Lock Risk	Best Used For
Auto-commit Enabled	Every operation is committed immediately. No transaction remains open.	Very Low	Simple insert/update/delete operations per FlowFile.
Auto-commit Disabled	Transactions stay open until explicitly committed.	Moderate–High	Atomic multi-step DB operations requiring rollback support.
Auto-commit Disabled + No Commit	Transactions stay open unintentionally.	Very High	(Dangerous) – causes long-held locks, contention, and deadlocks.

When to Use Auto-commit vs. Manual Transactions

Use Auto-commit (Recommended Default)

Ideal when:

Each FlowFile contains a single SQL statement.
The operation doesn’t depend on other DB actions.
You want fast execution and minimal locking.

Examples:

Insert one row per FlowFile into a target table.
Update a user status flag.
Delete a record by ID.

Auto-commit ensures locks are immediately released after execution.

Disable Auto-commit (Use With Caution)

Only use when:

You are performing multi-step operations within a single transaction.
Operations must either all succeed or all fail (atomicity).
You have explicit commit/rollback logic, such as in scripting.

Examples:

Insert into multiple tables that must stay in sync (e.g., invoice + invoice items).
Complex business rules where logic must rollback if any step fails.
ExecuteScript or ExecuteGroovy that handles transactions manually.

If you disable auto-commit in PutSQL or ExecuteScript, you must manage transaction lifecycle (commit/rollback).

Common Pitfalls

1. Leaving Transactions Open

If a processor disables auto-commit and doesn’t commit the transaction, it leaves:

Locks on rows or tables.
Open connections in the DB.
Resource contention in connection pools.

This is one of the top causes of deadlocks and performance bottlenecks in NiFi data flows.

2. Long-running Processors with Manual Transactions

Slow processors with auto-commit disabled can hold locks longer, increasing chances of other processors waiting, which leads to contention and potential circular waits.

Detecting Deadlocks & Contention

For PostgreSQL:

Use the following query to check blocked and blocking processes:

SELECT
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid;

Best Practices

Practice	Importance
Enable auto-commit in PutSQL	Ensures transactions close quickly and locks are released early.
Avoid disabling auto-commit unless needed	Reduces chances of forgotten open transactions.
Use explicit commit/rollback when needed	Essential for atomic flows with auto-commit disabled.
Monitor open DB sessions	Detect stuck/idle transactions or blocking queries.
Test concurrency scenarios	Simulate load to uncover hidden deadlock risks.
Use connection pool wisely	Exhausted connections increase wait time and contention.

Summary

For most use cases, leave Auto-commit enabled in NiFi's PutSQL processor.
Only disable it if you have complex, multi-step operations and a clear commit/rollback strategy.
Mismanagement of transactions is one of the leading causes of database deadlocks in NiFi pipelines.