当前位置:首页 > mysql > 正文内容

mysql中长事务详解

phpmianshi4年前 (2017-05-08)mysql180

什么是长事务

运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。

下面我将演示下如何开启事务及模拟长事务:

#假设我们有一张stu_tb表,结构及数据如下
stu_id 唯一索引
mysql> select * from stu_tb;
+--------------+--------+----------+---------------------+---------------------+
|           id | stu_id | stu_name | create_time         | update_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | from1    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
|            2 |   1002 | dfsfd    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
|            3 |   1003 | fdgfg    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
|            4 |   1004 | sdfsdf   | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
+--------------+--------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

#显式开启事务,可用begin或start transactionmysql

> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu_tb where stu_id = 1002 for update;

#如果我们不及时提交上个事务,那么这个事务就变成了长事务,当其他会话要操作这条数据时,就会一直等待。


如何找到长事务

遇到事务等待问题时,我们首先要做的是找到正在执行的事务。 information_schema.INNODB_TRX 表中包含了当前innodb内部正在运行的事务信息,这个表中给出了事务的开始时间,我们可以稍加运算即可得到事务的运行时间。

SELECT
    t.*, to_seconds(now()) - to_seconds(t.trx_started) idle_time
FROM
    information_schema.innodb_trx t;

*************************** 1. row ***************************
                    trx_id: 6168
                 trx_state: RUNNING
               trx_started: 2019-09-16 11:08:27
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 11
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0trx_autocommit_non_locking: 0
                 idle_time: 170


在结果中idle_time是计算产生的,也是事务的持续时间。但事务的trx_query是NUL,这并不是说事务什么也没执行,一个事务可能包含多个SQL,如果SQL执行完毕就不再显示了。当前事务正在执行,innodb也不知道这个事务后续还有没有sql,啥时候会commit。 因此trx_query不能提供有意义的信息。

如果我们想看到这个事务执行过的SQL,看是否可以杀掉长事务,怎么办呢?我们可以联合其他系统表查询得到,具体查询SQL如下:

SELECT
    now(),
    (
        UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)
    ) diff_sec,
    b.id,
    b. USER,
    b. HOST,
    b.db,
    d.SQL_TEXT
FROM
    .innodb_trx a
INNER JOIN PERFORMANCE_SCHEMA .`PROCESSLIST` b ON a.TRX_MYSQL_THREAD_ID = b.id
AND b.command = 'Sleep'
INNER JOIN PERFORMANCE_SCHEMA .threads c ON b.id = c.PROCESSLIST_ID
INNER JOIN PERFORMANCE_SCHEMA .events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
| now()               | diff_sec | id | user | host      | db     | SQL_TEXT                                            |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
| 2019-09-16 14:06:26 |       54 | 17 | root | localhost | testdb | select * from stu_tb where stu_id = 1002 for update |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+


上述结果中diff_sec和上面idle_time表示意思相同,都是代表此事务持续的秒数。SQL_TEXT表示该事务刚执行的SQL。但是呢,上述语句只能查到事务最后执行的SQL,我们知道,一个事务里可能包含多个SQL,那我们想查询这个未提交的事务执行过哪些SQL,是否可以满足呢,答案是结合events_statements_history系统表也可以满足需求。下面语句将会查询出该事务执行过的所有SQL:

SELECT
    ps.id 'PROCESS ID',
    ps. USER,
    ps. HOST,
    esh.EVENT_ID,
    trx.trx_started,
    esh.event_name 'EVENT NAME',
    esh.sql_text 'SQL',
    ps.time
FROM
    PERFORMANCE_SCHEMA .events_statements_history esh
JOIN PERFORMANCE_SCHEMA .threads th ON esh.thread_id = th.thread_id
JOIN information_schema. PROCESSLIST ps ON ps.id = th.processlist_id
LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
WHERE
    trx.trx_id IS NOT NULL
AND ps. USER != 'SYSTEM_USER'
ORDER BY
    esh.EVENT_ID;
    
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| PROCESS ID | USER | HOST      | EVENT_ID | trx_started         | EVENT NAME                   | SQL                                                 | time |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
|         20 | root | localhost |        1 | 2019-09-16 14:18:44 | statement/sql/select         | select @@version_comment limit 1                    |   60 |
|         20 | root | localhost |        2 | 2019-09-16 14:18:44 | statement/sql/begin          | start transaction                                   |   60 |
|         20 | root | localhost |        3 | 2019-09-16 14:18:44 | statement/sql/select         | SELECT DATABASE()                                   |   60 |
|         20 | root | localhost |        4 | 2019-09-16 14:18:44 | statement/com/Init DB        | NULL                                                |   60 |
|         20 | root | localhost |        5 | 2019-09-16 14:18:44 | statement/sql/show_databases | show databases                                      |   60 |
|         20 | root | localhost |        6 | 2019-09-16 14:18:44 | statement/sql/show_tables    | show tables                                         |   60 |
|         20 | root | localhost |        7 | 2019-09-16 14:18:44 | statement/com/Field List     | NULL                                                |   60 |
|         20 | root | localhost |        8 | 2019-09-16 14:18:44 | statement/com/Field List     | NULL                                                |   60 |
|         20 | root | localhost |        9 | 2019-09-16 14:18:44 | statement/sql/select         | select * from stu_tb                                |   60 |
|         20 | root | localhost |       10 | 2019-09-16 14:18:44 | statement/sql/select         | select * from stu_tb where stu_id = 1002 for update |   60 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+


从上述结果中我们可以看到该事务从一开始到现在执行过的所有SQL,当我们把该事务相关信息都查询清楚后,我们就可以判定该事务是否可以杀掉,以免影响其他事务造成等待现象。

在这里稍微拓展下,长事务极易造成阻塞或者死锁现象,通常情况下我们可以首先查询 sys.innodb_lock_waits 视图确定有没有事务阻塞现象:

#假设一个事务执行 
select * from stu_tb where stu_id = 1002 for update
#另外一个事务执行 
update stu_tb set stu_name = 'wang' where stu_id = 1002

mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2019-09-16 14:34:32
                    wait_age: 00:00:03
               wait_age_secs: 3
                locked_table: `testdb`.`stu_tb`
                locked_index: uk_stu_id
                 locked_type: RECORD
              waiting_trx_id: 6178
         waiting_trx_started: 2019-09-16 14:34:32
             waiting_trx_age: 00:00:03
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 19
               waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1002
             waiting_lock_id: 6178:47:4:7
           waiting_lock_mode: X
             blocking_trx_id: 6177
                blocking_pid: 20
              blocking_query: NULL
            blocking_lock_id: 6177:47:4:7
          blocking_lock_mode: X
        blocking_trx_started: 2019-09-16 14:18:44
            blocking_trx_age: 00:15:51
    blocking_trx_rows_locked: 2
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 20sql_kill_blocking_connection: KILL 20


上述结果显示出被阻塞的SQL以及锁的类型,更强大的是杀掉会话的语句也给出来了。但是并没有找到阻塞会话执行的SQL,如果我们想找出更详细的信息,可以使用下面语句:

SELECT
    tmp.*, c.SQL_Text blocking_sql_text,
    p. HOST blocking_host
FROM
    (
        SELECT
            r.trx_state wating_trx_state,
            r.trx_id waiting_trx_id,
            r.trx_mysql_thread_Id waiting_thread,
            r.trx_query waiting_query,
            b.trx_state blocking_trx_state,
            b.trx_id blocking_trx_id,
            b.trx_mysql_thread_id blocking_thread,
            b.trx_query blocking_query
        FROM
            information_schema.innodb_lock_waits w
        INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
        INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
    ) tmp,
    information_schema. PROCESSLIST p,
    PERFORMANCE_SCHEMA .events_statements_current c,
    PERFORMANCE_SCHEMA .threads t
WHERE
    tmp.blocking_thread = p.id
AND t.thread_id = c.THREAD_ID
AND t.PROCESSLIST_ID = p.id

*************************** 1. row ***************************
  wating_trx_state: LOCK WAIT
    waiting_trx_id: 6180
    waiting_thread: 19
     waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006blocking_trx_state: RUNNING
   blocking_trx_id: 6177
   blocking_thread: 20
    blocking_query: NULL
 blocking_sql_text: select * from stu_tb where stu_id = 1006 for update
     blocking_host: localhost

上面结果显得更加清晰,我们可以清楚的看到阻塞端及被阻塞端事务执行的语句,有助于我们排查并确认是否可以杀掉阻塞的会话。

监控长事务

现实工作中我们需要监控下长事务,定义一个阈值,比如说30s 执行时间超过30s的事务即为长事务,要求记录并告警出来,提醒管理人员去处理。下面给出监控脚本,各位可以参考下,根据需求改动使用:

#!/bin/bash
# Describe:    monitor long transaction

/usr/local/mysql/bin/mysql -N -uroot -pxxxxxx -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT 
from information_schema.innodb_trx a inner join information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G H
do
    if [ "$C" -gt 30 ]
    then
    echo $(date +"%Y-%m-%d %H:%M:%S")
    echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H"
fi
done >> /tmp/longtransaction.txt


简单说明一下,这里的-gt 30是30秒钟的意思,只要超过了30秒钟就认定是长事务,可以根据实际需要自定义。将该脚本加入定时任务中即可执行。

总结

1、查询所有正在运行的事务及运行时间

SELECT
    t.*, to_seconds(now()) - to_seconds(t.trx_started) idle_time
FROM
    information_schema.innodb_trx t;

2、查询事务详细信息及执行的SQL

SELECT
    now(),
    (
        UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)
    ) diff_sec,
    b.id,
    b. USER,
    b. HOST,
    b.db,
    d.SQL_TEXT
FROM
    .innodb_trx a
INNER JOIN PERFORMANCE_SCHEMA .`PROCESSLIST` b ON a.TRX_MYSQL_THREAD_ID = b.id
AND b.command = 'Sleep'
INNER JOIN PERFORMANCE_SCHEMA .threads c ON b.id = c.PROCESSLIST_ID
INNER JOIN PERFORMANCE_SCHEMA .events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

3、查询事务执行过的所有历史SQL记录

SELECT
    ps.id 'PROCESS ID',
    ps. USER,
    ps. HOST,
    esh.EVENT_ID,
    trx.trx_started,
    esh.event_name 'EVENT NAME',
    esh.sql_text 'SQL',
    ps.time
FROM
    PERFORMANCE_SCHEMA .events_statements_history esh
JOIN PERFORMANCE_SCHEMA .threads th ON esh.thread_id = th.thread_id
JOIN information_schema. PROCESSLIST ps ON ps.id = th.processlist_id
LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
WHERE
    trx.trx_id IS NOT NULL
AND ps. USER != 'SYSTEM_USER'
ORDER BY
    esh.EVENT_ID;

4、简单查询事务锁


select * from sys.innodb_lock_waits


5、查询事务锁详细信息

SELECT
    tmp.*, c.SQL_Text blocking_sql_text,
    p. HOST blocking_host
FROM
    (
        SELECT
            r.trx_state wating_trx_state,
            r.trx_id waiting_trx_id,
            r.trx_mysql_thread_Id waiting_thread,
            r.trx_query waiting_query,
            b.trx_state blocking_trx_state,
            b.trx_id blocking_trx_id,
            b.trx_mysql_thread_id blocking_thread,
            b.trx_query blocking_query
        FROM
            information_schema.innodb_lock_waits w
        INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
        INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
    ) tmp,
    information_schema. PROCESSLIST p,
    PERFORMANCE_SCHEMA .events_statements_current c,
    PERFORMANCE_SCHEMA .threads t
WHERE
    tmp.blocking_thread = p.id
AND t.thread_id = c.THREAD_ID
AND t.PROCESSLIST_ID = p.id


版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中count(x)到底慢不慢

总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的...

mysql常见面试题第一讲

一、为什么用自增列作为主键1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键...

mysql中可重复读隔离级别的实现原理

原理MySQL默认的隔离级别是可重复读,即:事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。 那么MySQL可重复读是如何实现的呢?使用的的一...

mysql中QueryCache优化

原理 MySQL 的 Query Cache 实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的 Query 语句(当然仅限于 SELECT 类型的 Query)通过一定的 has...

mysql中数据页的相关概念

mysql中数据页的相关概念

概念在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(p...

mysql中eq_range_index_dive_limit参数学习

概念官方文档如下描述:This variable indicates the number of equality ranges in an equality comparison conditi...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。