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

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

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 repair vhost permissions for domains?

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

Powered by WHMCompleteSolution