MySQL事务:事务隔离

对数据进行并发操作时,事务可以确保数据的完整性,在银行、证券交易等业务场景需要用到它,本文将介绍MySQL事务是如何保证数据的一致性的。

事务是指逻辑上的一组操作(insert,delete,update)中的各个单元要么全部成功,要么全部失败。也就是说,事务是一个最小的工作单元,不可再分。

事务特性

只有对数据库表的增(insert)、删(delete)、改(update)与事务有关,事务具有四个特性:

  • Atomicity,原子性。事务是最小的工作单元,不可再分。
  • Consistency,一致性。事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
  • Isolation,隔离性。多个事务之间要相互隔离。
  • Durability,持久性。一个事务一旦被提交,它对数据库中数据的改变就是永久性的。

MySQL 原生的 MyISAM 引擎不支持事务, InnoDB是支持事务的,InnoDB引擎的undo log(回滚日志)是其实现事务的基础。相关介绍可参考 MySQL日志系统:binlog、redo log和undo log

使用InnoDB引擎的MySQL服务默认是开启自动提交的:

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql默认会将用户的操作当做事务即时提交,下面来举例说明事务的执行过程。

事务启动与提交

创建表并插入数据:

create table department(
    id int not null auto_increment primary key COMMENT 'ID',
    name char(4) not null COMMENT '名称') ENGINE=InnoDB 
	DEFAULT CHARSET=utf8
	comment = '部门'; 

insert into department (name) values ('开发');

数据表数据如下:

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
+----+------+
1 row in set (0.00 sec)

客户端A显式地开启一个事务,插入数据:

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

mysql> insert into department (name) values ('产品');
Query OK, 1 row affected (0.01 sec)

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
+----+------+
2 rows in set (0.03 sec)

客户端B查询:

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
+----+------+
1 row in set (0.00 sec)

客户端B没有查询到A插入的数据,因为A没有提交,还没有完成一个事务。(注:当前使用的隔离级别为可重复读)

接下来提交A的事务:

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

客户端B再来查询:

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
+----+------+
2 rows in set (0.00 sec)

这时就可以查询到A插入的数据了。

事务回滚

启动事务,使用savepoint保存节点:

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

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

mysql> insert into department (name) values ('测试');
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into department (name) values ('项目');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
|  3 | 测试 |
|  4 | 项目 |
+----+------+
4 rows in set (0.00 sec)

如果事务执行过程中突然崩溃,mysql会回滚到事务执行前的状态。这里使用rollback来手动回滚:

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

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
|  3 | 测试 |
+----+------+
3 rows in set (0.00 sec)

mysql> rollback to sp1;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
+----+------+
2 rows in set (0.00 sec)

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

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
+----+------+
2 rows in set (0.00 sec)

再次执行一个插入事务:

mysql> insert into department (name) values ('测试');
Query OK, 1 row affected (0.06 sec)

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
|  5 | 测试 |
+----+------+
3 rows in set (0.00 sec)

可以看到回滚执行了删除(delete)的操作。

什么是事务隔离

前面举例说明了单个事务的执行过程 ,真实业务中往往是多个事务同时执行,MySQL有多种事务隔离级别,隔离越彻底,效率可能会越低,所以需要根据自己的业务情况来选择合适的隔离方法。

事务隔离级别

MySQL数据库定义了四种事务隔离级别:

1、读未提交(read uncommitted):事务还未提交,它做的变更就可以被其它事务读到。

  • 最低的隔离级别。
  • 这种隔离级别会导致脏读(Dirty Read)现象,事务A读取到了事务B未提交的数据。

2、读提交(read committed):事务提交之后,它做的变更才会被其他事务读到。

  • 可避免脏读现象发生。
  • 会导致不可重复读现象:指在一个事务内读取表中的某一行数据,多次读取结果不同,重新读取了前一事务已提交的数据。
  • 是oracle数据库的默认隔离级别。

3、可重复读(repeatable read),事务读取到的数据,永远是此事务启动时读到的数据。也就是说,事务B修改了数据并且提交了,事务A读取到的数据还是不变。

  • 可避免脏读、不可重复读情况的发生。
  • 会出现虚读(幻读),每次读取到的数据是假象,不够真实。
  • MySQL的默认事务隔离级别。

4、串行化(serializable ),事务串行执行,不能多事务并发执行。

  • 最高的隔离级别,解决前面提到的所有问题,当然效率也是最低的。

查询MySQL数据库隔离级别:

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.01 sec)

事务隔离实例

以转账为例来举例说明这4中隔离级别,先创建account表:

create table account(
    id int not null auto_increment primary key COMMENT 'ID',
    name char(30) not null COMMENT '账户名',
    balance int COMMENT '余额') ENGINE=InnoDB 
	DEFAULT CHARSET=utf8
	comment = '账户'; 

insert into account (name, balance) values ('zhangsan',1000);

读未提交

设置数据库隔离级别为读未提交:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

1、A窗口查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    1000 |
+----+----------+---------+
1 row in set (0.00 sec)

2、B窗口给zhangsan转钱,不提交

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

mysql> update account set balance=balance+1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    2000 |
+----+----------+---------+
1 row in set (0.00 sec)

可以发现A窗口读到了B未提交的数据,这就是脏读。

读提交

设置数据库隔离级别为读提交:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;


mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.01 sec)

1、A窗口查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    2000 |
+----+----------+---------+
1 row in set (0.00 sec)

2、B窗口给zhangsan转钱,不提交

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

mysql> update account set balance=balance+1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    2000 |
+----+----------+---------+
1 row in set (0.00 sec)

4、B窗口提交事务

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

5、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    3000 |
+----+----------+---------+
1 row in set (0.00 sec)

事务 B 的更新在提交后才能被 A 看到,这是不可重复读,两次读取帐户的余额不同。

可重复读

设置数据库隔离级别为可重复读:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;


mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

1、A窗口查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    3000 |
+----+----------+---------+
1 row in set (0.00 sec)

2、B窗口给zhangsan转钱,不提交

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

mysql> update account set balance=balance+1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    3000 |
+----+----------+---------+
1 row in set (0.00 sec)

4、B窗口提交事务

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

5、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    3000 |
+----+----------+---------+
1 row in set (0.00 sec)

6、A窗口提交事务并查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    4000 |
+----+----------+---------+
1 row in set (0.00 sec)

事务 A 执行过程中看到的数据是一样的,事务B增加了余额并且提交了,事务A读取到的余额还是不变,出现虚读(幻读)。

串行化

设置数据库隔离级别为串行化:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

mysql> show variables like 'transaction_isolation';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set, 1 warning (0.05 sec)

1、A窗口查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    4000 |
+----+----------+---------+
1 row in set (0.00 sec)

2、B窗口给zhangsan转钱

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

mysql> update account set balance=balance+1000 where name='zhangsan';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

发现B窗口无法转账成功,因为A事务没有提交,不能并行执行事务,无法更新。

这里锁的等待时间默认为50s:

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

可以设置为其它值:

set innodb_lock_wait_timeout=30;

串行化隔离级别使用的是共享锁,事务A启动后对数据加了共享锁,事务B不能修改数据,但可以查询:

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    4000 |
+----+----------+---------+
1 row in set (0.00 sec)

3、A窗口给zhangsan转钱

mysql> update account set balance=balance+1000 where name='zhangsan';
Query OK, 1 row affected (31.46 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    5000 |
+----+----------+---------+
1 row in set (0.00 sec)

4、B窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    4000 |
+----+----------+---------+
1 row in set (0.00 sec)

5、A窗口提交事务

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

6、B窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    5000 |
+----+----------+---------+
1 row in set (0.00 sec)

事务隔离实现方式

MySQL日志系统:binlog、redo log和undo log 中简单介绍了InnoDB的多版本并发控制 (multi-version concurrency control,MVCC) 是基于undo log来实现的,保证了事务的隔离性。

InnoDB 引擎的读提交可重复读隔离级别是使用 MVCC技术中的一致性读视图(consistent read view)来实现的。注意:这里提到的视图和查询语句定义的视图不是一个概念。

可重复读隔离级别下,在事务启动时数据库会创建一个视图,在这个事务的整个存在期间都用这个视图。其它事务对同一数据进行操作时,不会影响当前视图。也就是说,在可重复读隔离级别下,事务读取到的数据永远是此事务启动时读到的数据;

读提交隔离级别也使用的是一致性读视图,这个视图会在每个 SQL 语句开始执行的时候创建;

读未提交隔离级别没有视图概念,直接返回记录上的最新值;

串行化隔离级别通过用加锁(共享锁)的方式来实现串行执行,避免并发访问。

MVCC一致性视图

MVCC 通过对数据行创建多个数据版本来实现对数据库的并发控制,实现不加锁也可以进行并发读,达到事务隔离的效果。先来看一个例子(隔离级别为可重复读):

1、A窗口查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    6000 |
+----+----------+---------+
1 row in set (0.00 sec)

2、B窗口查询

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    6000 |
+----+----------+---------+
1 row in set (0.00 sec)

3、C窗口给zhangsan转钱,提交

mysql> update account set balance=balance+1000 where name='zhangsan';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    7000 |
+----+----------+---------+
1 row in set (0.00 sec)

4、B窗口给zhangsan转钱,不提交

mysql> update account set balance=balance+1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    8000 |
+----+----------+---------+
1 row in set (0.00 sec)

5、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    6000 |
+----+----------+---------+
1 row in set (0.00 sec)

6、A窗口提交

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    7000 |
+----+----------+---------+
1 row in set (0.00 sec)

7、B窗口提交

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    8000 |
+----+----------+---------+
1 row in set (0.00 sec)

8、A窗口查询

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |    8000 |
+----+----------+---------+
1 row in set (0.00 sec)

执行流程图如下:

A事务中,事务C修改了余额,A查询到的余额还是6000,这是MVCC的一致性视图(read view)实现的。

B事务中,位置①查询到余额为6000,而位置②查询到的余额为8000。是因为事务B在更新数据时使用了当前读,这避免了幻读。下面介绍它们的工作原理。

事务启动时会生成一个库的快照,事务获得一个唯一的事务 ID(transaction id),事务更新数据时,会生成一个新的数据版本,并且把事务 ID赋值给这个数据版本的事务 ID,记为 row trx_id。

事务 ID是事务启动时向事务系统申请的,也就是在执行第一个操作表的语句时申请:

begin/start transaction;
select * from accrount; 

或者执行以下语句,系统会立刻启动一个事务:

start transaction with consistent snapshot

也就是说,多个事务更新数据表中的某一行记录时,会产生多个版本,每个版本的 row trx_id不同。对某个事务来说,它启动时能够看到所有已经提交的事务结果,之后这个事务执行期间,它不能看到其他事务的更新。

当前事务在启动的瞬间,InnoDB会构造一个事务数组 ,保存启动了但还没提交的事务 ID,最小事务id记为min_trx_id,当前系统已经创建过的最大事务 ID 加 1 记为max_trx_id,是系统应该分配给下一个事务的事务ID。min_trx_id和max_trx_id就构成了当前事务的一致性视图(Read-View)。

读提交和可重复读隔离级别都使用了MVCC的一致性视图,区别在于他们生成Read-View的时机不同。

可重复读是在事务启动时创建视图,之后这个事务的其他查询都用这个视图,所以它从头到尾看到的数据是一样的。

而读提交是在每个 SQL 语句开始执行前都会创建一个新的视图;

下面来分析一下上面的例子中事务A的执行流程:

事务A在查询4时,发现①当前版本为12,而事务A的视图数组为[10, 11],事务A判断12比11大,所以不可见;②接着查找上一个历史版本,发现13还是比11大,不可见;③继续查找上一个版本,row trx_id为10,比11小,是可见的。这一行数据虽然被其它事务修改,但事务A每次查询的结果都是一致的,这就是一致性读。

如果是读提交隔离级别,查询4读到的余额就是7000,因为事务C查询前会创建一个新的视图,事务B没有提交,不可见,而事务C提交了,所以是可见的。

当前读

上面介绍的MVCC一致性视图使用的是快照读,是一种不加锁的非阻塞读;

当前读读取的是记录的最新版本,并对读取的当前记录加锁(Next-Key Lock),保证其他并发事务不能修改这条记录,避免了幻读问题。由于需要对当前记录加锁,会阻塞其他事务的写操作,所以它是一种阻塞读。

下面的SQL语句会使用当前读:

select * from account lock in share mode; # 添加读锁(S 锁,共享锁)
select * from account for update; # 添加写锁(X 锁,排他锁)
insert语句
update语句
delete语句

下面来分析一下前面例子中事务B的执行流程:

事务B在查询2时,使用的是快照读,读取到的余额为6000。但当它更新数据时,就不能在数据版本10上更新了,否则会导致事务C的更新丢失。所以事务B使用的是当前读,读取当前最新的数据版本13,更新后余额变为8000。

在事务B查询时(查询3),自己的版本是 12,最新的数据版本也是 12,是可见的,所以查询到余额为8000。

如果事务C更新余额后没有马上提交,事务B更新时会进行锁等待,直到事务C释放这个锁后事务B才会进行当前读。

小结

本文介绍了MySQL事务隔离性涉及到的一些技术,简单做一个小结。

MySQL InnoDB引擎支持事务,undo log(回滚日志)是实现事务的基础。MVCC通过给行数据生成多个版本,每个数据版本有唯一的 row trx_id,每个事务的一致性视图达到了事务隔离的效果。

读提交和可重复度隔离级别都使用了MVCC的一致性视图,区别在于他们生成Read-View的时机不同。可重复读是在事务启动时创建视图,而读提交是在每个 SQL 语句开始执行前都会创建一个新的视图;

当前读读取的是当前已经提交的最新版本,需对当前记录加锁,是一种阻塞读。

--THE END--

他们只要拒绝相信就行。面对难题,最容易的对策就是拒绝相信它的存在。——艾萨克·阿西莫夫《神们自己》

posted @ 2022-10-13 22:02  测试开发小记  阅读(102)  评论(0编辑  收藏  举报