数据库事务

发表于 更新于 分类于 本文字数： 1.8k 阅读时长 ≈ 7 分钟

1. 数据库事务

1.1 数据库事务概述

  • 数据库事务 (transaction) 是一套 操作数据库命令的有序集合，是一个不可分割的工作单位。
  • 事务中单个命令不会立即改变数据库数据，当内部全部命令执行成功，统一更新数据；当有任何一命令失败时，可以进行状态回滚。
  • 事务由事务开始与事务结束之间执行的全部数据库操作组成。
事务的作用：

  1. 为数据库操作序列提供了一个从失败中恢复到正常状态的方法。

  2. 当多个应用程序在并发访问数据库时，以防止彼此的操作相干扰。

1.2 数据库事务 ACID 特性

  1. 原子性 (Atomicity)
    • 原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部发生，要么都不发生。
  2. 一致性 (Consistency)
    • 事务内部的操作的状态前后一致，成功则全部成功，失败则全部失败，避免部分成功出现错误数据。
  3. 隔离性 (Isolation)
    • 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并且执行的各个事务之间不能互相干扰。
  4. 持久性 (Durability)
    • 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

1.3 事务的开启、提交、回滚

  • MySQL默认情况下是自动提交事务。
  • 每条语句都是一个独立的事务：一旦成功就提交，语句报错失败就回滚。
  • 目标：将多条语句加入到一个事务中。
方案 1：手动提交模式
1
2
3
4
5
6
7
# 开启手动提交事务模式 (取消自动提交事务)
set autocommit = false;  或  set autocommit = 0;
该语句执行后，它之后的所有SQL，都需要手动提交才会生效，直到恢复自动提交模式。
# 恢复自动提交模式
set autocommit = true;  或  set autocommit = 1;
#查看是否自动提交
show variables like 'autocommit';

示例： 

1
2
3
4
set autocommit = false;  #设置当前连接为手动提交模式(一个连接内有效)
update t_employee set salary = 15000 where ename = '孙红梅';
commit;  #提交 
rollback;  #回滚

方案 2：自动提交模式下开启独立事务
1
2
3
4
5
6
# 也可以在自动提交模式下，开启一个事务
start transaction;
# 添加多个sql命令

# 最后可以提交或者回滚
commit; 或 rollback

示例： 

1
2
3
4
5
start transaction; #开始事务
update t_employee set salary = 0 where ename = '李冰冰';

#下面没有写commit;那么上面这句update语句没有正式生效
commit; 或 rollback; #提交

方案 3：DDL不支持事务（注意）

  • 说明：

    • DDL 不支持事务。
    • DDL（数据定义语言）包括：create、drop、alter 等，用于创建库、创建表、删除库、删除表、修改库、修改表结构等操作。这些语句不支持事务控制。换句话说，事务仅对 insert、update、delete 语句支持。
    • TRUNCATE 表名; 清空整个表的数据，但不支持事务。
    • 其作用类似于drop 表，然后新建一张表。

  • 示例：

    1
    2
    3
    START TRANSACTION;
    TRUNCATE t_employee;
    ROLLBACK;  -- 回滚无效

    说明：即使在事务内使用 START TRANSACTION 进行开始事务，TRUNCATE 的操作也无法回滚。

1.4 事务隔离性

一个事务内部的操作以及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰

隔离级别 概述 脏读 不可重复读 幻读
read-uncommitted 读未提交事务数据 是 是 是
read-committed 读已提交事务数据(oracle) 否 是 是
repeatable-read 可重复度(mysql) 否 否 是(小概率)
serializable 串行化和序列化 否 否 否

数据库事务的隔离性：一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性能越弱

  • 脏读：一个事务读取了另一个事务未提交的数据；（数据上的错误）

  • 不可重复读：一个事务读取了另一个事务提交的修改数据。（不符合一致性原则）

  • 幻读：一个事务读取了另一个事务提交的新增、删除的记录情况，记录数不一样，像是出现幻觉。（不符合一致性原则）

1
2
3
4
# 修改隔离级别:
set transaction_isolation='隔离级别';
#查看隔离级别:
select @@transaction_isolation;

示例：张三欠李四1000块钱，张三还钱给李四

1
2
3
4
# 假设李四现在有10000，李四查询余额得到
select * from bank where account = 'lisi';
# account money
# lisi    10000

脏读：李四读到了张三未提交的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 张三和李四分别都开启事务
set transaction_isolation = 'read-uncommitted';
start transaction;

# 张三还钱
update bank set money = money + 1000 where acount = 'lisi';
update bank set money = money - 1000 where acount = 'zhangsan';

# 此时李四查询：
select * from bank where account = 'lisi';
# account money
# lisi    11000

# 然而此时张三回滚
rollback;

#李四的钱则会变回10000
select * from bank where account = 'lisi';
# account money
# lisi    10000

不可重复读：数据是对的，但是不符合一致性原则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 张三和李四分别都开启事务
set transaction_isolation = 'read-committed';
start transaction;

# 张三还钱
update bank set money = money + 1000 where acount = 'lisi';
update bank set money = money - 1000 where acount = 'zhangsan';

# 此时李四查询：
select * from bank where account = 'lisi';
# account money
# lisi    10000
# 查询到的余额仍然是10000，读取不到未提交数据，只有张三commit后才能收到
# 数据没有错误，但不符合一致性原则

可重复读：事务不会读取了另一个事务提交的修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 张三和李四分别都开启事务
set transaction_isolation = 'repeatable-read';
start transaction;

# 张三还钱
update bank set money = money + 1000 where acount = 'lisi';
update bank set money = money - 1000 where acount = 'zhangsan';
commit;

# 此时李四查询：
select * from bank where account = 'lisi';
# account money
# lisi    10000
# 查询到的余额仍然是10000，李四不会读取了张三提交的修改

幻读：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 张三和李四分别都开启事务
start transaction;

# 李四查询全部人
select * from bank;
# account money
# lisi    10000
# zhangsan 8000

# 张三
insert into bank values ('wangwu', 900);
commit;

# 此时李四再查询全部人
select * from bank;
# 如果查询到了王五，就是出现了幻读现象。正常情况下李四应该commit;之后重新查询才能查询到
# set transaction_isolation = 'read-uncommitted'; 会产生幻读
# set transaction_isolation = 'read-committed';	 会产生幻读
# set transaction_isolation = 'repeatable-read'; 小概率产生
# set transaction_isolation = 'serializable'; 不会产生幻读

serializable：取消了并发性，彻底解决了错误，然而效率太低

1
2
3
4
5
6
7
8
9
10
11
# 张三和李四分别都开启事务
set transaction_isolation = 'serializable'
start transaction;

# 张三修改数据
update bank set money = money + 1000 where acount = 'lisi';
# 然后李四查询
select * from bank where account = 'lisi';
# 设置成serializable后，意为设置为串行化，取消了并发性，此时李四的语句不会得到执行，得不到任何结果
# 只有等张三提交后，李四那边会立即出现查询结果
commit;