Deadlock Error in ToDo table's update_in_reference query

Frappe Framework
1

Hi everyone, our project has been frequently getting Deadlock Error for some operations. The logs seen are -

Traceback with variables (most recent call last):
File “apps/frappe/frappe/database/database.py”, line 235, in sql
self._cursor.execute(query, values)
self = <frappe.database.mariadb.database.MariaDBDatabase object at 0x7a78cfb770e0>
query = ‘SELECT allocated_to FROM tabToDo WHERE reference_type=%(param1)s AND reference_name=1 AND status NOT IN (%(param2)s,%(param3)s) AND COALESCE(allocated_to,%(param4)s)<>%(param5)s ORDER BY creation DESC FOR UPDATE’
values = {‘param1’: ‘Ticket’, ‘param2’: ‘Cancelled’, ‘param3’: ‘Closed’, ‘param4’: ‘’, ‘param5’: ‘’}
as_dict = False
as_list = 0
debug = False
ignore_ddl = 0
auto_commit = 0
update = None
explain = False
run = True
pluck = True
as_iterator = False
trace_id = None
File “env/lib/python3.12/site-packages/pymysql/cursors.py”, line 153, in execute
result = self._query(query)
self = <pymysql.cursors.Cursor object at 0x7a78cd174fe0>
query = “SELECT allocated_to FROM tabToDo WHERE reference_type=‘HD Ticket’ AND reference_name=1 AND status NOT IN (‘Cancelled’,‘Closed’) AND COALESCE(allocated_to,‘’)<>‘’ ORDER BY creation DESC FOR UPDATE”
args = {‘param1’: ‘Ticket’, ‘param2’: ‘Cancelled’, ‘param3’: ‘Closed’, ‘param4’: ‘’, ‘param5’: ‘’}
File “env/lib/python3.12/site-packages/pymysql/cursors.py”, line 322, in _query
conn.query(q)
self = <pymysql.cursors.Cursor object at 0x7a78cd174fe0>
q = “SELECT allocated_to FROM tabToDo WHERE reference_type=‘Ticket’ AND reference_name=1 AND status NOT IN (‘Cancelled’,‘Closed’) AND COALESCE(allocated_to,‘’)<>‘’ ORDER BY creation DESC FOR UPDATE”
conn = <pymysql.connections.Connection object at 0x7a78cfb761e0>
File “env/lib/python3.12/site-packages/pymysql/connections.py”, line 563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
self = <pymysql.connections.Connection object at 0x7a78cfb761e0>
sql = b"SELECT allocated_to FROM tabToDo WHERE reference_type=‘Ticket’ AND reference_name=1 AND status NOT IN (‘Cancelled’,‘Closed’) AND COALESCE(allocated_to,‘’)<>‘’ ORDER BY creation DESC FOR UPDATE"
unbuffered = False
File “env/lib/python3.12/site-packages/pymysql/connections.py”, line 825, in _read_query_result
result.read()
self = <pymysql.connections.Connection object at 0x7a78cfb761e0>
unbuffered = False
result = <pymysql.connections.MySQLResult object at 0x7a78cd2cf380>
File “env/lib/python3.12/site-packages/pymysql/connections.py”, line 1206, in read
self._read_result_packet(first_packet)
self = <pymysql.connections.MySQLResult object at 0x7a78cd2cf380>
first_packet = <pymysql.protocol.MysqlPacket object at 0x7a78cfb9bf70>
File “env/lib/python3.12/site-packages/pymysql/connections.py”, line 1283, in _read_result_packet
self._read_rowdata_packet()
self = <pymysql.connections.MySQLResult object at 0x7a78cd2cf380>
first_packet = <pymysql.protocol.MysqlPacket object at 0x7a78cfb9bf70>
File “env/lib/python3.12/site-packages/pymysql/connections.py”, line 1330, in _read_rowdata_packet
packet = self.connection._read_packet()
self = <pymysql.connections.MySQLResult object at 0x7a78cd2cf380>
rows =
File “env/lib/python3.12/site-packages/pymysql/connections.py”, line 775, in _read_packet
packet.raise_for_error()
self = <pymysql.connections.Connection object at 0x7a78cfb761e0>
packet_type = <class ‘pymysql.protocol.MysqlPacket’>
buff = bytearray(b’\xff\xbd\x04#40001Deadlock found when trying to get lock; try restarting transaction’)
packet_header = b’K\x00\x00\x04’
btrl = 75
btrh = 0
packet_number = 4
bytes_to_read = 75
recv_data = b’\xff\xbd\x04#40001Deadlock found when trying to get lock; try restarting transaction’
packet = <pymysql.protocol.MysqlPacket object at 0x7a78cfb98d30>
File “env/lib/python3.12/site-packages/pymysql/protocol.py”, line 219, in raise_for_error
err.raise_mysql_exception(self._data)
self = <pymysql.protocol.MysqlPacket object at 0x7a78cfb98d30>
errno = 1213
File “env/lib/python3.12/site-packages/pymysql/err.py”, line 150, in raise_mysql_exception
raise errorclass(errno, errval)
data = b’\xff\xbd\x04#40001Deadlock found when trying to get lock; try restarting transaction’
errno = 1213
errval = ‘Deadlock found when trying to get lock; try restarting transaction’
errorclass = <class ‘pymysql.err.OperationalError’>
pymysql.err.OperationalError: (1213, ‘Deadlock found when trying to get lock; try restarting transaction’)

The deadlock is happening for the query in “update_in_reference” method in todo.py

Looking at db logs the analysis was following.
oth queries are trying to acquire exclusive row locks (FOR UPDATE) on rows in the same table.

  • Even though they target different reference_name values, the rows scanned to satisfy the WHERE and ORDER BY conditions overlap due to status NOT IN (...)

High row lock contention

  • Transaction 1 is locking 20001 rows.

  • Transaction 2 is trying to lock 4 rows that fall within the same table space

High concurrency + row-level locking on overlapping data ranges often triggers deadlocks. Although there is no overlap in the values between the two queries, both transactions end up locking adjacent rows or index gaps, causing lock contention

This seems to be entirely frappe related issue. Any advice on how to resolve this?