了解事务之前,先来看看数据库为什么需要有事务,假设没有事务会有什么影响?假设我们有一个银行账户系统,表结构如下:
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 5000 |
+------+--------+---------+
2 rows in set (0.00 sec)
在某个时刻,张三要向李四买件商品,因此要给李四转账 100。数据库中应该有如下两个操作,将张三的余额扣掉 100,给李四的余额增加 100。
mysql> update atm set abalance=balance-100 where aname = 张三;
mysql> update atm set abalance=balance+100 where aname = 李四;
那么如果张三的余额扣款成功,但是李四的余额没有增加成功,会出现什么后果? 张三明明转了账,但是李四却没收到钱。
如果张三的余额扣款失败,但是李四的余额却增加成功呢? 张三一分钱没花,就买了东西。
所以数据库引入事务的主要目的是把数据库会从一种一致状态转换到另一种一致状态,数据库提交工作时可以确保要么所有修改都保存,要么所有修改都不保存。
一般来说,事务满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。事务的本质其实就是一系列数据库操作,只不过这些数据库操作符合 ACID 的特性而已。
并发事务中可能会出现以下问题:
脏读、不可重复读和幻读其实都是数据库一致性的问题,必须由数据库提供一定的事务隔离机制来解决。MySQL 默认的事务隔离级别是可重复读。
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
读未提交(Read Uncommitted) | 可能 | 可能 | 可能 |
读已提交(Read Committed) | 不可能 | 可能 | 可能 |
可重复读(Repeatable Read) | 不可能 | 不可能 | 可能 |
可串行化(Serializable) | 不可能 | 不可能 | 不可能 |
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。 同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。
可以使用下面两种语句来开启事务,开启事务后可以继续写若干条语句,这些语句都属于刚刚开启的这个事务。
# 方式一
mysql> begin;
mysql> 加入事务的语句...
# 方式二
mysql> start transaction;
mysql> 加入事务的语句...
START TRANSACTION 语句与 BEGIN 语句有相同的功效,都标志着开启一个事务。相较于 BEGIN 语句,START TRANSACTION 语句后面可以跟随几个参数:
例如开启读写事务和一致性读,可以这样写:
mysql> start transaction read write, with consistent snapshot;
当编写完事务执行的语句,就可以使用 COMMIT 命令来提交事务了。
mysql> commit;
COMMIT 语句就代表一个事务,例如前面的转账的例子可以这样写:
mysql> begin;
mysql> update atm set abalance=balance-100 where aname = 张三;
mysql> update atm set abalance=balance+100 where aname = 李四;
mysql> commit;
如果我们写了几条语句之后发现前面某条语句写错了,可以使用下面这个语句将数据库恢复到执行事务之前的样子:
mysql> rollback;
savepoint savepoint_name; //声明一个 savepoin
rollback to savepoint_name; // 回滚到savepoint
release savepoint savepoint_name; // 删除指定保留点
下面是使用保存点的例子:
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 5000 |
| 3 | 王五 | 10000 |
| 4 | 马六 | 20000 |
+------+--------+---------+
4 rows in set (0.01 sec)
mysql> insert into account values(5,'赵七',3000);
Query OK, 1 row affected (0.01 sec)
# 创建保存点
mysql> savepoint t_1;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 5000 |
| 3 | 王五 | 10000 |
| 4 | 马六 | 20000 |
| 5 | 赵七 | 3000 |
+------+--------+---------+
5 rows in set (0.01 sec)
mysql> insert into account values(6,'王八',7000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 5000 |
| 3 | 王五 | 10000 |
| 4 | 马六 | 20000 |
| 5 | 赵七 | 3000 |
| 6 | 王八 | 7000 |
+------+--------+---------+
6 rows in set (0.01 sec)
# 回滚到保存点
mysql> rollback to t_1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 5000 |
| 3 | 王五 | 10000 |
| 4 | 马六 | 20000 |
| 5 | 赵七 | 3000 |
+------+--------+---------+
5 rows in set (0.00 sec)
MySQL 中事务默认的隔离级别是可重复读(repeatable read),可以通过以下两种方式修改事务默认的隔离级别。
# 方式一
# level值
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化
# 修改全局隔离级别(修改完要重新登录才生效)
set global transaction isolation level <level值>;
# 修改当前会话隔离级别(修改完立即生效,重新登录失效)
set session transaction isolation level <level值>;
# 方式二
# 隔离级别
read-uncommitted 读未提交
read-committed 读已提交
repeatable-read 可重复读
serializable 串行化
# 修改全局隔离级别(修改完要重新登录才生效)
set @@global.tx_isolation ='隔离级别';
# 修改当前会话隔离级别(修改完立即生效,重新登录失效),默认的@tx_isolation是当前会话
set @@session.tx_isolation ='隔离级别';
# 查看当前会话和全局的隔离级别
mysql> select @@global.tx_isolation,@@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ | SERIALIZABLE |
+-----------------------+------------------------+
1 row in set, 2 warnings (0.10 sec)
# @tx_isolation查的是当前会话的隔离级别
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set, 1 warning (0.03 sec)
MySQL 中有一个系统变量 autocommit,用于自动提交事务。
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
可以看到它的默认值为 ON。也就是说在默认情况下,如果不显示使用 START TRANSACTION 或者 BEGIN 语句开启一个事务,那么每一条语句都算一个独立的事务,这种特性称为事务的自动提交。
如果我们想关闭自动提交的功能,有下面两种方法:
mysql> set autocommit=OFF;
当使用 START TRANSACTION 或者 BEGIN 语句开启了一个事务,或者把系统变量 autocommit 的值设置为 OFF 时,事务就不会进行自动提交。如果我们输入了某些语句,且这些语句会导致之前的事务悄悄地提交掉(就像输入了 COMMIT语句一样),那么这种因为某些特殊的语句而导致事务提交的情况称为隐式提交。会导致事务隐式提交的语句有下面这些:
在 MySQL 中,事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统,但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务,这也是 MyISAM 被 InnoDB 取代的重要原因之一。
首先创建一张 account 表并插入一些数据。
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 (1, 'lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES (2 ,'hanmei', '16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES (3, 'lucy', '2400');
设置客户端 A 和客户端 B 的事务隔离级别为 read uncommitted(读未提交),并且一起开启事务。
set tx_isolation='read-uncommitted';
begin;
(1)客户端 A 查询表 account 的初始值:
(2)在客户端 A 的事务提交之前,在客户端 B,更新表 account:
(3)此时虽然客户端 B 的事务还没提交,但是客户端 A 就已经可以查询到客户端 B 已经更新的数据:
(4)一旦客户端 B 的事务因为某种原因回滚,所有的操作都将会被撤销,那客户端 A 查询到的数据其实就是脏数据:
(5)在客户端 A 执行更新语句 update account set balance=balance-50 where id=1,lilei 的 balance 没有变成 350,居然是 400,是不是很奇怪,数据不一致啊,如果你这么想就太天真了,在应用程序中,我们会用 400-50=350,但是并不知道其他会话回滚了,要想解决这个问题可以采用读已提交的隔离级别。
以上示例的执行顺序如下表所示:
客户端A | 客户端B |
---|---|
begin; | begin; |
select * from account;(lilei的balance为450) | |
update account set balance=balance-50 where id=1; | |
select * from account;(lilei的balance为400) | |
select * from account;(lilei的balance为400,出现了脏读的问题) | |
rollback; | |
update account set balance=balance-50 where id=1; | |
select * from account;(lilei的balance为400) | |
rollback |
设置客户端 A 和客户端 B 的事务隔离级别为 read committed(读已提交),并且一起开启事务。
set tx_isolation='read-committed';
begin;
(1)客户端 A 查询表 account 的初始值:
(2)在客户端 A 提交事务之前,在客户端 B 更新表 account:
(3) 此时客户端 B 的事务还没提交,客户端 A 不能查询到 B 已经更新的数据,解决了脏读问题:
(4)客户端 B 提交事务:
(5)客户端 A 再次执行查询,结果与上一次的查询结果不一致,即产生了不可重复读的问题:
以上示例的执行顺序如下表所示:
客户端A | 客户端B |
---|---|
begin; | begin; |
select * from account;(lilei的balance为450) | |
update account set balance=balance-50 where id=1; | |
select * from account;(lilei的balance为400) | |
select * from account;(lilei的balance为450) | |
commit; | |
select * from account;(lilei的balance为400,出现了不可重复读) | |
commit; |
设置客户端 A 和客户端 B 的事务隔离级别为 repeatable read(可重复读),并且一起开启事务。
set tx_isolation='repeatable-read';
begin;
(1)客户端 A 查询表 account 的初始值:
(2)在客户端 A 提交事务之前,在客户端 B 更新表 account 并提交:
(3)在客户端 A 查询表 account 的所有记录,与步骤(1)查询结果一致,没有出现不可重复读的问题:
(4)在客户端 A,接着执行 update account set balance=balance-50 where id=1,balance 没有变成 400-50=350,因为 lilei 的 balance 值用的是步骤(2)中的 350 来算的,所以是 350-50=300,数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了 MVCC(multi-version concurrency control) 机制,select 操作不会更新版本号,是快照读(历史版本);insert、update 和 delete 会更新版本号,是当前读(当前版本)。
(5)重新打开客户端 B,插入一条新的数据后提交:
(6)在客户端 A 查询表 account 的所有记录,没有查出新增数据,所以没有出现幻读。
(7)在客户端 A 执行 update account set balance=888 where id=4; 能更新成功,再次查询能查到客户端 B 新增的数据,出现了幻读。
客户端A | 客户端B |
---|---|
begin; | begin; |
select * from account;(lilei的balance为400) | |
update account set balance=balance-50 where id=1; | |
select * from account;(lilei的balance为350) | |
commit; | |
select * from account;(lilei的balance为400) | |
update account set balance=balance-50 where id=1; | |
select * from account;(lilei的balance为300) | |
begin; | |
insert into account values(4,'lily',700); | |
select * from account;(能查到新增的lily这行) | |
commit; | |
select * from account;(查不到新增的lily这行) | |
update account set balance=888 where id=4; | |
select * from account;(能查到新增的lily这行,出现了幻读) | |
commit; |
串行化这种隔离级别并发性极低,开发中很少会用到。 设置客户端 A 和客户端 B 的事务隔离级别为 serializable(串行化),并且一起开启事务。
set tx_isolation='serializable';
begin;
(1)客户端 A 查询表 account id=1 这一行的初始值:
(2)客户端 B 更新相同的 id=1 的记录会被阻塞等待,更新 id=2 的记录可以成功,说明在串行模式下 innodb 的查询也会被加上行锁(前提是 where 后面的字段有索引,不然行锁会升级为表锁)。客户端 A 在查询 id=1 的时候,会给这一行加上读锁,其他客户端 B 可以查询,但不能修改这行。
(3)客户端 B 在修改 id=2 这一行时,会给这一行加上写锁,其他客户端不能查询也不能修改这一行。
在 MySQL 中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4,在回滚日志里面就会有类似下面的记录。
当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。如图中看到的,在视图 A、B、C 里面,这一个记录的值分别是 1、2、4,同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC,Multiversion Concurrency Control)。MySQL 只在读已提交和可重复读隔离级别下实现了 MVCC 机制。
以下是关于 MVCC 的一些概念:
在可重复读隔离级别,当事务开启后,执行第一条查询 SQL 时会生成当前事务的一致性视图 read-view,该视图在事务结束之前都不会变化。
在读已提交隔离级别,当事务开启后,在每次执行查询 SQL 时都会重新生成一致性视图。
InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。
数组里面事务 ID 的最小值(min_id)记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位(max_id)。
这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。
MVC 机制的实现就是通过 read-view 机制与 undo 版本链比对机制,使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。如果该版本的数据不可见,则根据版本链指针找到上一个版本的数据继续比对。
版本链比对规则:
为什么黄色部分会包含已提交的事务呢?因为高水位的定义是事务创建时所有未提交的事务 ID 的最大值+1,但并不是小于高水位大于低水位的事务就都没有提交。
对于删除的情况可以认为是 update 的特殊情况,会将版本链上最新的数据复制一份,然后将 trx_id 修改成删除操作的 trx_id,同时在该条记录的头信息(record header)里的(deleted_flag)标记位写上 true,来表示当前记录已经被删除,在查询时按照上面的规则查到对应的记录如果 delete_flag 标记位为 true,意味着记录已被删除,则不返回数据。
注意:BEGIN/START TRANSACTION 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句(查询或修改)时,事务才真正启动,才会向 MySQL 申请事务 ID,MySQL 内部是严格按照事务的启动顺序来分配事务 ID 的。
接下来说明的例子是在可重复读隔离级别下的。
我们假设:
这样,事务 A 的视图数组就是[99,100], 事务 B 的视图数组是[99,100,101], 事务 C 的视图数组是[99,100,101,102]。
从图中可以看到,第一个有效更新是事务 C,把 name 从 xiaoming1 改成了 xiaomign2,事务 C 已提交。这时候,这个数据的最新版本的 row trx_id 是 102,而 80 这个版本已经成为了历史版本。
第二个有效更新的是事务 B,把 name 从 xiaoming2 改成了 xiaoming3,事务 B 未提交。这个数据的最新版本(即 row trx_id)是 101,而 102 又成为了历史版本。
现在事务 A 要来读数据了,它的视图数组是[99,100]。当然了,读数据都是从当前版本读起的。所以,事务 A 查询语句的读数据流程是这样的:
这样执行下来,虽然期间这一行数据被修改过,但是事务 A 不论在什么时候查询,看到这行数据的结果都是一致的,所以我们称之为一致性读。
按照前面的分析方法很容易让人头晕,有一个更简单的方法来判断数据是否可见。一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:
现在,我们用这个规则来判断上面的查询结果,事务 A 的查询语句的视图数组是在事务 A 启动的时候生成的,这时候:
如果事务 A 自己更新了 name=xiaoming4,那么事务 A 去读 name 的值就应该是 xiaoming4,因为自己更新是当前读。自己改的值自己总得认吧!
总结一下:
在 MySQL 读取数据时可以按照是否使用锁定读来区分当前读和快照读:
在读提交和可重复读两种事务隔离级别下,普通的 select 操作使用快照读,不会对数据加锁,也不会被事务阻塞。
除了 update 语句外,select 语句如果加锁,也是当前读。下面这两个 select 语句,就是分别加了读锁(S 锁,共享锁)和写锁(X 锁,排他锁)。
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;