MySQL的事务及隔离级别

本文深入探讨SQL事务处理及四种隔离级别的应用,包括原子性、一致性、隔离性和持久性的特性,以及如何通过保存点控制回滚范围。同时,演示了不同隔离级别下可能遇到的脏读、不可重复读和幻读问题及其解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

操作中需要准备的数据库和表(注:SQL语句中标点符号统一为英文状态下):
#选择shop数据库
use shop;
#创建sh_user表
create table sh_user(
id int unsigned primary key auto_increment comment ‘用户id’,
name varchar(100) not null default ’ ’ comment’用户名’,
password varchar(255) not null default ’ ’ comment’密码’,
salt char(32) not null default ’ ’ comment ‘密码盐’,
email varchar(128) not null default ’ ’ comment ‘邮箱’,
mobile char(11) not null default ’ ’ comment ‘手机号’,
level tinyint unsigned not null default 0 comment ‘用户级别’,
money decimal(10,2) unsigned not null default 0 comment ‘金额’,
gender tinyint unsigned not null default 0 comment ‘性别’,
qq varchar(20) not null default ’ ’ comment ‘QQ’,
is_active tinyint unsigned not null default 0 comment ‘是否激活’,
reg_time datetime default null comment ‘注册时间’,
create_time datetime not null default current_timestamp comment ‘创建时间’,
update_time datetime default null comment ‘更新时间’);
#添加数据
insert into sh_user (id,name,password,salt,money,is_active)values
(1,‘Alex’,MD5(CONCAT(MD5(‘123’),‘salt1’)),‘salt1’,1000,1),
(2,‘Bill’,MD5(CONCAT(MD5(‘123’),‘salt2’)),‘salt2’,1000,1);

一、事务处理
  事务处理可以保证在同一个事务中的操作具有同步性。
1.事务的概念
  在MySQL中,事务就是针对数据库的一组操它可以由一条或多条SQL语句组成,且每个SQL语句都是相互依赖的。只要在程序执行过程中有一条SQL语句执行失败或发生错误,则其他语句都不会执行。
  MySQL中的事务必须满足A、C、I、D这四个基本特性:
(1)原子性(Atomicity)。指一个事务必须被视为一个不可分割的最小工作单元,只有事务中所有的数据库操作都执行成功,才算整个事务执行成功。
(2)一致性(Consistency)。指在事务处理时,无论执行成功还是失败,都要保证数据库系统处于一致的状态,保证数据库系统不会返回到一个未处理的事务中。MySQL中的一致性主要是由日志机制实现,通过日志记录数据库的所有变化,为事务恢复提供了跟踪记录。
(3)隔离性(Isolation)。指当一个事务在执行时,不会受到其他事务的影响。
(4)持久性(Durability)。指事务一旦提交,其对数据库的修改就是永久性的。但事务的持久性不能做到百分百的持久,只能从事务本身的角度来保证永久性,而一些外部原因导致数据库发生故障,如硬盘损坏,那么所有提交的数据可能都会丢失。

2.事务的基本操作
开启事务:start transaction;
提交事务:commit;
取消事务(回滚):rollback;
rollback只能对未提交的事务进行回已提交的事务无法回滚。当执行commit或rollback后,当前的事务就会自动结束。

#查看用户数据
select name,money from sh_user;
#开启事务
start transaction;
#Alex减少100元
update sh_user set money=money-100 where name=‘Alex’;
#Bill增加100元
update sh_user set money=money+100 where name=‘Bill’;
#提交事务
commit;
#查询金额
select name,money from sh_user;

接下来测试事务的回滚:
start transaction;
#将Bill的金额扣除100元
update sh_user set money=money-100 where name=‘Bill’;
#查询Bill的金额
select name,money from sh_user where name=‘Bill’;
#回滚事务
rollback;
#查看Bill的金额
select name,money from sh_user where name =‘Bill’;
从查询结果可以看出,Bill的金额又恢复成1100元,说明事务回滚成功。

事务的自动提交
  MySQL默认是自动提交模式,如果没有显示开启事务(strat transaction),每一条SQL语句都会自动提交(commit)。若用户想要控制事务的自动提交方式,可以通过更改autocommit变量来实现,将其值设为1表示开启自动提交,设为0表示关闭自动提交。
#查看当前会话的autocommit值
select @@autocommit;

3.事务的保存点
  在回滚事务时,事务内所有的事务都将撤销。而若希望只撤销一部分,可以用保存点来实现。
  一个事务中可以创建多个保存点,在提交事务后,事务中的保存点就会被删除。另外,在回滚到某个保存点之后创建过的保存点也会消失。
#查询Alex的金额,结果为900
select name,money from sh_user where name=‘Alex’;
#开启事务
start transaction;
#Alex扣除100元
update sh_user set money=money-100 where name=‘Alex’;
#创建保存点s1
savepoint s1;
#Alex再扣除50元
update sh_user set money=money-50 where name=‘Alex’;
#回滚到保存点s1
rollback to savepoint s1;
#查询Alex的金额,结果为800
select name,money from sh_user where name=‘Alex’;
#再次回滚事务
rollback;
#查看Alex金额,结果为900
select name,money from sh_user where name=‘Alex’;

二、事务隔离级别
1.查看隔离级别
#查看全局隔离级
select @@global.transaction_isolation;
#查看当前会话中的隔离级
select @@session.transaction_isolation;
#查看下一个事务的隔离级
select @@transaction_isolation;
  全局的隔离级影响的是所有连接MySQL的用户,而当前会话的隔离级别只影响当前正在登录MySQL服务器的用户,不会影响其他用户。而下一个事务的隔离级别仅对当前用户的下一个事务操作有影响。
  在默认情况下,上述3种方式的返回结果都是repeatable-read,表示隔离级别为可重复读。

2.修改隔离级别
#修改事务隔离级别
set session transaction isolation level read uncommitted;
#查看是否修改成功
select @@session.transaction_isolation;

#修改事务隔离级别
set session transaction isolation level repeatable read;
#查看是否修改成功
select @@session.transaction_isolation;

  默认情况下,事务的访问模式为read write(读/写模式),表示事务可以执行读(查询)或写(更改、插入、删除等)操作。若开发需要,可以将事务的访问模式设置为readonly(只读模式),禁止对表进行修改。

3.MySQL中的4中隔离级别
(1)READ UNCOMMITTED(读取未提交)
  READ UNCOMMITTED是事务中最低的级别,在该级别下的事务可以读取到其他事务中未提交的数据,这种读取方式也被称为脏读(Dirty Read)。脏读是指一个事务读取了另外一个事务未提交的数据。
  开启两个命令行窗口,分别登陆MySQL数据库,执行use shop切换到shop数据库。然后使用这两个窗口分别模拟Alex和Bill,以下称为客户端A和客户端B。
#客户端B,MySQL默认隔离级别为repeatable read可以避免脏读,将级别设置为较低的read uncommitted
set session transaction isolation level read uncommitted;
#客户端B,查询Bill当前的金额为1100
select name,money from sh_user where name=‘Bill’;
#客户端A,开启事务,并执行转账操作
start transaction;
update sh_user set money=money-100 where name=‘Alex’;
update sh_user set money=money+100 where name=‘Bill’;
#客户端B查询金额,结果为1200,客户端A未提交,但金额已增加
select name,money from sh_user where name=‘Bill’;
#客户端B。避免客户端脏读,将客户端B的事务隔离级别设为READ COMMITTED(或更高级别)可以避免脏读
set session transaction isolation level read committed;
#客户端B,查询Bill金额,此时为1100
select name,money from sh_user where name=‘Bill’;
#客户端A,回滚
rollback;

(2)READ COMMITTED(读取提交)
  READ COMMITTED是大多数DBMS(如SQL Sever、Oracle)的默认隔离级别,但不包括MySQL。在该隔离级别下只能读取其他事务已经提交的数据,避免了脏读数据的现象。但是在该隔离级别下,会出现不可重复读(NON-REPEATABLE READ)的问题。
  不可重复读是指在一个事务中多次查询的结果不一致,原因是查询的过程中数据发生了改变。
  假设客户端A是Alex用户,客户端B是网站后台,演示客户端B的不可重复读。当客户端B的事务隔离级别为READ COMMITTED时,会出现不可重复读的情况。在客户端B中开启事务,查询Aliex的金额,然后在客户端A中将Alex的金额扣除100元,最后在客户端B再次查询Alex的金额。
#客户端B
set session transaction isolation level read committed;
start transaction;
select name,money from sh_user where name =‘Alex’;
#客户端A
update sh_user set money=money-100 where name=‘Alex’;
#客户端B
select name,money from sh_user where name =‘Alex’;
commit;
  从上述结果可以看出,客户端B在同一个事务中两次查询结果中的结果不一样,这就是不可重复读的情况。
  避免网站后台的不可重复读。将客户端B的事务隔离级别设为默认级别REPEATABLE READ,可以避免不可重复读的情况。在该级别下按照上一步的方式重新测试。
#客户端B
set session transaction isolation level repeatable read;
start transaction;
select name,money from sh_user where name=‘Alex’;
#客户端A
update sh_user set money=money+100 where name=‘Alex’;
#客户端B
select name,money from sh_user where name =‘Alex’;
commit;
  从上述结果可以看出,客户端B两次查询的结果是相同的,说明REPEATABLE READ可以避免不可重复读的情况。

(3)REPEATABLE READ(可重复读)
  REPEATABLE READ是MySQL的默认隔离级别,它解决了脏读和不可重复读的问题,确保了同一事物的多个实例在并发读取数据时,会看到同样的结果。
  在理论上,该隔离级会出现幻读(PHANTOM READ)的现象。幻读又被称为虚读,是指在一个事务内两次查询中数据条数不一致,幻读和不可重复读有些类似,同样发生在两次查询过程中。不同的是,幻读时由于其他事务做了插入记录的操作,导致记录数有所增加,不过,MySQL的InnoDB存储引擎通过多版本并发控制机制解决了幻读问题。
  假设客户端A用于新增用户,客户端B用于统计金额
演示客户端B的幻读。
#客户端B
set session transaction isolation level read committed;
start transaction;
#查询总金额为2000
select sum(money) from sh_user;
#客户端A
insert into sh_user(id,name,money) values(3,‘Tom’,1000);
#再次查询总金额为3000
select sum(money) from sh_user;
commit;
#避免客户端B的幻读。将客户端B的隔离级别设置为repeatable read ,即可避免幻读
set session transaction isolation level repeatable read;
start transaction;
#查询总金额为3000
select sum(money) from sh_user;
#客户端A
insert into sh_user(id,name,money) values(4,‘d’,1000);
#客户端B,再次查询总金额仍为3000
select sum(money) from sh_user;
commit;

(4)SERIALIZABLE(可串行化)
  SERIALIZABLE是最高级别的隔离级,它在每个读的数据行上加锁,使之不会发生冲突,从而解决了脏读、不可重复读和幻读的问题。但是由于加锁可能导致超时(Timeout)和锁竞争(Lock Contention)现象,因此SERIALIZABLE也是性能最低的一种隔离级。除非为了数据的稳定性,需要强制减少并发的情况时,才会选择此种隔离级。
  假设客户端B执行查询操作,客户端A执行更新操作:
#演示可串行化,将客户端B的事务隔离级别设置为SERIALIZABLE,然后开启事务
set session transaction isolation level serializable;
start transaction;
#查看Alex的金额为900
select name,money from sh_user where name=‘Alex’;
#客户端A中增加金额
update sh_user set money=money+100 where name=‘Alex’;
(此时光标在不停闪烁,进入等待状态)
#客户端B提交事务,客户端A的操作才会执行,提示执行结果
commit;
(若客户端B一直未提交事务,客户端A的操作会一直等待,直到超时后报错,表示锁等待超时,尝试重新启动事务)
  在默认情况下,锁等待的超时时间为50秒,可通过以下语句查询:
select @@innodb_lock_wait_timeout;

<think>我们正在讨论MySQL事务管理及隔离级别用户希望详细了解MySQL事务及其隔离级别。根据引用内容,MySQL提供四种事务隔离级别:读未提交、读提交、可重复读和串行化。不同级别在并发控制、数据一致性和性能方面有不同表现。此外,InnoDB引擎在可重复读级别下通过MVCC和间隙锁避免大部幻读问题。回答结构:1.事务基本概念:介绍事务的ACID特性2.隔离级别详解:别解释四种隔离级别,以及它们如何解决脏读、不可重复读、幻读问题3.设置隔离级别:如何设置全局、会话级和单个事务隔离级别4.InnoDB的MVCC机制:解释多版本并发控制如何支持事务隔离5.隔离级别的选择建议:根据应用场景推荐合适的隔离级别6.示例:通过SQL示例演示不同隔离级别的行为7.相关问题:生成相关问题注意:引用内容中已经提到了一些关键点,如InnoDB在可重复读级别下避免幻读,以及设置隔离级别的命令。我们将整合这些信息。</think>### MySQL 事务管理及隔离级别详解 #### 一、事务基础概念 **事务(Transaction)** 是数据库操作的逻辑单元,需满足 **ACID** 特性: - **原子性(Atomicity)**:操作要么全部成功,要么全部回滚 - **一致性(Consistency)**:事务前后数据状态合法 - **隔离性(Isolation)**:并发事务互不干扰 - **持久性(Durability)**:提交后数据永久保存 ```sql START TRANSACTION; -- 开启事务 UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- 提交事务 ``` #### 二、隔离级别详解 MySQL 提供四种隔离级别,解决并发问题: | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现机制 | 性能 | |------------------|------|------------|------|------------------------------|--------| | **READ UNCOMMITTED** (读未提交) | ✓ | ✓ | ✓ | 无锁 | 最高 | | **READ COMMITTED** (读已提交) | ✗ | ✓ | ✓ | 快照读(MVCC) | 较高 | | **REPEATABLE READ** (可重复读) | ✗ | ✗ | △⁺ | MVCC + 间隙锁 | 中等 | | **SERIALIZABLE** (串行化) | ✗ | ✗ | ✗ | 全表锁 | 最低 | > **注**: > - **脏读**:读取到未提交的数据 > - **不可重复读**:同一查询返回不同结果(数据被修改) > - **幻读**:同一查询返回新插入的行 > - **⁺**:InnoDB 在 REPEATABLE READ 下通过间隙锁避免大部幻读[^2][^3] #### 三、隔离级别设置方式 ```sql -- 全局设置(影响所有新会话) SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 会话级设置(仅当前连接有效) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 仅对下一个事务有效 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; -- 开启事务... -- 查看当前隔离级别MySQL 8.0+) SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; ``` #### 四、MVCC 多版本并发控制 InnoDB 实现隔离级别的核心机制: ```mermaid graph LR A[事务开始] --> B[事务ID] B --> C[创建ReadView] C --> D{查询数据} D -->|检查版本可见性| E[返回符合版本的数据] ``` - 每个数据行有隐藏字段:`DB_TRX_ID`(最后修改的事务ID)和 `DB_ROLL_PTR`(回滚指针) - 读操作访问**历史版本快照**,写操作创建新版本 - 解决读写冲突,实现非锁定读[^2] #### 五、不同隔离级别行为演示 ##### 场景:账户余额查询 ```sql -- 会话1(事务A) START TRANSACTION; SELECT balance FROM accounts WHERE id=1; -- 返回1000 -- 会话2事务B)更新数据 START TRANSACTION; UPDATE accounts SET balance=900 WHERE id=1; COMMIT; SELECT balance FROM accounts WHERE id=1; -- 不同隔离级别结果: ``` | 隔离级别 | 第二次查询结果 | 说明 | |------------------|----------------|---------------------| | READ UNCOMMITTED | 900 | 读到未提交的修改 | | READ COMMITTED | 1000 → 900 | 读到已提交的新数据 | | REPEATABLE READ | 1000 → 1000 | 保持首次读取结果 | | SERIALIZABLE | 1000 → 1000 | 事务B会被阻塞 | #### 六、隔离级别选型建议 1. **READ COMMITTED** - 适用场景:多数OLTP系统(如电商订单) - 优势:平衡性能与一致性 - 注意点:需处理不可重复读问题 2. **REPEATABLE READ**(MySQL默认级别) - 适用场景:财务系统、对数据一致性要求高的场景 - 优势:避免不可重复读和幻读(InnoDB优化) - 注意点:间隙锁可能导致死锁[^3] 3. **READ UNCOMMITTED/SERIALIZABLE** - 特殊场景:数据仓库(只读析)、银行核心系统 - 谨慎使用:前者数据易,后者性能差 > **最佳实践**: > - 使用 `SHOW ENGINE INNODB STATUS` 监控锁竞争 > - 通过 `EXPLAIN` 析查询是否触发间隙锁 > - 关键业务操作显式指定隔离级别 #### 七、事务控制语句 ```sql START TRANSACTION; -- 显式开启事务 SAVEPOINT sp1; -- 创建保存点 ROLLBACK TO sp1; -- 回滚到保存点 SET autocommit=0; -- 关闭自动提交(默认=1) ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值