Hi,
I am running v13.16.0 on a docker environment with MARIADB_VERSION 10.6 on a separate container. I use a middleware to fetch order data from Woocommerce to update ERPNext. As part of the process, I lookup customer data (matching email id), if customer records including contact and address have some changes I update them accordingly. The middleware sends HTTP requests for each change, using either /api/method/frappe.client.rename_doc or /api/resource/(doctype). I have been facing deadlocks although I have set a delay between requests to avoid overloading ERPNext API (9 seconds, while using Insominia directly it takes about 6 seconds to finish each call) to avoid overloading ERPNext API. I still face deadlocks, below the DB log line showing error and one of the error messages generated by ERPNext.
Can you please suggest what are the measures to take to avoid such issues, we are currently testing the workflow for each of the integration points with ERPNext and this issue is causing some concerns. Appreciate your help and support, thanks.
[ERROR] mysqld: Deadlock found when trying to get lock; try restarting transaction
"exception": "IndexError: tuple index out of range",
"exc": "["Traceback (most recent call last):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py\", line 148, in sql\n self._cursor.execute(query, values)\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/cursors.py\", line 148, in execute\n result = self._query(query)\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/cursors.py\", line 310, in _query\n conn.query(q)\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py\", line 548, in query\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py\", line 775, in _read_query_result\n result.read()\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py\", line 1163, in read\n self._read_result_packet(first_packet)\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py\", line 1236, in _read_result_packet\n self._read_rowdata_packet()\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py\", line 1270, in _read_rowdata_packet\n packet = self.connection._read_packet()\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/connections.py\", line 725, in _read_packet\n packet.raise_for_error()\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/protocol.py\", line 221, in raise_for_error\n err.raise_mysql_exception(self._data)\n File \"/home/frappe/frappe-bench/env/lib/python3.9/site-packages/pymysql/err.py\", line 143, in raise_mysql_exception\n raise errorclass(errno, errval)\npymysql.err.OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py\", line 458, in get_values\n out = self._get_values_from_table(fields, filters, doctype, as_dict, debug, order_by, update, for_update=for_update)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py\", line 603, in _get_values_from_table\n r = self.sql(\"select {fields} from `tab{doctype}` {where} {conditions} {order_by} {for_update}\"\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py\", line 173, in sql\n raise frappe.QueryDeadlockError\nfrappe.exceptions.QueryDeadlockError\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/app.py\", line 68, in application\n response = frappe.api.handle()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/api.py\", line 55, in handle\n return frappe.handler.handle()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/handler.py\", line 31, in handle\n data = execute_cmd(cmd)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/handler.py\", line 67, in execute_cmd\n return frappe.call(method, **frappe.form_dict)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py\", line 1208, in call\n return fn(*args, **newargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/client.py\", line 224, in rename_doc\n new_name = frappe.rename_doc(doctype, old_name, new_name, merge=merge)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py\", line 971, in rename_doc\n return rename_doc(*args, **kwargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/model/rename_doc.py\", line 69, in rename_doc\n update_link_field_values(link_fields, old, new, doctype)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/model/rename_doc.py\", line 271, in update_link_field_values\n frappe.db.set_value(parent, {docfield: old}, docfield, new)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py\", line 667, in set_value\n for name in self.get_values(dt, dn, 'name', for_update=for_update):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py\", line 463, in get_values\n elif (not ignore) and frappe.db.is_table_missing(e):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py\", line 153, in is_table_missing\n return e.args[0] == ER.NO_SUCH_TABLE\nIndexError: tuple index out of range\n"]"