" 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' |
+--------+--------+-----------+--------+---------+------+----------+------------------------------------------------------------------------------------------------------+
| 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.
