InternalError: (1205, u'Lock wait timeout exceeded; try restarting transaction')

ERPNext
1

Hi All,

I am facing this error in V10.x.x branch of frappe and erpnext.
I tried to find the solution on discuss form but could not able find it.
Even existing 2 topics aren’t addressed yet and it the issue is still persisting

Topic 1:

Topic 2:

May be most of the users aren’t facing this issue because of their there level of usage, but we’re submitting 2,000+ Sales Invoice with 120 line items each Invoice everyday. 10+ sales admin are working simultaneously.

I’ve two servers, MASTER and SLAVE and both have decent resources configuration on AWS, SLAVE Server I m using for reporting and search query.

Processors: 8
RAM: 16 GB
SSD: 100 GB

Traceback (most recent call last):
  File "/home/frappe-master/frappe-master/apps/frappe/frappe/desk/form/save.py", line 22, in savedocs

    doc.save()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 259, in save

    return self._save(*args, **kwargs)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 282, in _save

    self.insert()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 215, in insert

    self.set_new_name()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 375, in set_new_name

    set_new_name(self)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 48, in set_new_name

    set_name_by_naming_series(doc)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 69, in set_name_by_naming_series

    doc.name = make_autoname(doc.naming_series+'.#####', '', doc)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 98, in make_autoname

    n = parse_naming_series(parts, doctype, doc)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 113, in parse_naming_series

    part = getseries(n, digits, doctype)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 134, in getseries

    current = frappe.db.sql("select `current` from `tabSeries` where name=%s for update", (key,))

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/database.py", line 166, in sql

    self._cursor.execute(query, values)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute

    result = self._query(query)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query

    conn.query(q)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query

    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result

    result.read()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1073, in read

    self._read_result_packet(first_packet)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1143, in _read_result_packet

    self._read_rowdata_packet()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1177, in _read_rowdata_packet

    packet = self.connection._read_packet()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet

    packet.check_error()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error

    err.raise_mysql_exception(self._data)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception

    raise errorclass(errno, errval)

InternalError: (1205, u'Lock wait timeout exceeded; try restarting transaction')

 

Traceback (most recent call last):

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/app.py", line 62, in application

    response = frappe.handler.handle()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/handler.py", line 22, in handle

    data = execute_cmd(cmd)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/handler.py", line 53, in execute_cmd

    return frappe.call(method, **frappe.form_dict)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/__init__.py", line 939, in call

    return fn(*args, **newargs)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/desk/form/save.py", line 22, in savedocs

    doc.save()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 259, in save

    return self._save(*args, **kwargs)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 282, in _save

    self.insert()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 215, in insert

    self.set_new_name()

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/document.py", line 375, in set_new_name

    set_new_name(self)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 48, in set_new_name

    set_name_by_naming_series(doc)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 69, in set_name_by_naming_series

    doc.name = make_autoname(doc.naming_series+'.#####', '', doc)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 98, in make_autoname

    n = parse_naming_series(parts, doctype, doc)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 113, in parse_naming_series

    part = getseries(n, digits, doctype)

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/model/naming.py", line 134, in getseries

    current = frappe.db.sql("select `current` from `tabSeries` where name=%s for update", (key,))

  File "/home/frappe-master/frappe-master/apps/frappe/frappe/database.py", line 166, in sql

    self._cursor.execute(query, values)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute

    result = self._query(query)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query

    conn.query(q)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query

    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result

    result.read()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1073, in read

    self._read_result_packet(first_packet)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1143, in _read_result_packet

    self._read_rowdata_packet()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1177, in _read_rowdata_packet

    packet = self.connection._read_packet()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet

    packet.check_error()

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error

    err.raise_mysql_exception(self._data)

  File "/home/frappe-master/frappe-master/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception

    raise errorclass(errno, errval)

InternalError: (1205, u'Lock wait timeout exceeded; try restarting transaction')

Can anyone please assist me to fix this issue?

Best Regards,
Navdeep

2

Have you tried tuning the innodb_buffer_pool_size ? I know that this value if small can make systems slower.

3

Yes, I tried that but it’s not working for me.
I am using 11G for innodb_buffer_pool_size

4

Try increasing your http_timeout:

 bench config http_timeout 6000

or higher. This has to do with the synchronous WSGI server.

5

thanks for suggestion, but I’ve already tried this, it’s not working as well.
I am not getting nginx/http request time out error message, it’s related to deadlock in MySQL.

6

I am suffering with the same issue. Any solution you found?

7

I had the same problem for a while. I don’t know how i solve it or if it was fixed in v11, or if it just disappear by itself. It’s related to a lock in the tabSeries that is used to create the document names.

8

Change transaction isolation to READ-COMMITTED. (Default for InnoDB is REPEATABLE-READ which creates this problem.)

9

may be restarting everything will solve this…

10

I’ve done that, but that’s not an ideal solution. It’s happening frequently and can’t restart my server every time.

11

Is anyone able to resolve this completely? If yes, please share the steps.

12

We are also facing similar problem while creating Delivery notes, Material Request etc. In version 12.
Have also upgraded the server with SSD and 256 gb RAM it has reduced the number but still appearing. So far only solution is to keep retrying and sometimes even need to retry 10 times
As per our analysis, tabBin and tabSeries are causing majority of the deadlocks
Any help or clue will be highly appreciated

13

Have anyone try this solution ? How safe it is ? What is the precautions ? I’m facing the same issue here and it seems this is the only viable solution

14

Try indexing the major columns under Delivery Note, Material Requests, Bin. Also see which query is creating a lock. In my case it was workflow action delete query which was locking my tables.