关闭

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

标签: SQL 事务 隔离级别
310人阅读 评论(0) 收藏 举报
分类:

我们知道,数据一般有如下四种隔离级别
  • 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)


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:10147次
    • 积分:220
    • 等级:
    • 排名:千里之外
    • 原创:12篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档