MySQL事务

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