1. 前言
使用MySQL数据库时,使用事务与不使用事务相比,出现问题时排查更复杂。
不使用事务时,客户端只需要请求MySQL服务一次(只考虑显式执行的SQL语句);使用事务时,客户端至少需要请求MySQL服务四次(开启事务、执行SQL语句、提交/回滚事务、恢复自动提交)。
在Java中存在一些用法会导致事务失效,有的问题比较明显可以较快定位,有的问题隐藏较深可能需要较长时间排查。
因此需要对MySQL的事务执行原理进行分析,并整理用于排查事务相关问题的快速有效的方法。
可以根据MySQL客户端访问MySQL服务时的连接ID(MySQL服务线程ID),或MySQL客户端源端口,来判断事务执行情况。
在Java应用中访问MySQL服务时,涉及Java应用、网络传输、MySQL服务这三层,在每一层都可以对执行的SQL语句与事务操作进行监控与观测,涉及的内容如下图所示:
对于MySQL服务,可以根据一般查询日志中的连接ID(MySQL服务线程ID)判断事务是否生效。
以下首先对MySQL事务执行原理进行分析,作为排查MySQL事务相关问题的基础,最后说明在MySQL服务中对事务相关问题进行排查的方法。
可参考以下相关内容:
内容 | 链接 |
---|---|
MySQL SQL语句与事务执行及日志分析 | https://blog.csdn.net/a82514921/article/details/126563449 |
tcpdump、Wireshark抓包分析MySQL SQL语句与事务执行 | https://blog.csdn.net/a82514921/article/details/126563471 |
Spring、MyBatis、Druid、MySQL不使用事务执行SQL语句分析 | https://blog.csdn.net/a82514921/article/details/126563515 |
Spring、MyBatis、Druid、MySQL使用事务执行SQL语句分析 | https://blog.csdn.net/a82514921/article/details/126563542 |
Spring、MyBatis、Druid、MySQL执行SQL语句与事务监控 | https://blog.csdn.net/a82514921/article/details/126563558 |
数据源使用错误导致MySQL事务失效分析 | https://blog.csdn.net/a82514921/article/details/126563573 |
TiDB乐观事务、悲观事务模型验证 | https://blog.csdn.net/a82514921/article/details/126563502 |
2. MySQL执行SQL语句及事务相关的概念
2.1. MySQL服务
在MySQL服务中,涉及以下事务相关的概念。
2.1.1. MySQL线程
参考“Connection Interfaces”https://dev.mysql.com/doc/refman/5.6/en/connection-interfaces.html#。
在MySQL服务中,由连接管理线程处理客户端连接请求,每个客户端连接都会关联到一个MySQL服务线程
,每个客户端提交的SQL语句都在对应的MySQL服务线程中执行,当前连接的MySQL客户端数量与MySQL服务线程数一致。
参考https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_connection-id。
CONNECTION_ID()函数返回当前连接的ID,即线程ID
。每个连接都有一个在当前连接的客户端集合中唯一的ID。
CONNECTION_ID()函数返回值与INFORMATION_SCHEMA.PROCESSLIST表中的ID列相同,与SHOW PROCESSLIST输出的Id列相同,以上显示的是MySQL的进程列表,即MySQL服务中的线程当前执行的操作。可参考“The INFORMATION_SCHEMA PROCESSLIST Table”https://dev.mysql.com/doc/refman/5.6/en/information-schema-processlist-table.html,“SHOW PROCESSLIST Statement”https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html。
2.1.2. MySQL连接传输协议
参考“Connection Transport Protocols”https://dev.mysql.com/doc/refman/5.6/en/transport-protocols.html。
MySQL支持连接传输协议包括:TCP/IP、Unix套接字文件、命名管道、共享内存等,在连接远程的MySQL服务时,使用的传输协议为TCP/IP
(其他协议只支持连接本地的MySQL服务,且只支持一类操作系统)。
2.1.3. MySQL事务控制
参考“START TRANSACTION, COMMIT, and ROLLBACK Statements”https://dev.mysql.com/doc/refman/5.6/en/commit.html。
MySQL中可对事务进行控制的语句包括:
-
START TRANSACTION
开始一个新的事务(也可以使用别名BEGIN
); -
COMMIT
提交当前事务,使修改保持生效; -
ROLLBACK
回滚当前事务,使修改取消; -
SET autocommit
禁用或启用当前会话的自动提交模式。
默认情况下,MySQL的自动提交模式是启用的,即不使用事务时,每条SQL语句是原子的,执行后就会生效
,就好像SQL语句被包含在START TRANSACTION与COMMIT中执行一样。不使用事务时,无法使用ROLLBACK撤销SQL语句的执行结果。当SQL语句执行过程中出现异常时,才会被回滚。
使用START TRANSACTION可以隐示地禁用自动提交,执行START TRANSACTION后,在执行COMMIT或ROLLBACK结束事务之前,自动提交都会保持禁用;结束事务之后,自动提交模式会恢复为之前的状态。
使用SET autocommit=0;
语句可以显式地禁用自动提交,autocommit是一个会话变量,必须对每个会话进行设置。
参考“autocommit, Commit, and Rollback”https://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html。
在InnoDB存储引擎中,所有的用户行为都在一个事务中发生,假如启用了自动提交模式,则每条SQL语句都会形成一个独立的事务。
2.1.4. MySQL事务与线程/连接
参考“Data Layout”https://dev.mysql.com/doc/internals/en/transactions-data-layout.html,“Transaction Life Cycle”https://dev.mysql.com/doc/internals/en/transactions-life-cycle.html。
在MySQL服务中,thd代表线程结构,MySQL服务将事务相关的数据保存在thd->transaction结构中。
当新的连接建立时,thd->transaction中的成员变量会初始化为空状态。假如SQL语句中使用了某个数据库表,则相关的存储引擎都会记录下来。
在SQL语句执行结束时,MySQL服务调用所有相关存储引擎的提交或回滚;当提交或回滚结束后,以上信息会被清空。
参考“THD Class Reference”https://dev.mysql.com/doc/dev/mysql-server/latest/classTHD.html。
对于每个客户端连接,MySQL服务创建一个单独的线程,使用THD类作为线程/连接的描述符。
2.1.5. MySQL事务与存储引擎
参考“Transaction Overview”https://dev.mysql.com/doc/internals/en/transaction-overview.html。
MySQL服务需要调用存储引擎的start_stmt()或external_lock(),以使存储引擎开始事务。
存储引擎在每个连接的内存中保存了事务信息,也在MySQL服务中注册了事务信息,以使MySQL服务随后能够发起COMMIT或ROLLBACK操作。
在事务需要结束时,MySQL服务会调用存储引擎的commit()或rollback()方法。
2.1.6. MySQL事务隐式提交
参考“Transaction Life Cycle”https://dev.mysql.com/doc/internals/en/transactions-life-cycle.html。
在以下情况下,事务会被提交:
-
当用户执行COMMIT语句时;
-
MySQL事务隐式提交,当MySQL服务开始处理DDL或SET AUTOCOMMIT={0|1}语句时。
关于会导致事务隐式提交的SQL语句,在“Statements That Cause an Implicit Commit”https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html中有详细说明,包括DDL等。
2.1.7. MySQL事务隐式回滚
除了在MySQL客户端执行ROLLBACK语句进行显式回滚外,以下情况下MySQL服务也会进行隐式回滚。
2.1.7.1. 连接断开与事务回滚
参考“LOCK TABLES and UNLOCK TABLES Statements”https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html。
当一个客户端会话的连接断开时,假如存在活动的事务,则MySQL服务会将事务回滚。
2.1.7.2. 连接超时与事务回滚
参考https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout。
MySQL系统变量wait_timeout用于设置MySQL服务在关闭非交互式连接之前等待其活动的时间,单位为秒。默认值为28800,即8小时。可能需要同时修改全局系统变量wait_timeout与interactive_timeout,才能使会话系统wait_timeout的修改生效。
即MySQL连接不活动超过该时间后,MySQL服务会将该连接断开,对应的事务也会被回滚。
2.1.7.3. 行锁超时与事务回滚
参考“InnoDB Error Handling”https://dev.mysql.com/doc/refman/5.6/en/innodb-error-handling.html,https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout。
InnoDB在等待获取行锁时,假如超过了一定的时间,则会进行回滚。
系统变量innodb_lock_wait_timeout用于设置以上获取行锁超时时间,单位为秒,默认为50。
当系统变量innodb_rollback_on_timeout为假时,InnoDB会将当前语句(即等待行锁并导致超时的语句)进行回滚(此时整个事务还是活动状态);当该系统变量为真时,InnoDB会将整个事务进行回滚。
该变量值默认值为假,即默认情况下,事务中执行的语句获取行锁超时,对应语句会被InnoDB回滚。
2.1.7.4. 死锁与事务回滚
参考“Deadlock Detection”https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html,“InnoDB Error Handling”https://dev.mysql.com/doc/refman/5.6/en/innodb-error-handling.html。
InnoDB会自动检测事务死锁,当出现死锁时,会将一个或多个事务回滚以打破死锁。InnoDB尝试将“小”的事务回滚,事务的大小由插入、更新或删除的行数决定。
2.1.7.5. InnoDB其他错误与事务回滚
参考“InnoDB Error Handling”https://dev.mysql.com/doc/refman/5.6/en/innodb-error-handling.html。
假如在SQL语句中没有指定IGNORE,则出现重复键错误时,InnoDB会将对应的SQL语句回滚;
出现行太长错误时,InnoDB会将对应的SQL语句回滚;
其他错误大多由InnoDB存储引擎层之上的MySQL服务层检测,并将对应的SQL语句回滚。
2.2. MySQL客户端
在MySQL客户端中,涉及以下事务相关的概念。
2.2.1. MySQL客户端连接池
参考“Chapter 8 Connection Pooling with Connector/J”https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html。
MySQL客户端连接驱动支持连接池,当一个客户端线程需要请求MySQL服务器时,客户端线程从连接池中请求一个连接;当连接使用完毕时,会返回到连接池中,可再被其他线程使用。
当一个线程从连接池中借出连接后,到归还至连接池这段时间,该连接由该线程独占使用。
2.2.2. MySQL客户端事务状态维护
参考“Performance Extensions”https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-performance-extensions.html。
MySQL JDBC连接参数中包含useLocalSessionState与useLocalTransactionState,默认值均为false,即默认情况下,MySQL Connector会向MySQL服务查询会话的自动提交、事务隔离级别等参数,以确定在调用commit()或rollback()方法时,是否需要将其发送到MySQL服务器。
3. MySQL事务分析方法总结
3.1. MySQL事务执行方式
在事务的执行过程中,Java应用(MySQL客户端)、MySQL服务、存储引擎,这三者需要共同参与,都需要知道当前是否使用了事务,以及事务的当前状态。
自动提交是MySQL的会话参数,默认为是,即MySQL执行SQL语句时,默认会自动提交。
MySQL在事务中执行SQL时,包含三个步骤:
-
执行START TRANSACTION或SET autocommit=0等语句,以开启事务;
-
执行需要在事务中执行的SQL语句;
-
执行COMMIT以提交事务;或执行ROLLBACK以回滚事务。
事务的信息保存在线程中,当事务提交或回滚后,线程中的事务相关信息会被清空。因此事务的以上步骤需要在一个线程/连接中执行,才能使MySQL服务正确调用存储引擎的提交或回滚处理,使事务能够生效
。
3.2. MySQL事务执行与TCP连接
MySQL事务在执行时,需要MySQL客户端连接上MySQL服务器,客户端与服务器都知道当前事务的存在,此时在客户端与服务器都存在一个对应的连接,连接在事务执行期间是独占的;在服务器中还存在一个对应的线程,线程也是事务执行期间独占的。
连接ID与线程ID是相同的,因此可以认为,在某个时间点,连接ID(线程ID)可以唯一确定一个事务
。
MySQL客户端与服务器通信使用TCP/IP协议(大部分使用场景下),根据RFC793,“TRANSMISSION CONTROL PROTOCOL”https://datatracker.ietf.org/doc/html/rfc793,在TCP连接中,IP地址加端口形成的套接字在连接中是唯一的。
MySQL事务在执行时,客户端IP、服务器IP、服务器端口是固定的,因此可以认为,在某个时间点,客户端端口可以唯一确定一个连接,也就是可以唯一确定一个事务
。
以上内容可参考文本开头的图示。
3.3. 可行的MySQL事务分析方法
根据以上分析可以推导出以下可行的MySQL事务分析方法:
- 根据连接ID(线程ID)判断
在指定的时间点,假如MySQL客户端向MySQL服务发送的SQL语句中,开始事务、执行SQL语句、提交或回滚事务,这三类SQL语句使用的连接ID(线程ID)相同
,则这个事务是生效
的;
在指定的时间点,假如MySQL客户端向MySQL服务发送的SQL语句中,开始事务、执行SQL语句、提交或回滚事务,这三类SQL语句使用的连接ID(线程ID)不同
,则这个事务是失效
的。
- 根据客户端端口判断
在指定的时间点,假如MySQL客户端向MySQL服务发送的SQL语句中,开始事务、执行SQL语句、提交或回滚事务,这三类SQL语句使用的客户端端口相同
,则这个事务是生效
的;
在指定的时间点,假如MySQL客户端向MySQL服务发送的SQL语句中,开始事务、执行SQL语句、提交或回滚事务,这三类SQL语句使用的客户端端口不同
,则这个事务是失效
的。
4. MySQL日志
MySQL有完备的日志体系,需要从中找到可以用于对事务执行情况进行监控与观测的日志。
根据MySQL日志对事务执行情况进行监控时,需要能够访问MySQL服务器,且需要有数据库ROOT用户权限,因此这种方法在使用时会受到限制。
4.1. 二进制日志
参考“The Binary Log”https://dev.mysql.com/doc/refman/5.6/en/binary-log.html。
MySQL二进制日志中包含了描述数据库变化的事件,例如表创建,或表数据变化等。
MySQL二进制日志不包含未对数据进行修改的语句,例如SELECT、SHOW等。
可以使用mysqlbinlog命令将MySQL二进制日志展示为可以人工查看的文本格式,可参考“mysqlbinlog — Utility for Processing Binary Log Files”https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html。
例如使用以下命令:
mysqlbinlog -v 二进制日志文件名
MySQL二进制日志不包含SELECT FOR UPDATE语句,且查看结果时操作比较复杂,需要使用mysqlbinlog命令进行解析才能看到SQL执行记录,二进制日志不适合用于监控事务执行情况。
4.2. 慢查询日志
参考“The Slow Query Log”https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html。
将MySQL系统变量slow_query_log设置为1可以开启MySQL慢查询日志,将系统变量long_query_time设置为0可以使MySQL将每条执行的SQL语句都写入慢查询日志。
慢查询日志虽然可以记录每个执行的SQL语句,但不会显示SQL语句执行时对应的事务信息,无法监控事务执行情况。
4.3. 错误日志中的InnoDB锁监控信息
参考“Enabling InnoDB Monitors”https://dev.mysql.com/doc/refman/5.6/en/innodb-enabling-monitors.html
将MySQL系统变量innodb_status_output、innodb_status_output_locks设置为ON,可以开启InnoDB锁监控。
InnoDB将监控输出到标准错误stderr,在Windows,Unix或类Unix系统中,都可以使用MySQL系统变量log_error,指定stderr写入哪个错误日志文件,可参考“The Error Log”https://dev.mysql.com/doc/refman/5.6/en/error-log.html、https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_log_error
InnoDB锁监控信息写入错误日志后,会显示事务ID,但不一定会显示事务ID对应的SQL语句,无法监控事务执行情况。
4.4. 一般查询日志
参考“The General Query Log”https://dev.mysql.com/doc/refman/5.6/en/query-log.html。
MySQL服务将从客户端接收到的每条SQL语句记录到一般查询日志中,包含了二进制文件中不会记录的查询语句。
默认情况下,MySQL的一般查询日志是关闭的。
当开启一般查询日志时,假如没有指定一般查询日志文件名,则默认文件名为“主机名.log”,默认保存在MySQL的data目录中。
4.4.1. 一般查询日志开启方式
参考“Selecting General Query Log and Slow Query Log Output Destinations”https://dev.mysql.com/doc/refman/5.6/en/log-destinations.html、https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html。
通过MySQL系统变量可对一般查询日志的开关、文件名、输出方式等进行设置。
- general_log
设置为1/ON时,可以开启一般查询日志,默认值为0/OFF,即关闭一般查询日志。
- general_log_file
用于设置一般查询日志文件名,默认值为“主机名.log”。
- log_output
用于设置一般查询日志与慢查询日志的输出方式,值为TABLE、FILE、NONE,分别代表输出到数据库表,输出到文件,禁用一般查询日志,默认值为FILE。
也可为以上值使用半角逗号分隔的组合,例如“TABLE,FILE”代表同时输出到数据库表及文件。
以上系统变量可通过SQL语句进行设置,例如:
SET @@GLOBAL.general_log = 'ON';
SET @@GLOBAL.general_log_file = 'general.log';
SET @@GLOBAL.log_output = 'FILE';
也可在MySQL启动命令中配置,例如:
--general_log=ON --general_log_file=general.log --log_output=FILE
也可在MySQL启动命令中指定的配置文件中,在“[mysqld]”节点中配置,例如:
general_log = ON
general_log_file = general.log
log_output = FILE
4.4.2. 一般查询日志内容示例
打开两个MySQL客户端,连接同一个MySQL数据库的同一个用户,依次执行以下SQL语句,观察一般查询日志中的结果:
会话序号 | 执行的SQL语句 |
---|---|
会话 1 | start transaction; |
会话 1 | update task_lock set end_time=now(); |
会话 2 | start transaction; |
会话 2 | update task_lock set end_time=now(); |
会话 1 | commit; |
会话 2 | commit; |
执行“select CONNECTION_ID()”语句,查看当前连接的ID,与一般查询日志文件/数据库表中的Id/thread_id值相同,即一般查询日志的内容中包含了当前连接/线程ID;根据前文可知,连接/线程在某个时间点唯一对应某个事务,根据连接/线程ID可以知道SQL语句在哪个事务中执行,即一般查询日志可用于监控MySQL事务执行情况
。
4.4.2.1. 一般查询日志文件方式示例
一般查询日志输出到文件时,示例如下,可以看到一般查询日志中显示的SQL语句与实际执行的顺序一致,且有显示对应的连接/线程ID,即可知道执行SQL语句时是否在同一个事务中:
Time Id Command Argument
220628 21:40:09 6 Connect test@localhost as anonymous on testdb
6 Query select @@version_comment limit 1
220628 21:40:11 7 Connect test@localhost as anonymous on testdb
7 Query select @@version_comment limit 1
220628 21:40:20 6 Query start transaction
6 Query update task_lock set end_time=now()
220628 21:40:22 7 Query start transaction
7 Query update task_lock set end_time=now()
220628 21:40:29 6 Query commit
220628 21:40:30 7 Query commit
4.4.2.2. 一般查询日志数据库表方式示例
一般查询日志输出到数据库表时,示例如下,输出内容与文件方式类似,展示效果比文件方式更好:
event_time user_host thread_id server_id command_type argument
2022/6/28 21:40:09 test[test] @ localhost [127.0.0.1] 6 1 Query select @@version_comment limit 1
2022/6/28 21:40:11 test[test] @ localhost [127.0.0.1] 7 1 Query select @@version_comment limit 1
2022/6/28 21:40:20 test[test] @ localhost [127.0.0.1] 6 1 Query start transaction
2022/6/28 21:40:20 test[test] @ localhost [127.0.0.1] 6 1 Query update task_lock set end_time=now()
2022/6/28 21:40:22 test[test] @ localhost [127.0.0.1] 7 1 Query start transaction
2022/6/28 21:40:22 test[test] @ localhost [127.0.0.1] 7 1 Query update task_lock set end_time=now()
2022/6/28 21:40:29 test[test] @ localhost [127.0.0.1] 6 1 Query commit
2022/6/28 21:40:30 test[test] @ localhost [127.0.0.1] 7 1 Query commit
一般查询日志输出到数据库表的表名为mysql.general_log
,可进行查询操作,需要ROOT用户才有查询权限
;
可以执行TRUNCATE TABLE mysql.general_log
语句,将以上表的内容清空,但不支持INSERT、DELETE、UPDATE等操作。