Latest detected deadlock

Frappe Framework
1

LATEST DETECTED DEADLOCK

2024-03-03 16:42:09 0x7f647c449640
*** (1) TRANSACTION:
TRANSACTION 4515945, ACTIVE 95 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 373 lock struct(s), heap size 41080, 2606 row lock(s), undo log entries 333
MariaDB thread id 2985, OS thread handle 140069558326848, query id 9511592 localhost 127.0.0.1 _e981c470a29293e5 Creating sort index

select *, timestamp(posting_date, posting_time) as “timestamp”
from tabStock Ledger Entry
where item_code = ‘0102010010009’
and warehouse = ‘Open End Blow Room Floor - IHL’
and is_cancelled = 0
and (
posting_date < ‘2023-12-31’ or
(
posting_date = ‘2023-12-31’ and
time_format(posting_time, ‘%H:%i:%s’) < time_format(‘20:41:23.753190’, ‘%H:%i:%s’)
)
)
order by timestamp(posting_date, posting_time) desc, creation desc
limit 1
for update

How to handle this type of deadlock

2

i face same problem.
1 month looking for solution but nothing help… :smiley:

3

Indexing helped but it occurred 60% less
2 Options i have

  1. Different Servers for Read and Write
  2. Move Stock Ledger table to MongoDB

I have implemented MongoDB on checkins and it’s working fine

4

its better than no progress like me…
hahaha

What column you add the index btw?
(posting_date, posting_time) ?

5

Docstatus, warehouse, voucher_no these type of columns

6

Thanks a lot…
I’ll try it latter.

7

btw, what version of erpnext you use?

8

version-15 and version-14

9

There was a major perf fix in recent versions: perf: new column Posting Datetime in SLE to optimize stock ledger related queries by rohitwaghchaure · Pull Request #39800 · frappe/erpnext · GitHub

Try upgrading to v15.15.0 or a higher version.

Also, you’ve just shared half of error message. Deadlock happens between two threads so without knowing what 2nd thread was doing it’s impossible to debug it.