对数据进行并发操作时,事务可以确保数据的完整性,在银行、证券交易等业务场景需要用到它,本文将介绍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 语句开始执行前都会创建一个新的视图;
当前读读取的是当前已经提交的最新版本,需对当前记录加锁,是一种阻塞读。
他们只要拒绝相信就行。面对难题,最容易的对策就是拒绝相信它的存在。——艾萨克·阿西莫夫《神们自己》