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:

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

---

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

---

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.

---

References

• StackOverflow: Does Auto-commit Really Solve Deadlocks in PostgreSQL?

• Apache NiFi: PutSQL Documentation

• PostgreSQL Deadlocks & Lock Management