MySQL 事务
1.事务的概念
事务是一组原子性的SQL查询,或者说是一个独立的工作单元,事务内的语句.要么全部执行成功,要么全部执行失败,确保数据库从一个一致状态转换到另一个一致状态。事务是保证数据完整性和并发操作正确性的核心机制,事务是在存储引擎实现的
2.查看存储引擎是否支持事务
SHOW ENGINES;
Engine: 表示存储引擎的名称
Support: 表示服务器对存储引擎的支持情况,YES表示支持,NO表示不支持,DEFAULT表示数据库默认使用的存储引擎,DISABLED表示支持引擎但已将其禁用
Comment: 对存储引擎简单的描述
Transactions: 表示存储引擎是否支持事务,可以看到InnoDB存储引擎支持事务,而MyISAM存储引擎不支持事务
XA: 表示存储引擎是否支持XA事务
Savepoints: 表示存储引擎是否支持保存点
3.事务的使用方法
3.1 事务基本命令:
开启事务: 方式一 BFGIN; 方式二 START TRANSACTION;
提交事务 COMMIT; 回滚事务 ROLLBACK;
3.2事务的提交
事务分为手动提交跟自动提交;
查看是否为自动提交
--方法一 select @@autocommit; --方法二 SHOW VARIABLES LIKE 'autocommit';
1或者ON表示启用自动提交;0或者OFF表示禁用自动提交;当AUTOCOMMIT=0时,所有查询都是在一个事务中,直到显示的执行提交语句COMMIT或者回滚语句ROLLBACK
还有一些命令在执行之前会强制执行COMMIT提交当前的活动事务,例如ALTER TABLE、LOCK TABLES等命令,更多此类命令请查看对应版本的官方文档
3.3 自动提交
autocommit 为1或者ON表示启用自动提交;与开启事务命令一并提交的SQL语句会执行,并自动提交事务
例如; name最后结果为zhao6
begin;update users set name='zhao' where id = 1;update users set name='zhao6' where id =1;
3.3 手动提交
START TRANSACTION;-- 开启事务 UPDATE users SET name='手动提交' WHERE id=1;-- SQL语句 COMMIT;-- 提交事务
4.查看事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- INNODB_TRX表用于实时监控 InnoDB 存储引擎中当前所有活跃事务的详细信息,开启事务,并且执行一条语句,表中会有信息trx_id:事务的ID。这是一个唯一标识符,用于识别事务。
详细字段含义点击查看
5.事务的ACID特性
●原子性(atomicity):一个事务被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,不可能只执行其中的一部分操作
●一致性(consistency):数据库总是从一个一致性的状态转换到另一个一致性的状态
●隔离性(isolation):一个事务所做的修改再最终提交前,对其他事务是不可见的
●持久性(durability):一但事务提交,则其所做的修改就会永久的保存到数据库中
一个实现的ACID的数据库比没有实现ACID的数据库,通常更需要更强的CPU处理能力、更大的内存和更多的磁盘空间
6.事务的隔离级别
四种隔离级别
(1)READ_UNCOMMITTED(未提交读)
这是事务最低的隔离级别,它充许别外一个事务可以看到这个事务未提交的数据。
会出现脏读、不可重复读、幻读 (隔离级别最低,并发性能高)。
(2)READ_COMMITTED(读提交)
保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。
可以避免脏读,但会出现不可重复读、幻读问题(锁定正在读取的行)。
(3)REPEATABLE_READ(可重复读)
是 MySQL InnoDB 引擎的默认事务隔离级别。其核心目标是确保在同一事务中多次读取同一数据时,结果保持一致,避免以下问题:
可以防止脏读、不可重复读,但会出幻读(锁定所读取的所有行)。
(4)SERIALIZABLE(可串行化)
是 MySQL 事务隔离级别中的最高级别,通过强制事务完全串行执行(事务被处理为顺序执行),确保所有并发操作互不干扰,彻底解决以下问题
保证所有的情况不会发生(锁表)。
问题释义:
脏读:一个事务读取了另一个事务 未提交的修改。若后者回滚,前者读到的数据是无效的
不可重复读: 同一事务内多次读取同一数据,结果不一致。因其他事务 修改并提交 了该数据
幻读:同一事务内多次查询同一范围的数据,结果集的行数不同。因其他事务 插入或删除 了符合该范围的数据。默认隔离级别为 REPEATABLE READ
,通过多版本并发控制(MVCC)和锁机制保障一致性
真正的幻读问题在可重复读级别通常通过MVCC加上间隙锁的策略来解决。幻读的严格定义在SQL标准中仍可能发生,但实际场景中较少
查看隔离级别
-- 查看当前会话隔离级别 SELECT @@transaction_isolation; -- MySQL 8.0+ SELECT @@tx_isolation; -- 旧版本 -- 查看全局隔离级别 SELECT @@global.transaction_isolation; -- MySQL 8.0+ SELECT @@global.tx_isolation; -- 旧版本
设置隔离级别
临时修改会话级别
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
例如 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 新的隔离级别会在下一个事务开始的时候生效;
永久修改全局级别(需重启服务生效)
[mysqld] transaction-isolation = REPEATABLE READ
7.死锁
7.1 定义:死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源从而导致恶性循环的现象.
7.2 分类:
>多个事务试图以不同的顺序锁定资源,导致死锁
>多个事务同时锁定同一个资源,导致死锁
>在范围查询或插入操作中,多个事务争用同一间隙锁,导致死锁
7.3 查看死锁:
SHOW ENGINE INNODB STATUS\G -- 在输出中查找 "LATEST DETECTED DEADLOCK"
7.4 解决策略
●自动处理:
InnoDB 内置死锁检测机制,检测到死锁时会自动回滚其中一个事务(通常选择回滚代价最小的事务)。
●手动干预:
锁等待超时:设置 SET GLOBAL innodb_lock_wait_timeout = 30; -- 超时时间设为30秒,默认50秒 ,超时后事务自动回滚。永久设置请修改配置文件innodb_lock_wait_timeout
查看锁等待时间
select @@global.innodb_lock_wait_timeout; 查看当前会话配置时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; 查看配置文件中的配置时间
强制终止事务:通过 KILL [事务线程ID]
终止阻塞事务,需结合 SHOW PROCESSLIST
定位线程。
统一加锁顺序:确保事务按固定顺序访问资源(如按主键排序后加锁)。
缩短事务时间:减少事务内的操作量,避免长事务占用锁资源。
批量操作:使用 SELECT ... FOR UPDATE
批量锁定所有需修改的记录,避免逐条加锁。
降低隔离级别至 READ COMMITTED
,减少间隙锁的使用,降低死锁概率(需权衡一致性)。
7.5 预防措施
●索引优化: 为高频查询字段添加索引,减少全表扫描和锁冲突。
●锁粒度控制: 优先使用行级锁,避免表级锁对并发性能的影响;仅在必要时使用 LOCK TABLES
。
●超时与重试机制: 应用程序捕获死锁错误(如 ER_LOCK_DEADLOCK
),自动重试事务。
●监控与告警: 使用 Percona Toolkit、MySQL Enterprise Monitor 等工具实时监控死锁频率和影响范围
8 事务相关问题
混合使用存储引擎 : 如果在事务中混合使用事务型和非事务表,如果该事务需要回滚,非事务型的表上的变更就无法撤销;在非事务型的表上执行事务相关操作的时候,多数情况MySQL并不会提醒,有时会在回滚时提醒
9事务日志
事务日志的实现主要通过两种方式:
二进制日志(Binary Log)
二进制日志主要用于复制和数据恢复。它记录了所有修改数据库数据的 SQL 语句(如 INSERT、UPDATE、DELETE 等),但不包括 SELECT 和 SHOW 这类不修改数据的操作。二进制日志是 MySQL 复制的核心,,同时也用于灾难恢复。
重做日志(Redo Log,也称为 InnoDB 日志)
是InnoDB存储引擎特有的,用于保证事务的持久性。在事务提交时,会将修改后的数据页记录到重做日志中,即使发生系统崩溃,也可以通过重做日志来恢复数据。重做日志是循环使用的,当空间不足时,旧的日志会被覆盖。