Deadlock detected messages in BDR or PGD

This article explains how to investigate further about some "deadlock detected" messages in the worker_errors or event_summary views that started to happen in a BDR / PGD cluster when Parallel Apply is enabled.

In BDR 3.7.x and PGD 4.x there is a known situation that can happen when Parallel Apply is enabled in the nodes, that is when bdr.writers_per_subscription set to a value higher than 1.

While transactions are applied in parallel, their commit order needs to remain the same as it was on the origin server. This can lead to deadlock scenarios in which an older transaction (applied earlier on the origin server) starts waiting for a younger transaction to complete. While it waits, the older transaction, which has been applied but not yet committed, winds up in a scenario where it is waiting for a lock on a row being held by the previously mentioned younger transaction. The result is a deadlock. In this scenario, at least one of the transactions is restarted. This net result is that the system appears to hang (in the case of seeing a deadlock over and over again) or in some cases move very slowly.

The following observations can be used to detect this situation in a node where Parallel Apply is enabled:

• Replication will be broken 'TO' that node.
• Other nodes will start showing an increasing lag 'TO' that node.
• The worker errors in that node will show the messages deadlock detected.
• One of the pglogical Writer processes will be waiting on Extension and the rest of the Writer processes will wait directly or indirectly behind that process.

Despite this situation, RAFT consensus and replication might be working correctly from that node to others (if others nodes are not having Parallel Apply enabled)

Another way to detect this situation is to use the subscription statistics information. For example, with the following command:

SELECT ncommit, nabort, nerror, ninsert, nupdate, ndelete, ntruncate, nddl, ndeadlocks, nretries FROM bdr.stat_subscription;

The previous query will show the total ndeadlocks counted since the last subscription statistics reset.

In case you need to get information related to a workload execution over a period of time, we suggest to reset the stats previous to that workload execution with the command:

SELECT bdr.reset_subscription_stats();

And then to get the bdr.stat_subscription in the suspected node once the workload has finished and lag has reached to 0 again.

In BDR 3.7.x and PGD 4.x the only workaround offered for the moment is to turn off parallel apply for the duration of the transaction operation.

The fix for this situation was introduced in PGD version 5.2. Hence, the recommendation is to upgrade to that or newer versions as soon as possible.