MySQL事务

本文最后更新于 2024年11月30日

1.事务

MySQL的事务是一组被视为一个单一逻辑工作单元的SQL操作。事务确保这些操作要么全部成功,要么在出现错误时全部失败并回滚,以保持数据库的一致性和完整性。事务通常用于需要多个步骤的操作,这些步骤必须全部成功才能确保数据的完整性,比如在银行转账中,转出和转入账户的操作必须同时完成。

在MySQL中,不同的存储引擎具有不同的特性,myisam memory存储引擎均不支持事务,如果你需要事务支持,InnoDB 是最推荐的选择,因为它在性能和事务处理方面表现优异,并且是MySQL的默认存储引擎。

查看当前存储引擎

SHOW ENGINES;

查看事务是否自动提交

SHOW VARIABLES LIKE '%autocommit%';

MySQL隐式事务INSERT, UPDATE, DELETE默认开启自动提交

2.事务的特性

  • Atomicity, 原子性:每个事务都是不可分割的单位,要么全部执行,要么全部失败
  • Consistency, 一致性:使数据库从一个一致的状态切换到另一个一致性的状态
  • Isolation, 隔离性:一个事务执行不受其他事务干扰(需要隔离级别控制)
  • Durability, 持久性:一个事务,一旦提交,就是永久性改变

3.事务的语法

提交一个事务

-- 必须,使用多个语句的事务,需要禁用隐式事务
SET autocommit = 0;  

-- 可选
START TRANSACTION; 

-- 只有增删改查可以有事务,ddl语句没有事务
UPDATE account SET money = money-20 WHERE id = 1; 
UPDATE account SET money = money+20 WHERE id = 2; 

-- 结束事务,应用程序外无法决定使用提交还是回滚,COMMIT,ROLLBACK只能手动选择一个
COMMIT;

-- ROLLBACK;

回滚到回滚点

-- 必须,使用多个语句的事务,需要禁用隐式事务
SET autocommit = 0;  

 -- 可选
START TRANSACTION; 

-- 只有增删改查可以有事务,ddl语句没有事务
UPDATE account SET money = money-20 WHERE id = 1; 

-- 回滚点
SAVEPOINT a

UPDATE account SET money = money+20 WHERE id = 2; 

-- 结束事务,应用程序外无法决定使用提交还是回滚,COMMIT,ROLLBACK只能手动选择一个

-- COMMIT;

ROLLBACK TO a;

4.事务的隔离级别

运行多个事务,访问相同数据,如果不采取隔离机制,就会引发并发问题,设置隔离级别避免并发问题,Oracle支持:读已提交,串行化,默认读已提交,MySQL支持:读未提交 ,读已提交 ,可重复读 ,串行化,默认可重复读。

4.1 事务中的读现象

  1. 脏读

    • 定义:一个事务读取到另一个未提交事务修改的数据,如果该事务回滚,那么读取的数据将是无效的。
    • 影响:导致数据不一致,通常与更新操作相关。
  2. 不可重复读

    • 定义:一个事务内多次读取同一数据,结果却不一致,这是因为在两次读取之间,其他事务修改了数据。
    • 影响:导致一个事务在不同时间点读取相同数据的结果不同,通常与更新操作相关。
  3. 幻读

    • 定义:一个事务读取到的结果和之后的读取不一致,是因为其他事务插入或删除了数据行。
    • 影响:事务在读取同一范围的数据时,发现新插入或删除的“幻影”行,通常与插入或删除操作相关。

4.2 事务的隔离级别及其特性

  1. 读未提交(Read Uncommitted)

    • 允许现象:脏读。
    • 特点:事务可以读取到其他事务未提交的修改。
    • 问题:数据一致性最低。
  2. 读已提交(Read Committed)

    • 避免:脏读。
    • 允许现象:不可重复读和幻读。
    • 特点:一个事务只能读取其他已提交事务的数据。
    • 问题:数据在同一事务内多次读取时,结果可能不一致。
  3. 可重复读(Repeatable Read)

    • 避免:脏读和不可重复读。
    • 允许现象:幻读。
    • 特点:保证一个事务内多次读取同一数据时结果一致。
    • 问题:数据一致性更高,但仍可能出现幻读。
  4. 串行化(Serializable)

    • 避免:脏读、不可重复读、幻读。
    • 特点:事务按顺序执行,完全隔离,达到最高级别的数据一致性。
    • 问题:性能最低,因事务需要排队,常引起锁竞争。

总结

  • 脏读可以通过将隔离级别提高到 读已提交 或更高来避免。
  • 不可重复读可通过使用 可重复读串行化 隔离级别来避免。
  • 幻读只有在 串行化 隔离级别下才完全解决,但这会牺牲性能。

4.3设置隔离级别

查看当前隔离级别

SELECT @@tx_isolation

设置隔离级别为读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

MySQL事务
https://blog.liuzijian.com/post/daa56f87-d4f2-a4e1-277c-4a4ead4b4fde.html
作者
Liu Zijian
发布于
2023年10月27日
更新于
2024年11月30日
许可协议