mysql,oracle,sql server中的默认事务隔离级别查看,更改

Alex_81D

已于 2023-03-02 23:10:18 修改

阅读量9.9k

点赞数 3

分类专栏：数据库文章标签：事务

于 2019-03-06 10:54:14 首次发布

本文链接：https://blog.csdn.net/Alex_81D/article/details/88223075

版权

数据库专栏收录该内容

36 篇文章 2 订阅

订阅专栏

第一节

好久没碰数据库了，只是想起自己当时在搞数据库的时候在事务隔离级别这块老是卡，似懂非懂的。现在想把这块整理出来，尽量用最简洁的语言描述出来，供新人参考。

首先创建一个表account。创建表的过程略过（由于InnoDB存储引擎支持事务，所以将表的存储引擎设置为InnoDB）。表的结构如下：

表结构

然后往表中插入两条数据，插入后结果如下：

数据

为了说明问题，我们打开两个控制台分别进行登录来模拟两个用户（暂且成为用户A和用户B吧），并设置当前MySQL会话的事务隔离级别。

一. read uncommitted（读取未提交数据）

具体用户A的操作如下：

set session transaction isolation level read uncommitted；
start transaction;
select * from account;

结果如下：

数据

用户B的操作如下：

set session transaction isolation level read uncommitted；
start transaction;
update account set account=account+200 where id = 1;

随后我们在A用户中查询数据，结果如下：

uncommittedA数据

结论一：

我们将事务隔离级别设置为read uncommitted，即便是事务没有commit，但是我们仍然能读到未提交的数据，这是所有隔离级别中最低的一种。

那么这么做有什么问题吗？

那就是我们在一个事务中可以随随便便读取到其他事务未提交的数据，这还是比较麻烦的，我们叫脏读。我不知道这个名字是怎么起的，为了增强大家的印象，可以这么想，这个事务好轻浮啊，饥渴到连别人没提交的东西都等不及，真脏，呸！

实际上我们的数据改变了吗？

答案是否定的，因为只有事务commit后才会更新到数据库。

二. read committed（可以读取其他事务提交的数据）---大多数数据库默认的隔离级别

同样的办法，我们将用户B所在的会话当前事务隔离级别设置为read commited。

在用户A所在的会话中我们执行下面操作：

update account set account=account-200 where id=1;

read committed

我们将id=1的用户account减200。然后查询，发现id=1的用户account变为800。

在B用户所在的会话中查询：


select * from account；

结果如下：

read committedB

我们会发现数据并没有变，还是1000。

接着在会话A中我们将事务提交：

commit;

在会话B中查询结果如下：

read committedB1

结论二：

当我们将当前会话的隔离级别设置为read committed的时候，当前会话只能读取到其他事务提交的数据，未提交的数据读不到。

那么这么做有什么问题吗？

那就是我们在会话B同一个事务中，读取到两次不同的结果。这就造成了不可重复读，就是两次读取的结果不同。这种现象叫不可重复读。

三. repeatable read（可重读）---MySQL默认的隔离级别

现在有个需求，就是老板说在同一个事务中查询结果必须保持一致，如果你是数据库，你会怎么做？数据库是这么做的。

在会话B中我们当前事务隔离级别为repeatable read。具体操作如下：

set session transaction isolation level repeatable read;
start transaction;

接着在会话B中查询数据：

repeatablereadB1

我们在A用户所在会话中为表account添加一条数据：


insert into account(id,account) value(3,1000);
commit;

然后我们查询看数据插入是否成功：

repeatable readA

回到B用户所在的会话，我们查询结果：

repeatablereadB2

用户B在他所在的会话中想插入一条新数据id=3，value=1000。来我们操作下：

readpeatablereadB3

什么？竟然插不进去，说我数据重复？

用户B当然不服啊，因为查询到数据只有两条啊，为什么插入id=3说我数据重复了呢？

我再看一遍，莫非我眼花了？

repeatablereadB2

试想一下，在实际中用户A和用户B肯定是相互隔离的，彼此不知道操作什么。用户B碰到这种现象，肯定会炸毛的啊，明明不存在的数据，插入却说主键id=3数据重复了。

结论三：

当我们将当前会话的隔离级别设置为repeatable read的时候，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。

有什么问题吗？

管他呢，老板的要求满足了。要一个事务中读取的数据一致（可重复读）。我只能这么做啊，打肿脸装胖子。数据已经发生改变，但是我还是要保持一致。但是，出现了用户B面对的问题，这种现象叫幻读（记得当时就在这个地方纠结好久，到底什么是幻读啊）。

四. serializable（串行化）

同样，我们将用户B所在的会话的事务隔离级别设置为serializable并开启事务。

set session transaction isolation level serializable;
start transaction;

在用户B所在的会话中我们执行下面操作：


select * from account;

结果如下：

serializableA

那我们这个时候在用户A所在的会话中写数据呢？

readcommittedA1

我们发现用户A所在的会话陷入等待，如果超时（这个时间可以进行配置），会出现Lock wait time out提示：

readcommittedA2

如果在等待期间我们用户B所在的会话事务提交，那么用户A所在的事务的写操作将提示操作成功。

结论四：

当我们将当前会话的隔离级别设置为serializable的时候，其他会话对该表的写操作将被挂起。可以看到，这是隔离级别中最严格的，但是这样做势必对性能造成影响。所以在实际的选用上，我们要根据当前具体的情况选用合适的。

第二节

mysql,oracle,sql server中的默认事务隔离级别查看,更改

未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据）

已提交读（数据库引擎的默认级别）

可重复读

可序列化（隔离事务的最高级别，事务之间完全隔离）

可串行化比较严谨,级别高;

MySQL

mysql默认的事务处理级别是'REPEATABLE-READ',也就是可重复读

1.查看当前会话隔离级别

select @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set session transaction isolatin level repeatable read;

4.设置系统当前隔离级别

set global transaction isolation level repeatable read;

Oracle

oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。

默认系统事务隔离级别是READ COMMITTED,也就是读已提交

1.查看系统默认事务隔离级别,也是当前会话隔离级别

--首先创建一个事务
declare
     trans_id Varchar2(100);
  begin
     trans_id := dbms_transaction.local_transaction_id( TRUE );
  end;

--查看事务隔离级别

SELECT s.sid, s.serial#,

　　CASE BITAND(t.flag, POWER(2, 28))
　　　　WHEN 0 THEN 'READ COMMITTED'
　　　　ELSE 'SERIALIZABLE'
　　END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

SQL Server

默认系统事务隔离级别是read committed,也就是读已提交

1.查看系统当前隔离级别

DBCC USEROPTIONS

isolation level 这一项的 Value 既是当前的隔离级别设置值

2.设置系统当前隔离级别

SET TRANSACTION ISOLATION LEVEL Read UnCommitted;

其中Read UnCommitted为需要设置的值

第三节

不可重复读和幻读的区别

不可重复读重点在于update和delete，而幻读的重点在于insert。

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

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

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

可串行化（Serializable ）

读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

参考链接：
1.404 Page not found - 美团技术团队
2.大发体育在线平台-dafa888手机版网页版