Hello buddies ! Hope you’ll are having good time with codes, coffee and monsoon. Same here 😛
Well, sharing a bit of technical gyaan (knowledge) from my last months experience. Considering here a well scaled application with multiple concurrent users trying to access its different functionalities. These functionalities may be using database for different CRUD operations and application being such huge, the database as well is expected to be huge with number of records in few tables being in millions.
There may be few cases wherein, multiple db queries from your application to your database would be querying / manipulating same table at a time. To handle such cases, the database engines (eg. ‘InnoDB’ – default MySQL engine) assigns locks on the tables/rows (depending on db operation) to the queries which are trying to access them. With this, if a query has taken lock on the table/rows, the db engine would stop allowing other db queries trying to use already locked tables. This way, the database engines tries to maintain data consistency.
Here, the query that is currently executing and has the lock is termed as ‘Blocking Query’ and all the other queries trying to access the already locked table, in other words, waiting for the blocking query to release the lock are known as ‘Blocked Queries’. So, there can be a possibility of having 1 Blocking Query and multiple blocked queries at any point of time.
The question here is ‘What will happen to the blocked query ?’
The queries fired from your application to your database would either be executing or be in blocked state. If blocked, the query would be waiting for some time (lock wait time) and if within that time, blocking doesn’t releases lock, the application would throw database exception stating lock wait time out.
The default lock wait time out for MySQL InnoDB engine is 50 seconds. This may be changed if required which would in turn reduce the chances of getting lock wait
time out issue. But this is not recommended, as you would not wish to have your web clients waiting for longer time and complaining you for poor application performance.
How do you know the reason for ‘Database exception stating lock wait time out’ ?
The reason is obvious, because your query waited for some threshold and was unable to execute because of a blocking query. But, you may also need to know, what was the blocking query that blocked the execution of a query which resulted to lock wait time out issue.
At any point of time, you may query your database’s metadata – information Schema to know what is/are the blocked queries and what is the blocking query. This would be helpful to know the current ‘Blocked Queries vs Blocking Queries‘.
Usually, in real time applications, you can’t wait for lock wait time out situation to happen and manually fire the query to find out Blocked Queries vs Blocking Queries right at that moment to find out the root cause. In short, in usual cases, the requirement is the analysis post lock wait time out has occurred. What would you do here ?
One option may be to have a batchjob / crontab that would keep firing the query to retrieve ‘Blocked Queries VS Blocking Queries’ from the information schema of
your database at every 30 seconds or say 1 minute. The retrieved value may be written in a table along with timestamp. Having this table with you, any time your application throws database exception stating lock wait timeout, you would be able the catch the culprit.
That’s all ! Tea break 😛