MySQL 学习笔记之事务操作

MySQL 事务操作

事务概述

事务是数据库管理系统中的一个重要概念，它用于保证数据库操作的完整性和一致性。事务可以确保一组操作要么全部成功，要么全部失败，避免了部分成功部分失败的情况，从而保持数据的一致性。

事务的四个特性（ACID）：

1. 原子性 (Atomicity)：事务中的所有操作要么全部成功，要么全部失败，不会出现部分成功的情况。
2. 一致性 (Consistency)：事务的执行会将数据库从一个一致性状态转变为另一个一致性状态。
3. 隔离性 (Isolation)：一个事务的操作不会受到其他事务的干扰，每个事务都在独立的环境中运行。
4. 持久性 (Durability)：一旦事务被提交，其对数据库的更改是持久的，即使系统崩溃也不会丢失。

1. 事务操作的基本用法

1.1 创建表和插入数据

CREATE TABLE account
(id    INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',name  VARCHAR(10) COMMENT '姓名',money DOUBLE(10, 2) COMMENT '余额'
) COMMENT '账户表';INSERT INTO account(name, money)
VALUES ('张三', 2000),('李四', 2000);

说明：

• AUTO_INCREMENT：自动生成唯一的ID。
• DOUBLE(10, 2)：定义金额的字段，最多10位数字，其中2位为小数部分。

1.2 设置手动提交

SET @@autocommit = 1; -- 设置手动提交

说明：

• @@autocommit：用于控制事务是否自动提交。设置为1表示启用自动提交，设置为0表示手动提交事务。

1.3 正常的转账操作

-- 查询张三余额
SELECT * FROM account WHERE name = '张三';-- 张三的余额减少1000
UPDATE account SET money = money - 1000 WHERE name = '张三';-- 李四的余额增加1000
UPDATE account SET money = money + 1000 WHERE name = '李四';-- 提交事务
COMMIT;

说明：

• 在进行数据修改（如转账操作）时，通常需要先查询确认操作的前提条件，然后执行修改操作，最后提交事务以保存修改。

1.4 异常情况处理

-- 查询张三余额
SELECT * FROM account WHERE name = '张三';-- 张三的余额减少1000
UPDATE account SET money = money - 1000 WHERE name = '张三';-- 出错了...
-- 李四的余额增加1000
UPDATE account SET money = money + 1000 WHERE name = '李四';-- 回滚事务
ROLLBACK;

说明：

• 如果在事务执行过程中发生错误，可以使用 ROLLBACK 回滚事务，撤销所有未提交的修改，恢复到事务开始之前的状态。

1.5 使用 START TRANSACTION

-- 启动事务
START TRANSACTION;-- 查询张三余额
SELECT * FROM account WHERE name = '张三';-- 张三的余额减少1000
UPDATE account SET money = money - 1000 WHERE name = '张三';-- 出错了...
-- 李四的余额增加1000
UPDATE account SET money = money + 1000 WHERE name = '李四';-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;

说明：

• START TRANSACTION 用于显式地开始一个事务。在事务执行完毕后，使用 COMMIT 提交事务，或者在出现异常时使用 ROLLBACK 回滚事务。

2. 事务隔离级别

事务隔离级别定义了事务在并发环境下的行为。不同的隔离级别会影响事务的可见性和数据库的一致性。MySQL 提供了四种隔离级别：

1. READ UNCOMMITTED：允许读取未提交的数据，可能会出现脏读。
2. READ COMMITTED：只允许读取已提交的数据，防止脏读，但可能会出现不可重复读。
3. REPEATABLE READ：在事务开始时，读取的结果在事务期间始终如一，防止脏读和不可重复读，但可能出现幻读。
4. SERIALIZABLE：最严格的隔离级别，通过强制事务串行执行，避免脏读、不可重复读和幻读，但性能可能较低。

2.1 查看当前事务隔离级别

-- 查看当前事务隔离级别
SELECT @@transaction_isolation;

说明：

• 通过 @@transaction_isolation 可以查看当前数据库的事务隔离级别。

2.2 设置事务隔离级别

-- 设置事务隔离级别为 READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置事务隔离级别为 REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

说明：

• SET SESSION TRANSACTION ISOLATION LEVEL 用于设置当前会话的事务隔离级别。
• 不同的隔离级别会影响事务的并发行为和数据一致性，根据实际需求选择合适的隔离级别。

完整代码

-- ---------------------------------------- 事务操作 -------------------------------------
create table account
(id    int primary key AUTO_INCREMENT comment ' ID',name  varchar(10) comment ' 姓名 ',money double(10, 2) comment ' 余额 '
) comment ' 账户表 ';
insert into account(name, money)
VALUES (' 张三 ', 2000),(' 李四 ', 2000);-- 恢复数据
update account set money = 2000 where name = ' 张三 ' or name = ' 李四 ';select @@autocommit;set @@autocommit = 1; -- 设置手动提交-- 转账操作
-- 正常情况
-- 1 . 查询张三余额
select * from account where name = ' 张三 ' ;
-- 2 . 张三的余额减少 1000
update account set money = money - 1000 where name = ' 张三 ' ;
-- 3 . 李四的余额增加 1000
update account set money = money + 1000 where name = ' 李四 ' ;-- 异常情况
-- 1 . 查询张三余额
select * from account where name = ' 张三 ' ;
-- 2 . 张三的余额减少 1000
update account set money = money - 1000 where name = ' 张三 ' ;
-- 出错了 . . . .
-- 3 . 李四的余额增加 1000
update account set money = money + 1000 where name = ' 李四 ' ;-- 提交事务
commit;-- 回滚事务（出现异常就操作）
rollback;-- 方式二
-- 转账操作
start transaction;-- 异常情况
-- 1 . 查询张三余额
select * from account where name = ' 张三 ' ;
-- 2 . 张三的余额减少 1000
update account set money = money - 1000 where name = ' 张三 ' ;
-- 出错了 . . . .
-- 3 . 李四的余额增加 1000
update account set money = money + 1000 where name = ' 李四 ' ;-- 提交事务
commit;-- 回滚事务
rollback;-- 事务隔离级别
-- 查看事务隔离级别
select @@transaction_isolation;-- 设置事务隔离级别
set session transaction isolation level read uncommitted;set session transaction isolation level repeatable read;