介绍了事务的ACID特性，MySQL各个隔离级别下的实现方式。

数据库中事务

事务的ACID

原子性（Atomicity）

定义：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行

一致性（Consistency）

定义：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束

隔离性（Isolation)

定义：多个事务并发执行时，一个事务的执行不应影响其他事务的执行

持久性（Durability）

定义：已被提交的事务对数据库的修改应该永久保存在数据库中

MySQL数据库InnoDB引擎中的实现

基于两阶段锁的多版本并发控制（Lock-Based MVCC）

快照读

select * from xxx where

当前读

update delete操作在MySQL内部实现上就是根据where条件读取记录，每条记录依次加锁更新。

insert简单理解有可能触发一次unique key检查，也存在一次读。

update ;

update table_a set column_a='a' where id=1; ---> select column_a from table_a where id=1;

delete;

delete from table_a where id=1; --->. select column_a from table_a where id=1;

insert/replace;

insert into table_a values (…);

select … for update;

select * from table_a where id=1 for update

select … in share mode;

select * from table_a where id=1 in share mode

MVCC对每条记录都会记录版本，包括最新版本和之前修改的一些版本；对于快照读，读取的是本事务可见的版本，可能是最新版本，也可能是某个历史版本；对于当前读，读取的是最新版本，并且同时会对记录加锁，保证记录不会被其他事务修改。

两阶段锁

两阶段：加锁阶段和解锁阶段不重合

时间上：

有例外，在read-committed隔离级别下会讲到

锁的类型

1.记录锁（Record Lock）

InnoDB里的行锁，锁的是索引

InnoDB一般是B+Tree索引：

**主键索引（Primary Key）/聚簇索引（Cluster Index）**叶节点记录了完整的行记录；通过主键索引，就可以获取记录所有的列。

select * from t_table where id=1

**辅助索引/二级索引（Secondary Index）**叶节点记录里主键的位置；通过辅助索引找到记录的列需要先通过辅助索引找到主键索引，再通过主键索引找到记录的列。

锁辅助索引的同时会锁住相关的主键

2.间隙锁（Gap Lock）

间隙：索引结构中能够插入新索引的空隙

间隙锁就是在索引前后加锁，但不包括索引本身。

作用：防止幻读

table_a

update table_a set name=‘a’ where status>3;

(3,+∞)加入GAP锁，防止新纪录插入

3.Next Key Lock

记录锁+间隙锁

锁的模式

共享锁（S）锁，排他锁（X）锁

**共享锁：**共享锁的作用通常用于在事务中读取一条行记录后，不希望它被别的事务锁修改，但所有的读请求产生的LOCK_S锁是不冲突的。

常见场景：

1.select * … in share mode

2.外键检查

3.insert into 检查duplicate key时

**排他锁：**排他锁的作用主要是避免对同一条记录的并发修改。通常对于UPDATE或者DELETE操作，或者类似SELECT … FOR UPDATE操作，都会对记录加排他锁。

未提交读 Read-Uncommitted

实际不会使用，忽略

提交读 Read-Committed

t_test_01表结构

1
2
3
4
5
6
7
8
9

CREATE TABLE `t_test_01` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`) USING BTREE,
  KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4194271 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

数据400W行，idx_status区分度也很高。

为了模拟两个并发的transaction，在naveicat之类的客户端中打开两个页面（执行的查询是两个session）；同时为了模拟语句执行的先后次序，语句中有select sleep(10)，表示当前执行语句的线程暂停10s。

不可重复读

实际语句：

1
2
3
4
5
6
7

set tx_isolation='read-committed';
select @@tx_isolation;
start TRANSACTION;
select * from t_test_01 where status=8;
select sleep(10);
select * from t_test_01 where status=8;
commit;

1
2
3
4
5

set tx_isolation='read-committed';
select @@tx_isolation;
start transaction;
update t_test_01 set name="newName" where status=8;
commit;

执行顺序：

结果：

语句2和语句6得到的结果不一致，语句6读到的name是被transactionB修改的；同一个transaction相同语句读到的结果不一致。

RC级别下update … where 加锁后释放锁

t_test_01表结构

1
2
3
4
5
6
7
8
9

CREATE TABLE `t_test_01` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`) USING BTREE,
  KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4194271 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

数据400W行，idx_status区分度也很高

code为唯一索引，存在code=‘9’，不存在code=‘9’ and version=1的情况

实际语句：

1
2
3
4
5
6
7

#transactionA
set tx_isolation='read-committed';
select @@tx_isolation;
start TRANSACTION;
	select * from t_test_version where code='9' and version=1 for update;
	select sleep(10);
commit;

1
2
3
4
5
6

#transactionB
set tx_isolation='read-committed';
select @@tx_isolation;
start TRANSACTION;
	select * from t_test_version where code='9' for update;
commit;

执行顺序：

结果：

语句3中transactionB获取锁阻塞，说明select * from t_test_version where code='9' and version=1 for update;未找到记录但是对code='9’的记录加锁了。

status为非唯一索引，存在status=‘9’，不存在status=‘9’ and version=1的情况

实际语句：

1
2
3
4
5
6

set tx_isolation='read-committed';
select @@tx_isolation;
start TRANSACTION;
	select * from t_test_version where status=9 and version=1 for update;
	select sleep(10);
commit;

1
2
3
4
5

set tx_isolation='read-committed';
select @@tx_isolation;
start TRANSACTION;
	select * from t_test_version where status=9 for update;
commit;

执行顺序：

结果：

语句3中transactionB获取锁未阻塞，感觉上select * from t_test_version where status=9 and version=1 for update;未对status=9的记录加锁一样，实际上MySQL对这种非唯一索引加锁后，如果发现不符合where条件会立即释放锁，这也是稍微违背两阶段加锁原则的地方。

可重复读 Repeatable-Read

无不可重复读

实际语句：

1
2
3
4
5
6
7

set tx_isolation='repeatable-read';
select @@tx_isolation;
start TRANSACTION;
select * from t_test_01 where status=8;
select sleep(10);
select * from t_test_01 where status=8;
commit;

1
2
3
4
5

set tx_isolation='repeatable-read';
select @@tx_isolation;
start transaction;
update t_test_01 set name="newName" where status=8;
commit;

执行顺序：

结果：

语句2和语句6的执行结果一样，虽然TransactionB在中间修改了name。

快照读不存在幻读

实际语句：

1
2
3
4
5

start TRANSACTION;
select * from t_test_01 where status>=8 and status<=10;
select sleep(10);
select * from t_test_01 where status>=8 and status<=10;
commit;

1
2
3

start transaction;
insert into t_test_01(name, code, status) values("name20000002", 200000002, 9);
commit;

执行顺序：

结果：

语句2和语句6读取的结果行数一样；MVCC读取快照，第一次读取之后，后面读取的版本都和第一次读的版本一致，只有在数据被本事务更新后会读取最新版本，所以快照读一致，不存在幻读。

当前读不存在幻读

实际语句：

1
2
3
4
5

start TRANSACTION;
select * from t_test_01 where status>=8 and status<=10 for update;
select sleep(10);
select * from t_test_01 where status>=8 and status<=10 for update;
commit;

1
2
3

start transaction;
insert into t_test_01(name, code, status) values("name20000003", 200000003, 9);
commit;

执行顺序：

结果：

TransactionB中的语句4执行被阻塞了，TransactionA commit之后4才开始执行；通过加gap lock间隙锁保证当前读之间一致，不存在幻读。

RR级别中存在的幻读

场景1

实际语句：

1
2
3
4
5
6
7

start TRANSACTION;
select * from t_test_01 where status=9;
select sleep(10);
select * from t_test_01 where status=9;
select * from t_test_01 where status=9 for update;
select * from t_test_01 where status=9;
commit;

1
2
3

start transaction;
update t_test_01 set `name`="newName9" where status=9;
commit;

执行顺序：

结果：

TransactionA每次读到的数据都是name9，并没有受TransactionB影响。

场景2

实际语句：

1
2
3
4
5
6
7

start TRANSACTION;
select * from t_test_01 where status=9;
select sleep(10);
select * from t_test_01 where status=9;
update t_test_01 set code=concat(code,'0') where status=9;
select * from t_test_01 where status=9;
commit;

1
2
3

start transaction;
update t_test_01 set `name`="name9" where status=9;
commit;

执行顺序

结果:

和场景1仅语句7有差别：场景1中select ** for update，场景2中 update **；两种场景中，结果不一样。场景2中语句8的结果受到了TransactionB的影响。

可以说RR级别中的幻读存在于快照读和当前读之间不一致，即：读到的数据和更新时数据不一致。

可序列化

不存在幻读

实际中也不会使用，类似于RR级别基础上对所有快照读加上 in share mode，读写会冲突

总结

MySQL实现的隔离级别和SQL规范中的略微不同，主要体现在RR级别下的幻读

• ← Previous Post
• Next Post →