We also face the same issue. A retail customer creates more than 20 invoices for one branch in about 5min. There’s a huge dead lock issue for us in journal entry, even after having separate series for every branch
I’ve encountered the same problems with Series and lock contention.
Last year I did an experiment: what if I stored the “Current Value” (which determines the next number to use) in Redis? It’s incredibly fast and has a built-in feature for auto-increment. This would avoid all locks.
I got it working, and it worked really well…
…except that in many cases, DocTypes don’t actually get created successfully. Because of validation failing or exceptions during record inserts.
SQL has rollback capability. Redis does not.
So I was incrementing “Current Value” very quickly, but often my values were thrown away. Leaving lots of “holes” in my series. And potentially I’d run out of numbers a lot faster.
I decided a fancier solution is necessary. And reverted back to the Series table…for now.
I also had the same deadlock problem with Sales Invoices and Purchase Receipts.
Solution 1:
This solution was to process tasks sequentially. Never in parallel.
I created a new Redis Queue with exactly 1 worker. Then enqueued all the Submits.
This helped a lot. Even though it means that Submitting takes much longer, because only 1 can be running at a time. But at least they all succeed.
Solution 2:
I wrote some code that forces a SQL lock to happen (perhaps on a Customer document.) By explicitly locking, all other SQL transactions were forced to wait for the lock to release. Once it does, the next process performs its own lock.
This ^ allows me to run many tasks in parallel, but without getting a Deadlock.
@szufisher Here’s one example. You can alter as needed.
Let’s say I want to lock an entire Customer and related documents. So that nothing else can interact with that customer’s data for a while.
I wrote a Python function similar to this:
def lock_customer_assets(customer_key: str):
"""
Apply SQL row locks to all a Customer's documents and assets.
"""
# Customer DocType
statement = """ SELECT * FROM `tabCustomer`
WHERE name = %(customer_key)s FOR UPDATE; """
frappe.db.sql(statement, values={"customer_key": customer_key})
# Sales Orders
statement = """ SELECT * FROM `tabSales Order`
WHERE customer = %(customer_key)s FOR UPDATE; """
frappe.db.sql(statement, values={"customer_key": customer_key})
# Delivery Notes
statement = """ SELECT * FROM `tabDelivery Note`
WHERE customer = %(customer_key)s FOR UPDATE; """
frappe.db.sql(statement, values={"customer_key": customer_key})
# ...
# ... Whatever other tables you feel like locking.
# ...
The concept is this:
You call lock_customer_assets at the beginning of your custom code.
For example, let’s say you have a script that loops through all Sales Orders, and tries to create a Sales Invoice? At the beginning of each loop iteration, you would call lock_customer_assets for that 1 customer.
The FOR UPDATE applies a row lock. No other SQL can access that row while the lock remains in place.
This means that <anything else> (standard code, your code, another loop iteration), cannot access that 1 Customer, or its Sales Orders, or its Delivery Notes.
They will have to Wait until …
The lock remains in place until the SQL transaction experiences either a COMMIT or ROLLBACK. You can make that happen manually yourself (perhaps at the end of each loop iteration). Or the Frappe framework does this automatically at the conclusion of an HTTP request.
Either way, once that SQL transaction ends? The row lock is released. And those <other processes> that have been Waiting can now run.
Things to be mindful of
You have to know when Commits are happening. If some standard ERPNext code is calling a commit, it can undo your lock.
The other SQL processes will not wait -forever- for the lock to release. There are a number of seconds until a Lock Timeout happens. So make sure your code isn’t taking too long…or you will cause other SQL transactions to fail.
Be careful (but creative!) with what you are locking.
In my example I’m locking an entire Customer and its Orders. For your own purposes, this may be too much.
You could also create your own, new DocType. Add just 1 row to it. And use that to control a custom process. Perhaps you want to ensure a custom function can only be running once, anywhere. So even if it’s called repeatedly it will never run in parallel with itself.
You can read more about this online. What I’m doing isn’t specific to Frappe framework. Plenty of applications use explicit SQL row locks to help control process flows, and avoid deadlocks.
