暗夜零星
暗夜零星

Mysql事务原理

Eddie Eddie 收录于 性能调优 和 Mysql调优
 约 4600 字 预计阅读 10 分钟  - 次阅读  

Mysql 事务案例、原理。

前言

在数据库中，多个事务往往同时执行，对共享数据进行读写操作，这可能会引发脏写、脏读、不可重复读及幻读等问题。

这些问题本质上源于多事务并发执行。为应对这些挑战，数据库设计了一系列机制，包括事务隔离机制、锁机制、MVCC多版本并发控制隔离机制及日志机制。

这些机制共同构成了数据库解决多事务并发问题的完整方案。

事务特性

事务旨在确保数据的一致性，通过确保一组操作要么全部成功执行，要么全部失败回滚。事务具备ACID四大属性：

  • 原子性(Atomicity)：事务操作具有不可分割性，通过 undo log 实现回滚保证。
  • 一致性(Consistency)：事务完成后，数据库必须保持一致状态，依赖业务逻辑和其它 ACID 属性。
  • 隔离性(Isolation)：并发事务互不干扰，通过MySQL锁和MVCC实现。
  • 持久性(Durability)：一旦事务提交，其对数据库的更改将永久保存，由 redo log 确保。

并发事务处理常遇问题

  • 更新丢失（Lost Update）：两事务同时修改同一行数据，后者覆盖前者更新。
  • 脏读（Dirty Reads）：事务A读取事务B未提交的修改。
  • 不可重读（Non-Repeatable Reads）：事务A内相同查询在不同时刻结果不同。
  • 幻读（Phantom Reads）：事务A读取到事务B提交后新增的数据。

事务隔离级别

脏读、不可重复读和幻读，均属数据库读一致性问题，需通过事务隔离机制来解决。

隔离级别 脏读(Dirty Read) 不可重复读(Non-Repeatable Read) 幻读(Phantom Read)
读未提交(Read uncommitted) 可能 可能 可能
读已提交(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
串行化(Serializable) 不可能 不可能 不可能

事务隔离级别越高，并发副作用越小，但性能开销增大，因为隔离实质上是通过事务串行化来减少冲突。 不同应用对读一致性和隔离级别的需求各异，例如部分应用可能更重视并发性能而非严格的隔离性。

查看当前事务隔离级别: show variables like 'tx_isolation';

设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

提示
MySQL默认事务隔离级别为可重复读，Spring程序开发时，隔离级别默认使用MySQL设置，除非在Spring中明确配置。

案例

1
2
3
4
5
6
7
8
9

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

读未提交

  1. 打开客户端A，并设置事务隔离级别read uncommitted（未提交读），查询表account的初始值。

    set tx_isolation='read-uncommitted';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     450 |
|  2 | hanmei|   16000 |
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)

-- 客户端A
  1. 在 A 事务提交前，打开客户端B，更新表account
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |   400   |
|  2 | hanmei|  15400  |
|  3 | lucy  |   2400  |
+----+-------+---------+
3 rows in set (0.00 sec)
              
-- 客户端B
  1. 这时，虽然 B 的事务未提交，但是 A 已能查询到 B 修改的数据。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     450 |
|  2 | hanmei|    16000|
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     400 |
|  2 | hanmei|    16000|
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)
              
-- 客户端A
  1. 一旦 B 的事务回滚，所有操作都将被撤销，那么 A 查询到的数据就是脏数据
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     400 |
|  2 | hanmei|   16000 |
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)

-- 此处假设用户B注意到lilei的账户余额与之前查询的不同

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 回滚事务后，再次查询以确认数据状态

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     450 |
|  2 | hanmei|   16000 |
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)
              
-- 客户端B
  1. 在 A 执行语句update account set balance = balance - 50 where id = 1后，发现 lilei 的余额并非预期的 350，而是 400，这看似数据不一致。 然而，这可能是因为其他会话进行了回滚操作。在应用中，若按400-50=350计算，将忽视其他会话的影响。为解决此类问题，可采用“读已提交”的隔离级别。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

| mysql> select * from account;
+----+------+--------+
| id | name  | balance |
+----+------+--------+
|  1 | lilei |    400  |
|  2 | hanmei |  16000  |
|  3 | lucy  |   2400  |
+----+------+--------+
3 rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+--------+
| id | name  | balance |
+----+------+--------+
|  1 | lilei |    400  |
|  2 | hanmei |  16000  |
|  3 | lucy  |   2400  |
+----+------+--------+
3 rows in set (0.00 sec)

客户端A

读已提交

  1. 打开客户端A，设置隔离级别 read committed（读已提交），查询表 account 的所有记录

    set tx_isolation='read-committed';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     450 |
|  2 | hanmei|   16000 |
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)

-- 客户端A
  1. 在 A 的事务提交之前，打开另一个客户端B，更新表account
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |   400   |
|  2 | hanmei|  16000  |
|  3 | lucy  |   2400  |
+----+-------+---------+
3 rows in set (0.00 sec)
              
-- 客户端B
  1. 这时，B的事务未提交，A 不能查询到 B 已经更新的数据，解决了脏读问题
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1  | lilei  | 450     |
| 2  | hanmei | 16,000  |
| 3  | lucy   | 2,400   |
+----+--------+---------+
3 rows in set (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1  | lilei  | 450     |
| 2  | hanmei | 16,000  |
| 3  | lucy   | 2,400   |
+----+--------+---------+
3 rows in set (0.00 sec)

-- 客户端A
  1. 客户端B的事务提交
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     400 |
|  2 | hanmei|   16000 |
|  3 | lucy  |    2400 |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- 客户端B
  1. 客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

mysql> select * from account limit 50 offset 0;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     450 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

-- 此处可能发生了某些操作导致数据变更 --

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     400 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

-- 注意：不可重复读的情况可能发生在此处 --

-- 客户端A

可重复读

在可重复读隔离级别下，事务开始时会“拍照”记录数据库的状态。之后的查询都参考这张“照片”，不受其他事务更改的影响。但如果自己的事务改了数据，再查这部分数据时，会看到改动，其他数据还是“照片”里的样子。

  1. 打开客户端A，并设置事务隔离级别为repeatable read（可重复度），查询表 account 的所有记录

    set tx_isolation='repeatable-read';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+-------+---------+
| id   | name  | balance |
+------+-------+---------+
|    1 | lilei |     400 |
|    2 | hanmei|   16000 |
|    3 | lucy  |    2400 |
+------+-------+---------+
3 rows in set (0.00 sec)
              
-- 客户端A
  1. 在 A 的事务提交前，打开客户端 B，更新表 account 并提交
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |   350   |
|  2 | hanmei|  16000  |
|  3 | lucy  |   2400  |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- 客户端B
  1. 在客户端A查询表 account 的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24



mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei | 400     |
|  2 | hanmei| 16000   |
|  3 | lucy  | 2400    |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei | 400     |
|  2 | hanmei| 16000   |
|  3 | lucy  | 2400    |
+----+-------+---------+
3 rows in set (0.00 sec)

-- 两次查询结果一致
-- 客户端A
  1. 在客户端A，接着执行update account set balance = balance - 50 where id = 1，balance 没有变成 400-50=350， lilei 的balance值用的是步骤2中的350来算的，所以是300，数据的一致性倒是没有被破坏。 可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select 操作是快照读（历史版本）；insert、update 和 delete 是当前读（当前版本）。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei | 400     |
|  2 | hanmei| 16000   |
|  3 | lucy  | 2400    |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei | 300     |
|  2 | hanmei| 16000   |
|  3 | lucy  | 2400    |
+----+-------+---------+
3 rows in set (0.00 sec)

-- 客户端A
  1. 重新打开客户端B，插入一条新数据后提交
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values('4','lily','700');
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+-------+--------+
| id | name  | balance |
+----+-------+--------+
|  1 | lilei |    350  |
|  2 | hanmei|   16000 |
|  3 | lucy  |   2400  |
|  4 | lily  |    700  |
+----+-------+--------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- 客户端B
  1. 在客户端A查询表account的所有记录，没有查出新增数据，所以没有出现幻读
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     300 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

-- 客户端A
  1. 验证幻读

在客户端 A 执行update account set balance=888 where id = 4；能更新成功，再次查询能查到客户端B新增的数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> update account set balance=888 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     250 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
|  4 | lily   |     888 |
+----+--------+---------+
4 rows in set (0.00 sec)

-- 客户端A

串行化

  1. 打开客户端A，并设置当前事务模式为serializable，查询表account的初始值：

set tx_isolation='serializable';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id = 1;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | lilei |     450 |
+----+-------+---------+
1 row in set (0.00 sec)

mysql>

-- 客户端A
  1. 打开客户端B，并设置当前事务模式为serializable，更新相同的id为1的记录会被阻塞等待，更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁，如果查询的记录不存在会给这条不存在的记录加上锁(这种是间隙锁)。

如果客户端A执行的是一个范围查询，那么该范围内的所有行、包括每行记录所在的间隙区间都会被加锁。此时如果客户端B在该区间内插入数据都会被阻塞，所以就避免了幻读。

这种隔离级别并发性极低，开发中很少会用。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = 10000 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update account set balance = 10000 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

-- 客户端B

事务问题定位

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

#查询执行时间超过1秒的事务
SELECT
	* 
FROM
	information_schema.innodb_trx 
WHERE
	TIME_TO_SEC( timediff( now( ), trx_started ) ) > 1;
 
 #强制结束事务
 kill 事务对应的线程id(就是上面语句查出结果里的trx_mysql_thread_id字段的值)

大事务的影响

  • 并发情况下，数据库连接池容易被撑爆
  • 锁定太多的数据，造成大量的阻塞和锁超时
  • 执行时间长，容易造成主从延迟
  • 回滚所需要的时间比较长
  • undo log膨胀
  • 容易导致死锁

事务优化

  • 将查询等数据准备操作放到事务外
  • 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
  • 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
  • 更新等涉及加锁的操作尽可能放在事务靠后的位置
  • 能异步处理的尽量异步处理
  • 应用侧(业务代码)保证数据一致性，非事务执行
收录于 合集・Mysql调优 9
更新于 2023-05-26 
查看源码编辑此页报告问题
Mysql事务
返回 | 主页
0%