mysql 事务

概述

MySQL 事务是指一组操作，要么全部执行成功，要么全部执行失败，是保证数据的一致性和完整性的重要手段。在 MySQL 中，使用 START TRANSACTION 或 BEGIN 开始一个事务，使用 COMMIT 提交事务，使用 ROLLBACK 回滚事务。MySQL 支持 ACID 属性的事务，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），通过这些特性保证事务的正确性和安全性。

数据库事务的四个基本特性：ACID

• 原子性（Atomicity）：事务是一个原子操作，要么全部执行成功，要么全部执行失败，不会只执行一部分操作。如果一个事务执行失败，则所有的修改都会回滚到事务开始前的状态。
• 一致性（Consistency）：事务执行后，数据库从一个一致性状态转移到另一个一致性状态。在事务开始前和结束后，数据库的完整性约束没有被破坏。
• 隔离性（Isolation）：事务的执行是相互隔离的，一个事务所做的修改在提交之前，对其他事务是不可见的。因此，多个并发事务之间不会相互干扰，不会产生脏读、不可重复读、幻读等问题。
• 持久性（Durability）：一旦事务提交，其所做的修改就会永久保存到数据库中，不会因为系统故障或其他异常情况而丢失。

ACID 是保证事务正确性和安全性的基本原则，各个数据库都支持 ACID 属性的事务。开发人员在进行数据库设计和事务管理时，应该遵守 ACID 原则，以确保数据的一致性和完整性。

并发事务处理带来的问题

更新丢失(Lost Update)或脏写:当两个或多个事务选择同一行数据修改，有可能发生更新丢失问题，即最后的更新覆盖了由其他事务所做的更新。 脏读（Dirty Reads）:事务A读取到了事务B已经修改但尚未提交的数据 不可重读（Non-Repeatable Reads） :事务A内部的相同查询语句在不同时刻读出的结果不一致 幻读（Phantom Reads）: 事务A读取到了事务B提交的新增数据

事务的隔离级别

• 读未提交（READ UNCOMMITTED）：允许一个事务读取另一个未提交事务修改的数据，可能导致脏读（Dirty Read）。
• 读已提交（READ COMMITTED）：只允许一个事务读取另一个已经提交的事务修改的数据，可以避免脏读，但可能会导致不可重复读（Non-Repeatable Read）。
• 可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同一数据时，结果始终相同，避免了不可重复读的问题。MySQL 默认使用的就是这种隔离级别。
• 串行化（SERIALIZABLE）：在读取和修改数据时，对其它事务都加锁，以保证并发事务之间的隔离性最强，避免了所有的并发问题。但这种隔离级别可能导致大量的锁等待和死锁，性能较差。 | 隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) | | --- | --- | --- | --- | | 读未提交（READ UNCOMMITTED） | 可能 | 可能 | 可能 | | 读已提交（READ COMMITTED） | 不可能 | 可能 | 可能 | | 可重复读（REPEATABLE READ） | 不可能 | 不可能 | 可能 | | 串行化（SERIALIZABLE） | 不可能 | 不可能 | 不可能 |

案例分析

读未提交（READ UNCOMMITTED）

-- 设置数据库事务隔离级别
set tx_isolation='read-uncommitted';

• 打开一个客户端（A）并将当前事务模式设置为read uncommitted级别，查询表id=1的初始值

set tx_isolation='read-uncommitted';
SELECT * FROM users_two WHERE id = 1;

• 在事务A提交事务之前，开启另一个客户端B并执行修改语句

set tx_isolation='read-uncommitted';
BEGIN;
UPDATE users_two SET phone = '1234567890';

• 通过A事务查询为id=1的记录可以发现，虽然B事务还未提交事务，但是在A事务查询id=1，可以看到事务B修改的数据

SELECT * FROM users_two WHERE id = 1;

• 一旦B事务进行回滚，那么A事务查询到的数据就是脏数据。想要解决这个问题将数据库隔离级别设置为READ COMMITTED

读已提交（READ COMMITTED）

-- 设置数据库隔离级别
set tx_isolation='read-committed';

• 打开一个客户端A并将设置当前事务为read committed 级别，查询id为1的记录

set tx_isolation='read-committed';
BEGIN;
SELECT * FROM users_two WHERE id = 1;

• 在事务A提交事务之前，开启另一个客户端B并执行修改语句

set tx_isolation='read-committed';
BEGIN;
UPDATE users_two SET phone='1234' WHERE id = 1;

• 通过A事务查询为id=1的记录可以发现，解决了会读到B事务还未提交的数据

SELECT * FROM users_two WHERE id = 1;

• 提交B事务

commit；

• 通过A事务查询id=1的记录会发现可以查询到B事务提交的数据，那么就会产生不可重复读的问题，想要解决将数据库隔离界别设置为REPEATABLE READ

SELECT * FROM users_two WHERE id = 1;

可重复读（REPEATABLE READ）

可重复读隔离级别在事务开启的时候，第一次查询是查的数据库里已提交的最新数据，这时候全数据库会有一个快照(当然数据库并不是真正的生成了一个快照)，在这个事务之后执行的查询操作都是查快照里的数据，别的事务不管怎么修改数据对当前这个事务的查询都没有影响，但是当前事务如果修改了某条数据，那当前事务之后查这条修改的数据就是被修改之后的值，但是查其它数据依然是从快照里查，不受影响。

-- 设置数据库隔离级别
set tx_isolation='repeatable-read';

• 演示可重复读
  • 首先开启一个事务A，查询表中所有记录

set tx_isolation='repeatable-read';
SELECT * FROM users_two;

• 在A事务提交事务之前，开启一个B事务对id = 1 的数据进行修改；

set tx_isolation='repeatable-read';
BEGIN;
UPDATE users_two SET phone = '11111111' WHERE id = 1;

• 在A事务中，执行查询语句，跟第一次查询的结果一致

SELECT * FROM users_two WHERE id =1 ;

• 演示修改过后为当前读
  • 开启一个事务，查询id=1的记录

set tx_isolation='repeatable-read';
BEGIN;
SELECT * FROM users_two WHERE id = 1;

• 然后修改id =1 的记录，在查询id = 1 的记录，发现会读到此事务更新过后的数据，称为当前读

UPDATE users_two SET phone = '222222' WHERE id = 1;

• 演示幻读问题
  • 首先开始事务A，执行查询id=1000的记录

set tx_isolation='repeatable-read';
BEGIN;
SELECT * FROM users_two WHERE id = 1000;

• 开始事务B，插入一条id=1000的数据，并且提交事务

set tx_isolation='repeatable-read';
BEGIN;
INSERT INTO `test`.`users_two` (`id`, `username`, `password`, `email`, `gender`, `phone`, `birthday`, `address`, `avatar`, `status`, `created_time`, `updated_time`) VALUES (1000, '47326', '50673', 'user0@example.com', 3, '222222', '2060-06-04', 'Address 373', '/avatars/user155.png', 0, '2023-04-20 21:28:24', '2023-04-25 07:12:08');
COMMIT;

• 在事务A中先查询id = 1000发现查不到此数据，但是可以修改id=1000的记录,A事务执行成功了，此时就是产生幻读的问题

SELECT * FROM users_two WHERE id = 1000;

UPDATE users_two SET phone = '123' WHERE id = 1000;

串行化（SERIALIZABLE）

-- 设置数据库隔离级别
set tx_isolation='serializable';

• 打开一个客户端，开启一个事务A，执行查询id=1的数据

set tx_isolation='serializable';
BEGIN;
SELECT * FROM users_two WHERE id = 1;

• 打开一个客户端B，更新id=1的记录会被阻塞等待，更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁，如果查询的记录不存在会给这条不存在的记录加上间隙锁，如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围都会被加锁。此时如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读。这种隔离级别并发性极低，开发中很少会用。

set tx_isolation='serializable';
BEGIN;
UPDATE users_two SET phone = '1321' WHERE id =1;

UPDATE users_two SET phone = '1321' WHERE id =2;