myisam读操作之间并不互锁,但在我的系统当中却会时常出现这样的情况,
一个select 进程处于sending data状态,而其它select进程则处于locked状态.
这样的状况并不总出现,可一旦出现就会非常麻烦,整个系统都会停滞!以下是show processlist的结果:+------+-------------+-----------------+-----------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+-----------------+-----------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 3 | system user | | NULL | Connect | 1323 | Waiting for master to send event | NULL |
| 4 | system user | | news_data | Connect | -1066 | Locked | UPDATE E_info SET info_reprint = info_reprint + 1 WHERE infoID = 2724158912 |
| 1130 | web_dev | 10.0.0.46:57567 | news_data | Query | 92 | Sending data | ( SELECT E_info.infoID,E_info.info_time , E_role_info.roleID,E_role_info.related
FROM `E |
| 1133 | web_dev | 10.0.0.46:57685 | news_data | Query | 74 | Locked | ( SELECT 3109066 AS 'columnID', '4.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1134 | web_dev | 10.0.0.46:57727 | news_data | Query | 55 | Locked | ( SELECT 2601693 AS 'columnID', '1.1.1.' AS 'cno', E_info_100.* , E_role_info_100.roleID,E_role_in |
| 1135 | web_dev | 10.0.0.46:57740 | news_data | Query | 52 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1136 | web_dev | 10.0.0.46:57744 | news_data | Query | 51 | Locked | ( SELECT 3032931 AS 'columnID', '1.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1137 | web_dev | 10.0.0.46:57756 | news_data | Query | 47 | Locked | ( SELECT 2378187 AS 'columnID', '3.2.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1138 | web_dev | 10.0.0.46:57794 | news_data | Query | 38 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1139 | web_dev | 10.0.0.46:57797 | news_data | Query | 38 | Locked | ( SELECT 2243835 AS 'columnID', '2.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1140 | web_dev | 10.0.0.46:57805 | news_data | Query | 34 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1141 | web_dev | 10.0.0.46:57834 | news_data | Query | 28 | Locked | ( SELECT 350674 AS 'columnID', '2.2.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_100 |
| 1142 | web_dev | 10.0.0.46:57849 | news_data | Query | 25 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1143 | web_dev | 10.0.0.46:57854 | news_data | Query | 25 | Locked | ( SELECT 79991 AS 'columnID', '2.1.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1144 | web_dev | 10.0.0.46:57856 | news_data | Query | 25 | Locked | ( SELECT 183824 AS 'columnID', '2.5.' AS 'cno', E_info_100.* , E_role_info_100.roleID,E_role_info_ |
| 1145 | web_dev | 10.0.0.46:57914 | news_data | Query | 8 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1146 | web_dev | 10.0.0.46:57919 | news_data | Query | 6 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1149 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+-------------+-----------------+-----------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
请斑竹等高手为我解惑!
一个select 进程处于sending data状态,而其它select进程则处于locked状态.
这样的状况并不总出现,可一旦出现就会非常麻烦,整个系统都会停滞!以下是show processlist的结果:+------+-------------+-----------------+-----------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+-----------------+-----------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 3 | system user | | NULL | Connect | 1323 | Waiting for master to send event | NULL |
| 4 | system user | | news_data | Connect | -1066 | Locked | UPDATE E_info SET info_reprint = info_reprint + 1 WHERE infoID = 2724158912 |
| 1130 | web_dev | 10.0.0.46:57567 | news_data | Query | 92 | Sending data | ( SELECT E_info.infoID,E_info.info_time , E_role_info.roleID,E_role_info.related
FROM `E |
| 1133 | web_dev | 10.0.0.46:57685 | news_data | Query | 74 | Locked | ( SELECT 3109066 AS 'columnID', '4.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1134 | web_dev | 10.0.0.46:57727 | news_data | Query | 55 | Locked | ( SELECT 2601693 AS 'columnID', '1.1.1.' AS 'cno', E_info_100.* , E_role_info_100.roleID,E_role_in |
| 1135 | web_dev | 10.0.0.46:57740 | news_data | Query | 52 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1136 | web_dev | 10.0.0.46:57744 | news_data | Query | 51 | Locked | ( SELECT 3032931 AS 'columnID', '1.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1137 | web_dev | 10.0.0.46:57756 | news_data | Query | 47 | Locked | ( SELECT 2378187 AS 'columnID', '3.2.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1138 | web_dev | 10.0.0.46:57794 | news_data | Query | 38 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1139 | web_dev | 10.0.0.46:57797 | news_data | Query | 38 | Locked | ( SELECT 2243835 AS 'columnID', '2.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1140 | web_dev | 10.0.0.46:57805 | news_data | Query | 34 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1141 | web_dev | 10.0.0.46:57834 | news_data | Query | 28 | Locked | ( SELECT 350674 AS 'columnID', '2.2.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_100 |
| 1142 | web_dev | 10.0.0.46:57849 | news_data | Query | 25 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1143 | web_dev | 10.0.0.46:57854 | news_data | Query | 25 | Locked | ( SELECT 79991 AS 'columnID', '2.1.1.' AS 'cno', E_info_100.infoID,E_info_100.info_title, E_info_10 |
| 1144 | web_dev | 10.0.0.46:57856 | news_data | Query | 25 | Locked | ( SELECT 183824 AS 'columnID', '2.5.' AS 'cno', E_info_100.* , E_role_info_100.roleID,E_role_info_ |
| 1145 | web_dev | 10.0.0.46:57914 | news_data | Query | 8 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1146 | web_dev | 10.0.0.46:57919 | news_data | Query | 6 | Locked | ( SELECT E_info_100.infoID,E_info_100.info_time , E_role_info_100.roleID,E_role_info_100.related |
| 1149 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+-------------+-----------------+-----------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
请斑竹等高手为我解惑!
试试
什么存储引擎? 打开SELECT的模式是什么?理论上只看到了一个 update 应该不会锁这些SELECT,除非你使用了事务,或者 for update 查询。
存储引擎是myisam, 没有使用事务,E_info表上倒是有触发器,也没有使用for update查询!
是啊,而且update也是被锁的
http://www.mysqlperformancetuning.com/how-to-reduce-table_locks_waited-in-mysql-myisam
# 0.00 seconds A select query (ie. read-only) accesses the table, it will take around 2 seconds to complete.
# 0.01 seconds Another select query accesses the table, it takes no time and will complete in an instant as it can run in parallel.0.02 seconds An insert, delete or update query (ie. write) attempts to write to that very same table before the first select has completed.# 0.03 seconds A select query comes in, again waiting for the first select to complete.
# 0.04 seconds Another select query comes in, again waiting for the first select to complete.
# 0.05 seconds Yet another select query comes in, again waiting for the first select to complete.
# .
# 1.99 seconds (~1000 queries later) Yet another select query comes in, again waiting for the first select to complete.
And so on and so forth. The queries in orange and red are blocked and have to wait for the first select to complete before they can be executed. The selects in red are blocked by the write operation in orange which is in turn blocked by the long-running select in green. This is what causes the table_locks_waited value to grow. 难道我的现象也属于这种情况?先进来的select会锁住后进来的写操作,写操作之后的select都会因该写操作而被锁住?
版主指的是php的代码?还是sql语句?
没有版主,真的没有, 我根本就不懂得运用人工lock, 事务也不会用,for update干吗的也不清楚!
1. MYISAM表FOR UPDATE没用,忽略!
2. SHOW FULL PROCESSLIST, 可以看到你正在执行的所有语句!
3. 看看你的表结构,检查下你的索引状况。
4. 如果索引状况都OK的话,就修改下你的语句!当然我上面的只是从数据库角度来看, 系统方面的还没说。
更进一步的优化,私聊!
读操作之间不是不影响吗?