A question from Reddit’s /r/mysql:
Hey, I was planning to make a dashboard, where Users are subjected to make edits on their profiles every now and then, and I expect a high volume of requests to the database.
Having worked previously with MySQL for another Dashboard, I encountered errors for:
Maximum user connections - when I connected to the database only while query was to be executed
Lock wait timeout exceeded; try restarting transaction - when I connected to the database whenever a user logged into the dashboard, and ended the connection when he ended the session.
Both the approaches resulted in different errors, that too when I had a small user base, but they were active at the same time. Since, for the New Dashboard, I expect way more oscillating traffic, depending upon events, is there any way I can optimize my process of queries so that I can prevent the errors.
I am having trouble understanding what was being tried. The host language was not stated, but that choice could influence system behavior because of the way the host language connects to the database.
Assuming a 2-tier system where the web frontend contains the host language directly or indirectly (any of Perl, PHP, Python, Ruby as module or FCGI process), we will see as many connections to the database as there are web server processes or FCGI workers. Each connection spawns a thread in the database, and that will, when idle consume some 500 KB or so of memory in the database server. Obviously, the
max_connections value must be higher or equal to the maximum number of workers.
The “Lock wait timeout exceeded” means an InnoDB row lock has been held for a transaction duration longer than 50 seconds. This seems to be either a deadlock (two transactions trying to change an overlapping set of rows) that is not properly detected, or it is an attempt to hold a lock over a human user interface transactions - always an error.
For the latter problem often an optimistic locking approach is successful. That is: No locking when generating the data entry screen for the UI. When the data entry screen is comitted, start the transaction and guard it with a version number or the full data set.
Let’s look at this in a concrete fashion, using Python and a Flask app, using the ideas of Miguel Grinberg’s The Flask Mega-Tutorial as a foundation.
We want to edit some dummy table without timing out. The table is defined like so:
It has an integer field
id, which is the primary key, and two data fields,
city. We do not care what that means, it is just an example. We provide some sample data.
The web form shows these values, and as hidden fields, also preserves the pre-edit values. So we get the
id field to identify rows, and then hidden fields
oldcity, and the matching visible edit fields
Flask and WTForms demand a Form Class to handle this. We name it
EditForm and define our hidden and visible fields:
Finally we can put all the cabling into our routes:
We register only one action, for the routes
/index. This route loads the data from the database, by creating a
FormUpdater and calling
fetch() on it.
In case this form has been edited and the
Save button pressed, we end up in the if-branch for
validate_on_submit(). Here we call
update() on the
FormUpdater, and the re-fetch the data to make sure we show the updates. We also remember the number of modified rows in
In any case, we show the table as a form.
fetch() method simply runs
select id, name, city from editme and returns all rows. In order to make our life easier, we are using a
DictCursor, so we get named columns.
update() method generates a query that sets the new values for
city, identifying the row by
id. The where-clasuse of the update statement contains the additional condition
AND name = %s AND city = %s, where we put the old name and old city into the placeholders.
So when we generate the form, we do not lock the record, and we put the old name and old city into the form itself in hidden fields. When we accept the form, we check if the name and city are unchanged by comparing them against the old name and old city as they have been submitted back.
In case they have been changed, the update fails and the user edits are lost. Otherwise the update statement goes through. We report back the number of rows changes, 0 or 1.
More intelligence could be put into the function: In case we do not modify any row, we could reload the row and check if the oldname still matches - if yes, we could accept any user edit on the name field, as there is no collision. Then we could do the same with the city.
More intelligence could be put into the editor: In case we have any collision, we could flash and highlight the collision and allow the user to resubmit their change, resolving the conflict.