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

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 force Force postfix to use only IPv4 protocol?

Here are the steps to Force postfix to use only IPv4 protocol - 1. Open postfix configuration...

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...

How to repair vhost permissions for domains?

Here is the solution to repair the vhost permission - Login to your plesk server via SSH and...

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...

Powered by WHMCompleteSolution