How to overcome the Mysql LOCK on table?

" Lock on the tables " in MySQL could cause a serious performance issue.

For example we will discuss a case here:

One of the client executes a SELECT command on the table and the query runs for a long hour. Meanwhile another client executes an update command on the same table.

Now what happens is that in MySQL, the UPDATE query has a higher priory than SELECT statement. 
This causes SELECT query to pause until UPDATE is finished.

[root@server ~]# mysqladmin pr -utest -p`cat /etc/psa/.psa.shadow`
+--------+--------+-----------+--------+---------+------+----------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------+-----------+--------+---------+------+----------+------------------------------------------------------------------------------------------------------+
| 230932 | test | localhost | test| Query | 1 | Locked | UPDATE test_sessions_data SET data = 'a:3:{s:17:\"_saved_session_fg\";s:32:\"c1369e065b8f7b321ade7d |
| 230933 | test | localhost | test| Query | 1 | Locked | SELECT data FROM test_sessions_data WHERE sessid = '40b25cf74c59b58271cb3951838065ed' |
| 230934 | test | localhost | test| Query | 1 | Locked | SELECT data FROM test_sessions_data WHERE sessid = '497530d9fb6b003dd581998da76b197c' |
| 230935 | test | localhost | test| Query | 1 | Locked | SELECT data FROM test_sessions_data WHERE sessid = '13c3ab32e4b2dad43b87abde673033da' |
| 230936 | test | localhost | test| Query | 1 | Locked | SELECT data FROM test_sessions_data WHERE sessid = '7668b9b961a8dc11fbddfdf6cde690ee' |
| 230937 | test | localhost | test| Query | 0 | Locked | SELECT data FROM test_sessions_data WHERE sessid = '3b9f1512bb894bea8186ec1fd3bcffc0' |


There is a simple harmless fix to this issue.

Solution -

Mysql provides a way to change the priority of the sequence in which the queries should execute.
1. Edit my.cnf file using your favorite editor - vi /etc/my.cnf

2. Add parameter low_priority_updates=1
low_priority_updates=1

3. Save my.cnf.

4. Restart mysql service
service mysqld restart

You will no longer face table locking issue and notice a great improvement in mysql performance.

Was this answer helpful?

 Print this Article

Also Read

How to install and configure the Mod Security on Plesk 12 admin?

Mod Security installer is used for preventing the attacks on web applications. Mod security will...

Required PHP version greater than equal to 5.4.45 AND found version is : 5.3.3

While installing application via Softaculous Application Installer it appears the following error...

How to retrieve plesk panel admin password?

Steps to retrieve plesk panel admin password -For Plesk versions 10.x-12.x :Log in to server via...

Unable to connect FTP - Incorrect Password or User Account is disabled in Windows

Error - The domain's primary ftp user account is disabled in Windows or has an invalid password....

How to resolve POST request fails while adding the sub-domain?

While adding sub-domain, I'm facing issue with POST request. I tried adding sub-domain, server is...

Powered by WHMCompleteSolution