数据库事务隔离级别实例探讨

原创 2016年06月02日 09:47:59

我们知道,数据一般有如下四种隔离级别
  • 0.  read uncommitted (读未提交)
  • 1.  read committed (读已提交)
  • 2.  repeatabale read (可重复读)
  • 3.  serializable read (串行化读)

下面通过实例介绍这四种隔离级别。


准备工作

首先,准备工作, 我使用的数据库是Sybase, 我们在数据库里建一个测试表,并插入数据:

create table test_table (
  col1 int,
  col2 varchar(15)
)
go
alter table test_table lock datarows 
go
create unique index ux_idx1 on test_table (col1)
go

insert test_table (col1, col2) values (1, "test")
insert test_table (col1, col2) values (2, "test")
insert test_table (col1, col2) values (3, "test")
insert test_table (col1, col2) values (4, "test")
insert test_table (col1, col2) values (5, "test")
go

显示一下数据:

col1        col2           
 ----------- ---------------
           1 test           
           2 test           
           3 test           
           4 test           
           5 test           

(5 rows affected)

注意上面建表的时候,把表的锁改为了行模式

alter table test_table lock datarows 
go

现在打开两个窗口, 分别叫做窗口A,和窗口B。


读未提交

设置窗口A的事务隔离级别为 0 (读未提交)

SET TRANSACTION ISOLATION LEVEL 0
go

在窗口B中,执行如下SQL

begin tran

update test_table 
 set col2 = "TEST UPDATE"
where col1 = 1
go

我们看到输出:

(1 row affected)


表示上面的SQL执行成功。 注意: 上面的SQL开启了一个事务,但是事务并没有提交。


这时,在窗口A,执行如下SQL,查看同一条记录(col1=1)的值:


select * from test_table where col1 = 1
go


我们看到输出:

 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    

(1 row affected)

我们看到窗口B里更新后的值。也就是,B窗口里并没有提交的数据, 我们在窗口A中读到了。这就是读未提交。


读已提交

下面介绍读已提交,这时候,把窗口A的事务隔离级别设置为1(读已提交)


SET TRANSACTION ISOLATION LEVEL 1
go


并在窗口A执行如下SQL

select * from test_table where col1 = 1
go


这时候,我们发现上面的SQL语句阻塞了。因为事务隔离级别是读已提交,窗口A中的事务和窗口B中的事务操作了同一条记录, 它在等待窗口B中的事务提交或者回滚。如果窗口B中的事务没有完成,它就一直等待下去。


这时候,我们提交窗口B中的事务,窗口B执行:

commit
go

现在,再看窗口A, 刚才的阻塞解除了,窗口A看到如下输出:

 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    

(1 row affected)

窗口A的事务,读到了窗口B提交后的数据。


总结:事务隔离界别设置为读已提交,只有提交后的数据才能被读取。如果当前事务读取的记录在另外一个事务中更新了,且还没有的提交,当前读取操作会被阻塞。


可重复读

再来介绍可重复读,可重复读对应的就是不可重复读,先介绍什么是不可重复读。

现在窗口A中的事务隔离级别还是读已提交,先不用改它。我们在窗口A中执行如下SQL

begin tran 
select * from test_table where col1 = 2
go

我们立即看到输出:

 col1        col2           
 ----------- ---------------
           2 test           

(1 row affected)


上面的SQL,开启了一个事务,并读了一行数据 col2=2, 但是并没有提交事务。


这时,在窗口B中,我们执行如下SQL,修改col2=2的值:

update test_table 
 set col2 = "TEST UPDATE"
where col1 = 2
go


SQL执行成功,立即看到输出:

(1 row affected)
表示更新成功。


这时候,回到窗口A,在来查询 col2=2的值,

select * from test_table where col1 = 2
go
col1        col2           
----------- ---------------
          2 TEST UPDATE    

(1 row affected)

这时,读出来的值是窗口B中更新后的值。这样问题就来了,在窗口A,同一个事务里,两次读取同一个值,返回的结果不一样,这就是不可重复读。

窗口A两次读取的完整SQL和输出如下:

begin tran 
select * from test_table where col1 = 2
go
col1        col2           
----------- ---------------
          2 test           

(1 row affected)
 
select * from test_table where col1 = 2
go
col1        col2           
----------- ---------------
          2 TEST UPDATE    

(1 row affected)

现在看看,什么是可重复度。可重复度,顾名思义就是,在同一个事务里,多次读取的同一个值,前后应该一致。

我们提交窗口A中的事务

commit
go

我们以col1=3讲解可重复度。

在窗口A中设置事务隔离界别为可重复读:

SET TRANSACTION ISOLATION LEVEL 2
go


在窗口A执行如下SQL:

begin tran 
select * from test_table where col1 = 3
go

看到输出:

 col1        col2           
 ----------- ---------------
           3 test           

(1 row affected)

切换到窗口B中,执行如下SQL,更新同一条记录(col1=3)

update test_table 
 set col2 = "TEST UPDATE"
where col1 = 3
go
 

这时候发现上面的SQL语句阻塞了,上面的更新等待窗口A的事务完成。


在窗口A中,再次执行如下SQL

select * from test_table where col1 = 3
go
输出如下:

 col1        col2           
 ----------- ---------------
           3 test           

(1 row affected)

和上一次查询结果相同。窗口A中的同一个事务内,2次或者多次对同一个资源(这里是col1=3)读取的结果是一致的,这就是可重复读。


我们在窗口B,按Ctrl + C,把窗口B中SQL break掉。我们看看,在窗口B,能不能update 另外的一条记录 col1=4, 窗口B中执行:

update test_table 
 set col2 = "TEST UPDATE"
where col1 = 4
go

 SQL执行成功,立即返回了:

(1 row affected)

这时,我们在窗口A中,查看col1=4的记录:

select * from test_table where col1 = 4
go

马上就得到了输出:

 col1        col2           
 ----------- ---------------
           4 TEST UPDATE 

这时候再回到窗口B,再次 update  col1=4的记录,

update test_table 
 set col2 = "TEST UPDATE UPDATE"
where col1 = 4
go
 

这时发现,上面的update阻塞了。

回到窗口A,查询col1=4的记录:

select * from test_table where col1 = 4
go
 col1        col2           
 ----------- ---------------
           4 TEST UPDATE    

(1 row affected)

值还是原来的 “TEST UPDATE”。

这时,我们提交A窗口的事务,在窗口A执行:

commit
go


这时,窗口B中被阻塞的更新操作立即得到了执行,窗口B中输出:

(1 row affected)

查询一下:

select * from test_table where col1 = 4
go
 col1        col2           
 ----------- ---------------
           4 TEST UPDATE UPD

(1 row affected)


总结:如果A事务设置为可重复读, 当它读取了资源R,但还没有提交事务时,其他事务就不能更新资源R,对它没有读取的资源,其它事务是可以更新的。


上面的例子是以单条记录作为资源的, 如果使用select * 呢?

如果在窗口A中, 使用如下SQL

SET TRANSACTION ISOLATION LEVEL 2
go
begin tran
select * from test_table 
go

输出:

 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           


上面,select * 读取整张表,这时候,在窗口B中对 test_table 表中的已有记录更新操作都会被阻塞:

窗口B执行:

 update test_table 
  set col2 = "TEST UPDATE"
 where col1 = 5
 go


上面的SQL会被阻塞 (Ctrl +C 中断阻塞)。


窗口B执行:

 delete test_table where col1 = 1
 go

上面的SQL同样会被阻塞(Ctrl +C 中断阻塞)。

上例说明,可重复读对 select *  已有数据是适用的。


那么对新插入的数据呢?看下面的例子:

我们新插入一条记录,窗口B执行:

insert test_table (col1, col2) values (6, "test")
go

输出:

(1 row affected)

说明插入操作执行成功。


回到窗口A, 执行

select * from test_table 
go

输出:

 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           
           6 test           

(6 rows affected)

这时,窗口A中事务,读到了窗口B中新插入的数据。


窗口A中,完整的SQL和输出如下:

SET TRANSACTION ISOLATION LEVEL 2
go
begin tran
select * from test_table 
go
 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           

(5 rows affected)
select * from test_table 
go
 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           
           6 test           

(6 rows affected)


从上面的输出可以看出,虽然事务隔离级别设置可重复读,但是,同一个事务中,上面两次select * 操作,返回的结果是不同的。这个问题就是范读。

要解决泛读的问题,就需要提高事务的隔离界别到串行化读。


串行化读

最后来看看串行化读,提交窗口A中的事务,并设置事务隔离级别到串行化读,窗口A中执行:

commit tran
go

SET TRANSACTION ISOLATION LEVEL 3
go


现在窗口A执行如下SQL:

begin tran
select * from test_table 
go
输出:

 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           
           6 test           

(6 rows affected)


切换到窗口B中,执行:

insert test_table (col1, col2) values (7, "test")
go

这是,窗口B中,上面的SQL语句被阻塞了,插入操作不能完成。


窗口A中,执行select *:

select * from test_table 
go

输入和上次的一样:

 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           
           6 test           

(6 rows affected)


可见,串行化解决了泛读的问题。



窗口A中,执行

 commit tran
 go

这时,窗口B阻塞解除,

(1 row affected)
查询一下:

select * from test_table
go
 col1        col2           
 ----------- ---------------
           1 TEST UPDATE    
           2 TEST UPDATE    
           3 test           
           4 TEST UPDATE UPD
           5 test           
           6 test           
           7 test           

(7 rows affected)


版权声明:本文为博主原创文章,未经博主允许不得转载。

Spring事务隔离级别(solation level)介绍及例子

Spring事务隔离级别(solation level)介绍及例子当两个事务对同一个数据库的记录进行操作时,那么,他们之间的影响是怎么样的呢?这就出现了事务隔离级别的概念。数据库的隔离性与并发控制有很...
  • CSDN_so_nice
  • CSDN_so_nice
  • 2017年01月09日 17:06
  • 1270

理解事务的4种隔离级别

数据库事务的隔离级别有4种,由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。Read uncommitt...
  • qq_33290787
  • qq_33290787
  • 2016年07月16日 16:19
  • 54122

事务隔离级别示例与分析(一)

l  本示例文档演示SQL SERVER,ORACLE下不同事务隔离级别的区别,以及两种数据库本身的特点l  为了模拟并发环境,SQL SERVER在SMO程序中打开两个查询窗口即可。oracle可以...
  • mypop
  • mypop
  • 2011年01月06日 15:44
  • 1069

数据库中的事务理解

事务 事务(Transaction),一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在计算机术语中,事务通常就是指数据库事务。 概念...
  • zhangwj0101
  • zhangwj0101
  • 2016年03月21日 12:19
  • 11045

数据库事务隔离级别实例探讨

我们知道,数据一般有如下四种隔离级别  0.  read uncommitted (读未提交)1.  read committed (读已提交)2.  repeatabale read (可重复读...
  • xidianliuy
  • xidianliuy
  • 2016年06月02日 09:47
  • 753

事务隔离级别示例代码

create table t(id int identity,sid varchar(10)) insert t select 原始001 union all select 原始002go/*set ...
  • mengmou
  • mengmou
  • 2007年05月26日 16:34
  • 691

关于数据库事务隔离级别的介绍

事务(Transaction)是并发控制的基本单位。所谓的事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。例如,银行转账工作:从一个账号扣款并使另一个账号增款,这...
  • xiasihua88
  • xiasihua88
  • 2011年02月16日 09:06
  • 1328

数据库事务四种隔离级别

定义: 在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别。 数据库事务的隔离级别有4个,由低到高依次为Read uncommitted(未授权读取、读未提交)、Read com...
  • tolcf
  • tolcf
  • 2015年10月20日 22:40
  • 19735

事务四大隔离级别的不同

  • 2015年07月21日 23:53
  • 51.86MB
  • 下载

事务的隔离级别

现在来看看MySQL数据库为我们提供的四种隔离级别:   ① Serializable (串行化):可避免脏读、不可重复读、幻读的发生。   ② Repeatable read (可重复读):可避...
  • skyxuyan
  • skyxuyan
  • 2018年01月24日 17:37
  • 12
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库事务隔离级别实例探讨
举报原因:
原因补充:

(最多只允许输入30个字)