For some context, I am using NiFi with the PostgreSQL JDBC driver where two processors update different columns for same row in a table using different transactions. This often caused deadlocks with errors like "ERROR: deadlock detected. DETAIL: Process 70725 waits for ShareLock on..."

These errors are gone once I enabled the autocommit option for the JDBC driver session. However, I am not sure if this is the correct way to solve this problem. Am I just being lucky here or just making things worse? Why do I get no errors when using autocommit?

CodePudding user response：

Your question shows that you have not thought about database transactions in depth. Yes, there will be fewer deadlocks if you never have any transactions that last for more than a single statement (autocommit), although you can also get deadlocks in autocommit mode. But you have transactions for a purpose:

• to make sure that of several data modifications that must be performed together to transfer the database from one consistent state to another, either all or none of them are executed

• to prevent concurrent sessions from seeing the effects of partial data modifications

You should design your transactions by identifying which data modifications must be in a common transaction to ensure data integrity. Then make your transactions no longer than is necessary to guarantee data integrity. Never, ever perform an activity that could take a longer time while you have an open database transaction. These steps will ensure that you have no more deadlocks than necessary.