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

最新推荐文章于 2024-04-21 14:42:25 发布

xd大勇

最新推荐文章于 2024-04-21 14:42:25 发布

阅读量1.6k

点赞数 1

分类专栏： SQL 文章标签： SQL 事务隔离级别

本文链接：https://blog.csdn.net/xidianliuy/article/details/51564011

版权

SQL 专栏收录该内容

2 篇文章 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)

xd大勇

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库事务隔离级别实例探讨

我们知道，数据一般有如下四种隔离级别 0. read uncommitted （读未提交）1. read committed （读已提交）2. repeatabale read （可重复读）3. serializable read （串行化读）下面通过实例介绍这四种隔离级别。首先，准备工作，我使用的数据库是Sysbase，我们在数据库里建一个测试表，并插
复制链接

扫一扫