并发高时,出现锁等待:
----------------------------
*************************** 1. row ***************************
waiting_trx_id: 14537594
waiting_thread: 11373
INSERT into sch_work_time_temp(sch_work_time_id,SCH_WORK_TIME_STAT_ID,S_HOUR,S_MINUTE,WORK_DATE,SCH_DEVICE_CODE,SCH_STU_DEVICE_CODE,CRT_TIME,LOCK_FLAG)select sch_work_time_id,sch_work_time_stat_id,s_hour,s_minute,work_date,sch_device_code,SCH_STU_DEVICE_CODE,NOW() crt_time,null lock_flag from sch_work_time where sch_work_time_id>(select conf_value from dim_sys_conf where conf_id = 'PUSH_WORK_ID' ) ORDER BY sch_work_time_id LIMIT 0,500
blocking_trx_id: 14537336
blocking_thread: 11365
blocking_query: select CONCAT_WS('_',SCH_GRADE_ID,SCH_KQ_TYPE_ID,SCH_KQJ_TYPE_ID) KEYSTR ,S_MINUTE,S_MINUTE1,S_MINUTE2,SCH_TIME_ID,SCH_WP_ID from sch_rule WHERE SCH_WEEK_ID = (DAYOFWEEK(NOW())-1) AND SCH_GRADE_ID IN (SELECT DISTINCT B.SCH_GRADE_ID FROM sch_work_time_temp A LEFT JOIN sch_student B ON A.SCH_STU_DEVICE_CODE = B.SCH_STU_DEVICE_CODE AND B.VALID_FLAG=1 AND B.DEL_FLAG=0 WHERE A.LOCK_FLAG = '201709270957')
1 row in set (0.00 sec)
show procsslist:
| 11365 | lgl | jxweb11:46200 | bdzh | Query | 28 | Sending data | select CONCAT_WS('_',SCH_GRADE_ID,SCH_KQ_TYPE_ID,SCH_KQJ_TYPE_ID) KEYSTR ,S_MINUTE,S_MINUTE1,S_MIN |
| 11369 | lgl | jxweb11:48568 | bdzh | Sleep | 3728 | | NULL |
| 11370 | lgl | jxweb11:48570 | bdzh | Sleep | 3728 | | NULL |
| 11371 | lgl | jxweb11:48572 | bdzh | Sleep | 3728 | | NULL |
| 11372 | lgl | jxweb11:48574 | bdzh | Sleep | 23 | | NULL |
| 11373 | lgl | jxweb11:48726 | bdzh | Query | 26 | statistics | INSERT into sch_work_time_temp(sch_work_time_id,SCH_WORK_TIME_STAT_ID,S_HOUR,S_MINUTE,WORK_DATE,SCH_ |
11365的查询操作阻塞了11373的插入操作。。。而查询并没锁定表。
两个thread的状态:
Sending data:
The thread is reading and processing rows for a SELECT statement, and sending data to the client.
Because operations occurring during this state tend to perform large amounts of disk access (reads), it is
often the longest-running state over the lifetime of a given query.
statistics:
The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long
time, the server is probably disk-bound performing other work.
上面两种状态直指disk io问题,有可能是io问题引起的阻塞?
so explain select:
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------------------------------------------------------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 539 | Using where; Start temporary |
| 1 | SIMPLE | sch_rule | ALL | NULL | NULL | NULL | NULL | 719 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | B | ALL | NULL | NULL | NULL | NULL | 15354 | Using where; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------------------------------------------------------------+
create some indexes and explain again:
+----+--------------+-------------+------+---------------------+-------------------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+------+---------------------+-------------------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | NULL | NULL | NULL | Using where |
| 1 | SIMPLE | sch_rule | ref | ind_sch_grade_id | ind_sch_grade_id | 9 | <subquery2>.SCH_GRADE_ID | 5 | Using where |
| 2 | MATERIALIZED | A | ALL | ind_sch_work_time_1 | NULL | NULL | NULL | 21 | Using where |
| 2 | MATERIALIZED | B | ref | ind_sch_student_1 | ind_sch_student_1 | 183 | bdzh_test.A.SCH_STU_DEVICE_CODE | 1 | Using where |
+----+--------------+-------------+------+---------------------+-------------------+---------+---------------------------------+------+-------------+
and case over...