㈠ 背景介绍： ㈡ 定位原因： insert into t (col1, col2, col3, col4, col5, col6, col7) values (3532082239485507011_130_99, 130_99, 130, 99, 3532082239485507011, 2172353000317425008, 29078) ㈢ 处理思路： select trx_id,trx_state,trx_started,tr
㈠ 背景介绍：㈡ 定位原因：insert into t (col1, col2, col3, col4, col5, col6, col7) values ('3532082239485507011_130_99', '130_99', 130, 99, 3532082239485507011, 2172353000317425008, 29078)
㈢ 处理思路：select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_weight,trx_mysql_thread_id from information_schema.innodb_trx where trx_state='running';
㈣ 我的疑问：㈤ 原来如此：㈥ 模拟场景：
session_a：mysql> begin;query ok, 0 rows affected (0.00 sec)mysql> select emp_no,hire_date from employees where emp_no=10170 for update;+--------+------------+| emp_no | hire_date |+--------+------------+| 10170 | 1986-01-02 |+--------+------------+1 row in set (0.00 sec)session_b:mysql> select emp_no,hire_date,sleep(3) from employees where emp_no=10170 for update;
过段时间在a做commit，b会执行、并被记录到slow log中：
# time: 140818 22:37:31# user@host: root[root] @ localhost [] id: 1# query_time: 3.049016 lock_time: 0.018891 rows_sent: 1 rows_examined: 1use employees;set timestamp=1408372651;select emp_no,hire_date,sleep(3) from employees where emp_no=10170 for update;
session_a：mysql> begin;query ok, 0 rows affected (0.00 sec)mysql> select emp_no,hire_date from employees where emp_no=10170 for update;+--------+------------+| emp_no | hire_date |+--------+------------+| 10170 | 1986-01-02 |+--------+------------+1 row in set (0.00 sec)session_b:mysql> select emp_no,hire_date from employees where emp_no=10170 for update;
㈦ 我的收获：
我们日常做性能剖析实际上应该包括2个方面：
1）基于执行时间的分析
2）基于等待时间的分析
by water
good luck！