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

我们知道，数据一般有如下四种隔离级别

• 0.  read uncommitted （读未提交）
• 1.  read committed （读已提交）
• 2.  repeatabale read （可重复读）
• 3.  serializable read （串行化读）

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

准备工作

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

create table test_table (  col1 int,  col2 varchar(15))goalter table test_table lock datarows gocreate unique index ux_idx1 on test_table (col1)goinsert 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 0go

在窗口B中，执行如下SQL

begin tranupdate test_table  set col2 = "TEST UPDATE"where col1 = 1go

我们看到输出：

(1 row affected)

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

这时，在窗口A，执行如下SQL，查看同一条记录（col1=1）的值：

select * from test_table where col1 = 1go

我们看到输出：

 col1        col2            ----------- ---------------           1 TEST UPDATE    (1 row affected)

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

读已提交

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

SET TRANSACTION ISOLATION LEVEL 1go

并在窗口A执行如下SQL

select * from test_table where col1 = 1go

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

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

commitgo

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

 col1        col2            ----------- ---------------           1 TEST UPDATE    (1 row affected)

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

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

可重复读

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

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

begin tran select * from test_table where col1 = 2go

我们立即看到输出：

 col1        col2            ----------- ---------------           2 test           (1 row affected)

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

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

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

SQL执行成功，立即看到输出：

(1 row affected)

表示更新成功。

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

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

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

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

begin tran select * from test_table where col1 = 2gocol1        col2           ----------- ---------------          2 test           (1 row affected) select * from test_table where col1 = 2gocol1        col2           ----------- ---------------          2 TEST UPDATE    (1 row affected)

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

我们提交窗口A中的事务

commitgo

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

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

SET TRANSACTION ISOLATION LEVEL 2go

在窗口A执行如下SQL：

begin tran select * from test_table where col1 = 3go

看到输出：

 col1        col2            ----------- ---------------           3 test           (1 row affected)

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

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

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

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

select * from test_table where col1 = 3go

输出如下：

 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 = 4go

 SQL执行成功，立即返回了：

(1 row affected)

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

select * from test_table where col1 = 4go

马上就得到了输出：

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

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

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

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

回到窗口A，查询col1=4的记录：

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

值还是原来的 “TEST UPDATE”。

这时，我们提交A窗口的事务，在窗口A执行：

commitgo

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

(1 row affected)

查询一下：

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

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

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

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

SET TRANSACTION ISOLATION LEVEL 2gobegin transelect * 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 2gobegin transelect * 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 trangoSET TRANSACTION ISOLATION LEVEL 3go

现在窗口A执行如下SQL：

begin transelect * 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_tablego col1        col2            ----------- ---------------           1 TEST UPDATE               2 TEST UPDATE               3 test                      4 TEST UPDATE UPD           5 test                      6 test                      7 test           (7 rows affected)