事务的特性以及隔离级别

最新推荐文章于 2021-08-26 21:53:20 发布

朝闻道-夕死可矣

最新推荐文章于 2021-08-26 21:53:20 发布

阅读量748

点赞数 1

分类专栏： DB&SQL

本文链接：https://blog.csdn.net/jc_benben/article/details/55258857

版权

DB&SQL 专栏收录该内容

70 篇文章 1 订阅

订阅专栏

事务特性

⑴ 原子性（Atomicity）
　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚

⑵ 一致性（Consistency）
　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

⑶ 隔离性（Isolation）
　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

⑷ 持久性（Durability）
　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

隔离级别

多个事务同时访问数据库时候，会发生下列5类问题，包括3类数据读问题（脏读，不可重复读，幻读），2类数据更新问题（第一类丢失更新，第二类丢失更新）：
⑴ 脏读
　　脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的

⑵ 不可重复读
不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果

⑶ 幻读
幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。
而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读

(4)第一类丢失更新：A事务撤销时，把已提交的B事务的数据覆盖掉

撤销一个事务的时候，把其它事务已提交的更新数据覆盖了。这是完全没有事务隔离级别造成的。如果事务1被提交，另一个事务被撤销，那么会连同事务1所做的更新也被撤销

(5)第二类丢失更新：A事务提交时，把已提交的B事务的数据覆盖掉

它和不可重复读本质上是同一类并发问题，通常将它看成不可重复读的特例。当两个或多个事务查询相同的记录，然后各自基于查询的结果更新记录时会造成第二类丢失更新问题。每个事务不知道其它事务的存在，最后一个事务对记录所做的更改将覆盖其它事务之前对该记录所做的更改

不可重复读和幻读区别：

不可重复读在于修改和删除，幻读在于两次查询增加了记录--新增

从控制的角度来看, 两者的区别就比较大
对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录

避免不可重复读需要锁行就行
避免幻影读则需要锁表

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力

上面是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题，具体参考：

https://blog.csdn.net/jc_benben/article/details/82691778

https://blog.csdn.net/jc_benben/article/details/83344547

数据库想要在“合适”的时机阻塞住数据库操作，那么首先要定义好怎么样的时机算是“合适”，因为各个系统支持的业务千差万别，对数据的实时性和有效性的要求也不同。于是数据库理论中就提出了封锁级别的概念，对不同的同步要求采用不同的封锁级别。

三级封锁协议：

一级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。一级封锁协议可以防止丢失修改，并保证事务T是可恢复的。使用一级封锁协议可以解决丢失修改问题。在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，它不能保证可重复读和不读“脏”数据。
二级封锁协议：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后方可释放S锁。二级封锁协议除防止了丢失修改，还可以进一步防止读“脏”数据。但在二级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。
三级封锁协议：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。三级封锁协议除防止了丢失修改和不读“脏”数据外，还进一步防止了不可重复读

三级封锁协议反映在实际的数据库系统上，就是四级事务隔离机制。总的来说，四种事务隔离机制就是在逐渐的限制事务的自由度，以满足对不同并发控制程度的要求.

现在来看看数据库为我们提供的四种隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生

在Read Uncommitted策略下，数据库遵循一级封锁协议，只对修改数据的并发操作做限制。一个事务不能修改其他事务正在修改的数据，但可以读取到其他事务中尚未提交的修改，这些修改如果未被提交，将会成为脏数据

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生

在Read committed策略下，数据库遵循二级封锁协议，只允许读取已经被提交的数据，反过来讲，如果一个事务修改了某行数据且尚未提交，而第二个事务要读取这行数据的话，那么是不允许的。在MySql的InnoDB下，虽然这种操作不被允许，但MySQL不会阻塞住数据的查询操作，而是会查询出数据被修改之前的备份，返回给客户端。MySQL的这种机制称为MVCC（多版本并发控制），就是说数据库在事务并发的过程中对数据维护多个版本，使得不同的事务对不同的数据版本进行读写（MVCC的实现参见引用中的文章）。这样的机制反映在应用中就是，在任何时候对数据库查询总是可以得到数据库中最近提交的数据。为被提交的脏数据被隔离起来，无法被查询到，即防止脏读发生,即在set autocommit= 0或者START TRANSACTION状态下select表的内容不会改变

③ Read committed (读已提交)：可避免脏读的发生

Repeat Read又比Read Committed更加严格一点，但仍然是在二级封锁协议的范畴，只是读取过程受到更多MVCC的影响。在Read Committed下，允许一个事务中多次相同查询得到不同的结果，就是所谓的不可重复读问题。这在一些应用中是允许的，所以oracle、SQL server上默认这一隔离级别，但MySQL没有，它默认Repeat Read级别。在这一级别下，有赖于MVCC，同一个事务中的查询只能查到版本号不高于当前事务版本的数据，即事务只能看到该事务开始前或者被该事物影响的数据。反过来说，这一级别下，不允许事务读取在该事务开始后新提交的数据。即防止了不可重复读的发生。

依靠上面的机制，已经做到了在事务内数据内容的不变，但是不能保证多次查询得到的数据数量一致。因为在一个事务执行的过程中别的事务完全可以执行数据插入，当插入了刚好符合查询条件的数据时，就会引发数据查询结果集增加，引发幻读。还有一种情况就是，如果一个事务想插入一条数据，而另一个事务已经插入了含有相同主键的数据，那么当前事务也会被阻塞，并最终执行失败，虽然当前事务根本无法查询到这一条数据，这也是一种幻读。InnoDB提供的间隙锁机制可以在一定程度上防止幻读的发生

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证

最强事务隔离机制Serializable，它遵循三级封锁协议，使得所有的事务必须串行化执行，只要有事务在对表进行查询，那么在此事务提交前，任何其他事务的修改都会被阻塞。这解决了一切并发问题，但会造成大量的等待、阻塞甚至死锁，使系统性能降低

在任何一种隔离机制下，都是不允许一个事务删除或修改另一个事务影响过而未提交的数据的。因为事务增、删、改数据以后，会在该行加上排它锁，排它锁会阻塞其他事务再次对该行数据操作。也正是由于排它锁的存在，这四种隔离机制都不会出现任何一种更新丢失的现象，因为一条信息根本不允许第二个事务进行修改

四种隔离级别表现的现象：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	允许	允许	允许
Read committed		允许	允许
Repeatable read			允许
Serializable

两段封锁协议

串行调度： 多个事务依次串行执行，且只有当一个事务的所有操作都执行完后才执行另一个事务的所有操作。只要是串行调度，执行的结果都是正确的。

并行调度： 利用分时的方法同时处理多个事务。但是并行调度的调度结果可能是错误的，可能产生不一致的状态，包括有：丢失修改，不可重复读和读脏数据

two-phase locking protocol 2PL协议，两段封锁协议

在一个事务中，如果加锁动作都在释放锁动作之前称此事务为两段事务。上述加锁的限制称两段封锁协议。两段事务可截然为两段：拥有的锁增长阶段growing phase），拥有的锁缩减段段（shrinking phase）。两段事务中，一旦开始释放第一个锁之后，再不准对任何数据对象加锁

第一阶段是获得封锁的阶段，称为扩展阶段：其实也就是该阶段可以进入加锁操作，在对任何数据进行读操作之前要申请获得S锁，在进行写操作之前要申请并获得X锁，加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。就是加锁后就不能解锁了。
第二阶段是释放封锁的阶段，称为收缩阶段：当事务释放一个封锁后，事务进入封锁阶段，在该阶段只能进行解锁而不能再进行加锁操作

数据库的实现

各个数据库实现的隔离级别不太一样，并且支持的隔离级别也不一样。
比如oracle默认的隔离级别是Read committed（Oracle数据库支持READ COMMITTED 和 Serializable这两种事务隔离级别），
mysql的是Repeatable read
在oracle中，
设置一个事务的隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

设置增个会话的隔离级别

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

查询：
发起一个事务查询：

select sid,serial#,flag,
  CASE WHEN bitand(FLAG,268435456) = 0 THEN 'SERIALIZABLE'
                                       ELSE 'READ COMMITTED'
                                       END AS ISOLATIONLEVEL
  from V$transaction t,v$session s
  where t.addr=s.taddr
  AND   audsid = USERENV('SESSIONID');

mysql中，查看当前的事务级别

select @@global.tx_isolation,@@tx_isolation; -- 引擎隔离级别，会话隔离级别

或者MySQL8中：

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

设置：
set [glogal | session] transaction isolation level 隔离级别名称;
比如：

set  session  transaction isolation level read uncommitted;

-- 或者set session tx_isolation='READ-UNCOMMITTED';

在配置文件配置隔离级别：

transaction_isolation=READ-COMMITTED

参考：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

常用数据库阻塞语句查询

MSSQL

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st --系统里还存在的事务
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er 
ON st.session_id = er.session_id
AND er.session_id IS NULL
---- 杀死死锁进程
---- kill session_id

oracle：

--修改了数据，但是未提交的Session,选择WAIT_CALSS='Idle'，也就是Session处于休息状态，但是有锁定的表
SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.LOCKED_MODE,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE
FROM V$SESSION A
INNER JOIN V$LOCKED_OBJECT B
ON A.SID=b.SESSION_ID
INNER JOIN DBA_OBJECTS C
ON B.OBJECT_ID=c.OBJECT_ID
WHERE A.WAIT_CLASS='Idle'
AND A.SECONDS_IN_WAIT>10/*SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒*/
-- ALTER SYSTEM KILL SESSION 'SID,SERIAL#'

MySQL，这里是5.7

SELECT * FROM sys.session WHERE db='t';
SELECT * FROM sys.processlist WHERE db='t';
SELECT *FROM information_schema.innodb_trx t;
SELECT *FROM information_schema.processlist t WHERE t.db='t';
-- kill id

以下摘抄网络：

什么是事务的隔离性？

隔离性是指，多个用户的并发事务访问同一个数据库时，一个用户的事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离。

一个事务怎么会干扰其他事务呢？

咱们举例子来说明，假设有InnoDB表：

t(id PK, name);

表中有三条记录：

1, shenjian

2, zhangsan

3, lisi

case 1

事务A，先执行，处于未提交的状态：

insert into t values(4, wangwu);

事务B，后执行，也未提交：

select * from t;

如果事务B能够读取到(4, wangwu)这条记录，事务A就对事务B产生了影响，这个影响叫做“读脏”，读到了未提交事务操作的记录。

case 2

事务A，先执行：

select * from t where id=1;

结果集为：

1, shenjian

事务B，后执行，并且提交：

update t set name=xxoo where id=1;

commit;

事务A，再次执行相同的查询：

select * from t where id=1;

结果集为：

1, xxoo

这次是已提交事务B对事务A产生的影响，这个影响叫做“不可重复读”，一个事务内相同的查询，得到了不同的结果。

case 3

事务A，先执行：

select * from t where id>3;

结果集为：

NULL

事务B，后执行，并且提交：

insert into t values(4, wangwu);

commit;

事务A，首次查询了id>3的结果为NULL，于是想插入一条为4的记录：

insert into t values(4, xxoo);

结果集为：

Error : duplicate key!

事务A的内心OS是：你TM在逗我，查了id>3为空集，insert id=4告诉我PK冲突？

这次是已提交事务B对事务A产生的影响，这个影响叫做“幻读”。

可以看到，并发的事务可能导致其他事务：

读脏

不可重复读

幻读

InnoDB实现了哪几种事务的隔离级别？

按照SQL92标准，InnoDB实现了四种不同事务的隔离级别：

读未提交(Read Uncommitted)

读提交(Read Committed, RC)

可重复读(Repeated Read, RR)

串行化(Serializable)

不同事务的隔离级别，实际上是一致性与并发性的一个权衡与折衷。

InnoDB的四种事务的隔离级别，分别是怎么实现的？

InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。

一，读未提交(Read Uncommitted)

这种事务隔离级别下，select语句不加锁。

画外音：官方的说法是

SELECT statements are performed in a nonlocking fashion.

此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

二，串行化(Serializable)

这种事务的隔离级别下，所有select语句都会被隐式的转化为select ... in share mode.

这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。

画外音：官方的说法是

To force a plain SELECT to block if other transactions have modified the selected rows.

这是一致性最好的，但并发性最差的隔离级别。

在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

三，可重复读(Repeated Read, RR)

这是InnoDB默认的隔离级别，在RR下：

(1)普通的select使用快照读(snapshot read)，这是一种不加锁的一致性读(Consistent Nonlocking Read)，底层使用MVCC来实现，具体的原理在《InnoDB并发如此高，原因竟然在这？》中有详细的描述；

(2)加锁的select(select ... in share mode / select ... for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：

在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)

范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

画外音：这一段有点绕，多读几遍。

关于记录锁，间隙锁，临键锁的更多说明，详见《InnoDB，select为啥会阻塞insert？》。

四，读提交(Read Committed, RC)

这是互联网最常用的隔离级别，在RC下：

(1)普通读是快照读；

(2)加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；

此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

总结

并发事务之间相互干扰，可能导致事务出现读脏，不可重复度，幻读等问题

InnoDB实现了SQL92标准中的四种隔离级别