mysql,oracle,sql server中的默认事务隔离级别查看,更改 And SQLserver锁和事务隔离级别的比较与使用

最新推荐文章于 2024-04-12 17:09:02 发布

paolei

最新推荐文章于 2024-04-12 17:09:02 发布

阅读量687

点赞数

分类专栏： sql

原文链接：https://www.cnblogs.com/who-else/p/6659564.html

版权

sql 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

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

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

可重复读

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

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

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为需要设置的值

------------------------------------------------------------------------------------------

SQLserver锁和事务隔离级别的比较与使用

对象

① 锁：每条SQL语句

② 隔离：事务

锁

①

并发问题

丢失更新

未确认的读取（脏读）

不一致的分析（非重复读）：多次读取相同的数据（行）不一致（其他用户更改update）

幻像读：多次读取有不存在和新增的数据（其他用户插入insert或删除delete）

隔离级别

隔离级别	脏读	不可重复读取	幻像	说明
未提交读(read uncommitted)	是	是	是	如果其他事务更新，不管是否提交，立即执行
提交读(read committed默认)	否	是	是	读取提交过的数据。如果其他事务更新没提交，则等待
可重复读(repeatable read)	否	否	是	查询期间，不允许其他事务update
可串行读(serializable)	否	否	否	查询期间，不允许其他事务insert或delete

提交读

假设存在表A，如下所示

A1	A2	A3
11	21	31
12	22	32

打开查询分析器并打开两个连接，分别输入如下两个事务：

--事务Ⅰ

SET TRANSACTION ISOLATION LEVEL READ Committed

begin tran

update A set A2 = 20 where A1 = 11

waitfor delay '00:00:10'

rollback tran

--事务Ⅱ

SET TRANSACTION ISOLATION LEVEL READ Committed

select * from A where A1 = 11

如果先运行事务Ⅰ，然后紧接着运行事务Ⅱ，则事务Ⅱ要等待10秒钟（一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改）。

如果把事务Ⅱ改为如下

SET TRANSACTION ISOLATION LEVEL READ UNCommitted

select * from A where A1 = 11

那么事务Ⅱ不需等待，立即执行（可以看出READ UNCommitted事务select不对数据发出共享锁）

锁：(这里主要讲解 共享锁 和 排他锁 两种经常用到的锁)

共享锁主要是为了共享读（select），如果存在事务（一个或多个）拥有对表中数据（关于锁数据的多少，视锁的粒度而定）的共享锁，不允许对锁定的数据进行更新(update)（从锁的角度讲，即不允许事务获取排他锁，要等到所有的共享锁都释放掉）。反之，如果事务对数据已经具有排他锁（只能有一个），其他的事务就不能对锁定的数据获取共享锁和排他锁（即排他锁与共享锁不能兼容，更多信息请查看锁兼容性），在此特别强调一下锁定的数据，因为有的资料上讲解到“一个连接写的时候，另一个连接可以写”，实际上写的这种情况是各个连接的读写的数据不是相同的行，也就是说各个连接锁定的数据不同。

根据以上分析，我们总结为六个字为“共享读，排他写”。

了解了锁的情况之后，又涉及到一个问题。事务究竟要保持锁多久呢?

一般来说，共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read Committed的默认级别，只在读取(select)的期间保持锁定，即在查询出数据以后就释放了锁；如果隔离级别为更高的Repeatable read或Serializable，直到事务结束才释放锁。另说明，如果select语句中指定了HoldLock提示，则也要等到事务结束才释放锁。

排他锁直到事务结束才释放。

做出了以上分析，现在我们可能会存在这样的疑问，到底在执行SQL语句的时候发出什么样的锁呢，这就由事务的隔离级别决定了。一般情况，读语句(select)发出共享锁，写语句(update,insert,delete)发出排他锁。但是，如果这样不能满足我们的要求怎么办呢，有没有更多选择呢，别急，SQLserver为我们提供了锁定提示的概念。

锁定提示对SQL语句进行特别指定，这个指定将覆盖事务的隔离级别。下面对各个锁定提示分别予以介绍（更多资料请查看SQLserver的联机帮助），笔者做出了以下分类。

类型1

① READUNCOMMITTED：不发出锁

② READCOMMITTED：发出共享锁，保持到读取结束

③ REPEATABLEREAD：发出共享锁，保持到事务结束

④ SERIALIZABLE：发出共享锁，保持到事务结束

类型2

① NOLOCK：不发出锁。等同于READUNCOMMITTED

② HOLDLOCK：发出共享锁，保持到事务结束。等同于SERIALIZABLE

③ XLOCK：发出排他锁，保持到事务结束。

④ UPDLOCK：发出更新锁，保持到事务事务结束。（更新锁：不阻塞别的事物，允许别的事物读数据（即更新锁可与共享锁兼容），但他确保自上次读取数据后数据没有被更新）

⑤ READPAST：发出共享锁，但跳过锁定行，它不会被阻塞。适用条件：提交读的隔离级别，行级锁，select语句中。