24x7 Technical Support Forums
Mysql LOCK on table's fix - Printable Version

+- 24x7 Technical Support Forums (https://www.24x7servermanagement.net)
+-- Forum: Linux Server Management (https://www.24x7servermanagement.net/Forum-Linux-Server-Management)
+--- Forum: Plesk (https://www.24x7servermanagement.net/Forum-Plesk)
+--- Thread: Mysql LOCK on table's fix (/Thread-Mysql-LOCK-on-table-s-fix)



Mysql LOCK on table's fix - Mandeep - 12-11-2016

Hello there,

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

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

Code:
[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. Idea

Mysql provides a way to change the priority of the sequence in which the queries should execute. Cool

Trick:

1. edit my.cnf file using your favorite editor.

Code:
vi /etc/my.cnf


2. Add parameter low_priority_updates=1
Code:
low_priority_updates=1

3. save my.cnf

4. restart mysql service


Code:
service mysqld restart

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

Hope this article helps !! Smile