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存储引擎特有的,用于保证事务的持久性。在事务提交时,会将修改后的数据页记录到重做日志中,即使发生系统崩溃,也可以通过重做日志来恢复数据。重做日志是循环使用的,当空间不足时,旧的日志会被覆盖。

 
 
 
posted @ 2025-03-17 15:33  侯蜀黍  阅读(50)  评论(0)    收藏  举报