Deadlock Issue in Custom Payment Reconciliation RQ Job for Bulk Transactions

ERPNext Customize ERPNext
1

Description:

We have implemented a custom RQ job function for payment reconciliation in ERPNext. The job is derived from the trigger_reconciliation_for_queued_docs function in erpnext/accounts/doctype/process_payment_reconciliation/process_payment_reconciliation.py. In our customization, we made minor modifications by commenting out the queue_size code to process all entries at once.

Our goal is to reconcile all customer and supplier payments and invoices in a single run at 2:30 AM daily. While the process handles most of the workload efficiently, we encounter deadlock errors when reconciling high volumes of transactions. This typically occurs for 60–70 transactions, with 4–5 transactions encountering deadlock errors.

Error Details:
Error Title: erpnext.accounts.doctype.process_payment_reconciliation.process_payment_reconciliation.fetch_and_allocate
Traceback:
sql
Copy code
Traceback (most recent call last):
File “apps/frappe/frappe/utils/background_jobs.py”, line 220, in execute_job
retval = method(**kwargs)
…
File “apps/frappe/frappe/database/database.py”, line 240, in sql
raise frappe.QueryDeadlockError(e) from e
frappe.exceptions.QueryDeadlockError: (1213, ‘Deadlock found when trying to get lock; try restarting transaction’)

Relevant Context:

  1. We are using 2 workers in a single bench.
  2. The issue arises when the job processes entries in bulk, causing some transactions to fail due to deadlocks.
  3. The affected function is fetch_and_allocate, which leads to deadlocks while performing database operations (INSERT INTO or UPDATE queries) on tabProcess Payment Reconciliation Log Allocations.

Steps to Reproduce:

  1. Implement a custom RQ job similar to trigger_reconciliation_for_queued_docs.
  2. Process all Process Payment Reconciliation Doctype entries in a single run.
  3. Execute the job during high transaction loads.
  4. Observe the deadlock errors in the logs for a subset of transactions.

Environment Details:

  • ERPNext Version: 15.34.0
  • Frappe Version: 15.39.2
  • Database: MariaDB
  • Number of Workers: 2
  • Hosting: Digital Ocean

References:

  • ERPNext Function: trigger_reconciliation_for_queued_docs

File Path: erpnext/accounts/doctype/process_payment_reconciliation/process_payment_reconciliation.py