数据库事务ACID/隔离级别

参考博客

1. 事务的定义

事务是用户定义的一个数据库操作序列。这些操作要么全执行，要么全不执行，是一个不可分割的工作单元。在关系型数据库中，事务可以是一条SQL语句，也可以是一组SQL语句或整个程序。

程序和事务是两个概念。一般地将，一个程序包含多个事务。
事务的开始和结束可以由用户显示控制。如果用户未显示定义事务，则有数据库管理系统按默认规定划分事务。在SQL中，定义事务的语句：

begin/start transaction -- 开始
commit -- 提交
rollback -- 回滚

事务通常以begin/start transaction开始，以commit或rollback结束。

• begin/start transaction 表示启动一个事务
• commit 表示提交，即提交事务的所有操作，将事务中所有对数据库的增删改写回到磁盘上的物理数据库中去，事务正常结束；
• rollback表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续执行，将事务中所有对数据库已完成的增删改操作全部撤销，回滚到事务开始时的状态；

2. ACID

数据库事务特点：A（原子性） C（一致性） I（隔离性） D（持久性）

• 原子性：一个事务必须被视为一个不可分割的最小单位，要不全部提交成功，要么全部失败回滚。
• 一致性：数据库总是从一个一致性的状态转换到另外一个一致性状态，不会部分数据状态改变了部分状态没有改变。
• 隔离性：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。这个和数据库的隔离级别有关，所以只能通常来说。
• 持久性：一旦事务提交，则其所做的修改会被永久保存到数据库中。

3. 并发事务带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

4. 隔离级别

隔离级别要比想象的要复杂，四种隔离级别，每一种隔离级别规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离可以执行更高并发，系统开销也更低。

不同的RDMS厂商的默认隔离级别不一样，PostgreSQL、Oracle、Sql Server为已提交读(Read committed)，MySQL为可重复读(Repeatable read)。

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

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

脏读：事务T1读取了另一个事务T2未提交的insert、update、delete的数据，因为事务T2没有提交事务T1读取到的数据为脏数据，称为脏读。
不可重复读：事务T1多次读取同一数据期间，另一个事务T2对此数据进行update、delete操作；T1多次读取的数据不一致，称为不可重复读。
幻读：一个事务T1通过检索条件，读取N条数据，另一个事务T2写入了1条满足T1检索条件的数据，后续T1对检索的数据进行修改时，发现影响的数据记录数N+1，称为幻读。

查询隔离级别

Mysql 查询

-- Mysql 查询
-- Server version: 8.0.21 MySQL
-- 方式一
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

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

-- 查询全局会话事务隔离级别
select @@global.transaction_isolation;

-- 查询会话事务隔离级别
select @@session.transaction_isolation;

PostgreSQL 查询

-- PostgreSQL 查询
-- 查询postgreSQL默认隔离级别
vulture=# show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 行记录)

-- 检查当前隔离级别
vulture=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 行记录)

-- 修改当前事务的隔离级别，需要开启事务执行
vulture=# begin;
BEGIN
vulture=# set transaction isolation level repeatable read ;
SET
vulture=# show transaction_isolation ;
 transaction_isolation
-----------------------
 repeatable read
(1 行记录)

示例

-- 创建表
create table user (id int auto_increment,name varchar(10),primary key(id)) engine=innodb;

-- 查看全局事务隔离级别
select @@global.transaction_isolation;
-- 查看会话事务隔离级别
select @@session.transaction_isolation;
-- 设置会话事务隔离级别
set session transaction isolation level read uncommitted
set session transaction isolation level read committed
set session transaction isolation level repeatable read 
set session transaction isolation level serializable

• 未提交读(Read uncommitted)

	Session1	Session2
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
会话2设置隔离级别		set session transaction isolation level read uncommitted;
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
会话2设置隔离级别		set session transaction isolation level read uncommitted;
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
开启事务	begin；	begin；
查看数据	select * from user;	select * from user;
会话1写入数据	insert into user (name) values ('a');
查看数据	select * from user;	select * from user;
会话1事务回滚	rollback;
会话2查看数据		select * from user;
会话2事务提交		commit;

将会话2的事务隔离级别设置为未提交读，会话2中的事务能够读取会话1事务中未提交的数据，产生脏读；
未提交读最低的隔离级别，允许读取并发事务尚未提交的数据，会出现脏读、不可重复读、幻读

• 已提交读(Read committed)

-- 写入数据
insert into user (name) values ('a');

	Session1	Session2
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
设置会话2隔离级别		set session transaction isolation level read committed;
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
开启事务	begin；	begin；
查看数据	select * from user;	select * from user;
会话1更新数据	update user set name='b' where id = 2;
查看数据	select * from user where id = '2';	select * from user where id = '2';
会话1事务提交	commit；
会话2查看数据		select * from user where id = '2';
会话2事务提交		commit；

将会话2的事务隔离级别设置为已提交读：会话2事务未能读取会话1事务未提交的数据，解决了脏读问题；当会话1事务提交后，会话1再次读取数据，读取到会话1事务修改的数据，产生了不可重复读的问题
已提交读允许读取并发事务尚已提交的数据，解决脏读问题，仍会出现不可重复读、幻读。

• 可重复读(Repeatable read)

-- 写入数据
insert into user (name) values ('aa');

	session1	session2
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
设置会话2隔离级别		set session transaction isolation level repeatable read;
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
开始事务	begin;	begin;
查看数据	select * from user where id >1;	select * from user where id >1;
会话1更新数据	update user set name='e' where id=2;
查看数据	select * from user where id >1;	select * from user where id >1;
会话1事务写入数据	insert into user (name) values ('12345');
查看数据	select * from user where id >1;	select * from user where id >1;
会话1事务提交	commit;
会话2查看数据		select * from user where id >1;
会话2查看数据		select * from user where id >3;
会话2更新数据		update user set name='1111' where id>3;
会话2事务提交		commit;
查看数据	select * from user where id >1;	select * from user where id >1;

将会话2的事务隔离级别设置为可重复读：会话2事务未能读取到会话1事务修改的id=2的数据，当会话1事务提交后，会话2事务再次读取仍未读取到会话1事务修改id=2的数据，解决了不可重复读的问题;但会话1事务读取id>2的数据为1条，更新id>2的数据Rows matched: 2 Changed: 2，影响2条数据产生幻读问题，可通过锁机制Next-Key Lock解决。

• 可串行化(Serializable)

此隔离级别很简单，读操作加共享锁，写操作加排他锁，读写互斥。使用悲观锁的理论，实现简单，避免了脏读、不可重复读、幻读，并发能力降低。

	session1	session2
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
设置会话2隔离级别		set session transaction isolation level serializable;
查看事务隔离级别	select @@session.transaction_isolation;	select @@session.transaction_isolation;
会话2开启事务		begin;
会话2查看数据		select * from user where id = 2;
会话1查看数据	select * from user where id = 2;
会话1查看数据	select * from user where id=2 lock in share mode;
会话1查看数据	select * from user where id=2 for update; 等待会话2 提交事务
会话2修改数据		update user set name='f' where id=2;
会话1查看数据	select * from user where id=2;
会话1查看数据	select * from user where id=2 lock in share mode;

5. 引申（LOCK IN SHARE MODE） 和 （FOR UPDATE）

• SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

• SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作，除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。

对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。