Dashboards with charts error - could not serialize access due to concurrent update

[sunil-mel]

While running refreshing the dashboard page with multiple charts, I am getting an error message with postgres database.

On UI - Internal Server Error
Backend - psycopg2.errors.SerializationFailure: could not serialize access due to concurrent update

Using Below versions

frappe 15.37.0
insights 2.2.4

What I found is run_doc_method is called multiple times (twice number of charts) and I feel same query is being hit multiple times at same time. Please note I am using same query in multiple charts.

Full trace of the error

127.0.0.1 - - [09/Sep/2024 13:00:09] "POST /api/method/run_doc_method HTTP/1.1" 500 -
13:00:09 web.1         | Traceback (most recent call last):
13:00:09 web.1         |   File "apps/frappe/frappe/app.py", line 114, in application
13:00:09 web.1         |     response = frappe.api.handle(request)
13:00:09 web.1         |                ^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/api/__init__.py", line 49, in handle
13:00:09 web.1         |     data = endpoint(**arguments)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
13:00:09 web.1         |     return frappe.handler.handle()
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/handler.py", line 49, in handle
13:00:09 web.1         |     data = execute_cmd(cmd)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
13:00:09 web.1         |     return frappe.call(method, **frappe.form_dict)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/__init__.py", line 1768, in call
13:00:09 web.1         |     return fn(*args, **newargs)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/handler.py", line 336, in run_doc_method
13:00:09 web.1         |     response = doc.run_method(method, **args)
13:00:09 web.1         |                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 962, in run_method
13:00:09 web.1         |     out = Document.hook(fn)(self, *args, **kwargs)
13:00:09 web.1         |           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 1322, in composer
13:00:09 web.1         |     return composed(self, method, *args, **kwargs)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 1304, in runner
13:00:09 web.1         |     add_to_return_value(self, fn(self, *args, **kwargs))
13:00:09 web.1         |                               ^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 959, in fn
13:00:09 web.1         |     return method_object(*args, **kwargs)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
13:00:09 web.1         |     return func(*args, **kwargs)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/insights/insights/insights/doctype/insights_dashboard/insights_dashboard.py", line 70, in fetch_chart_data
13:00:09 web.1         |     return self.run_query(query_name, additional_filters=filters)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/insights/insights/insights/doctype/insights_dashboard/insights_dashboard.py", line 81, in run_query
13:00:09 web.1         |     new_results = query.fetch_results(additional_filters=additional_filters)
13:00:09 web.1         |                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/insights/insights/insights/doctype/insights_query/insights_query.py", line 193, in fetch_results
13:00:09 web.1         |     self.db_set(
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 1208, in db_set
13:00:09 web.1         |     self.load_doc_before_save()
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 1112, in load_doc_before_save
13:00:09 web.1         |     self._doc_before_save = frappe.get_doc(self.doctype, self.name, for_update=True)
13:00:09 web.1         |                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/__init__.py", line 1333, in get_doc
13:00:09 web.1         |     doc = frappe.model.document.get_doc(*args, **kwargs)
13:00:09 web.1         |           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 85, in get_doc
13:00:09 web.1         |     return controller(*args, **kwargs)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 126, in __init__
13:00:09 web.1         |     self.load_from_db()
13:00:09 web.1         |   File "apps/frappe/frappe/model/document.py", line 167, in load_from_db
13:00:09 web.1         |     d = frappe.db.get_value(
13:00:09 web.1         |         ^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/database/database.py", line 519, in get_value
13:00:09 web.1         |     result = self.get_values(
13:00:09 web.1         |              ^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/database/database.py", line 623, in get_values
13:00:09 web.1         |     out = self._get_values_from_table(
13:00:09 web.1         |           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/database/database.py", line 896, in _get_values_from_table
13:00:09 web.1         |     return query.run(as_dict=as_dict, debug=debug, update=update, run=run, pluck=pluck)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/query_builder/utils.py", line 87, in execute_query
13:00:09 web.1         |     result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep
13:00:09 web.1         |              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/database/postgres/database.py", line 213, in sql
13:00:09 web.1         |     return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
13:00:09 web.1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
13:00:09 web.1         |   File "apps/frappe/frappe/database/database.py", line 234, in sql
13:00:09 web.1         |     self._cursor.execute(query, values)
13:00:09 web.1         | psycopg2.errors.SerializationFailure: could not serialize access due to concurrent update
13:00:09 web.1         | 
13:00:09 web.1         | 
13:00:09 web.1         | 127.0.0.1 - - [09/Sep/2024 13:00:09] "POST /api/method/run_doc_method HTTP/1.1" 500 -