SQL SERVER 事务隔离

SQL SERVER中有3类问题会影响事务的完整性:

1.脏数据

事务1修改了数据,但是没有提交,事务2读取该数据.

(事务隔离级别为READ UNCOMMITTED)

 事务1

BEGIN TRAN
 BEGIN TRY
  --读取Region表内容
  SELECT * FROM Region WHERE Region_ID = '03'
  --等待10S
  WAITFOR DELAY '00:00:10'
  --再次读取Region表内容
  SELECT * FROM Region WHERE Region_ID = '03'
 END TRY
 BEGIN CATCH
  
 END CATCH
COMMIT

 

事务2

BEGIN TRAN
--修改Region内容
UPDATE Region SET Region_Name = 'Fresh Region' WHERE Region_ID = '03'
WAITFOR DELAY '00:00:10'
ROLLBACK

 

Region表数据

Region_ID   Region_Name

‘03’              'New Region'

先执行事务1,再执行事务2,得到的结果为

'03'              'New Region'

'03'              'Fresh Region'

实际因为事务2中ROLLBACK,Region表中的Region_Name并没有更改.此时事务1读取的是事务2的脏数据.

 

2.不可重复读取

将隔离级别设置为(READ COMMITTED)

 

更改事务2

 

BEGIN TRAN
--修改Region内容
UPDATE Region SET Region_Name = 'Fresh Region' WHERE Region_ID = '03'
COMMIT

 先执行事务1,再执行事务2,得到的结果为

'03'          'New Region'

'03'          'Fresh Region'

事务1读取了事务2提交后的数据,但是,事务1和事务2的数据并没有隔离开.真正的隔离是指,一个事务完全不影响另一个事务.

 

3.幻影行

幻影行表示一个事务的插入,删除,更新操作会使另外的事务返回不同的行

将隔离级别设置为(REPEATABLE READ)

 

事务1

 

BEGIN TRAN
	BEGIN TRY
		--读取Region表内容
		SELECT * FROM Region
		--等待10S
		WAITFOR DELAY '00:00:10'
		--再次读取Region表内容
		SELECT * FROM Region
	END TRY
	BEGIN CATCH
		
	END CATCH
COMMIT

 

事务2

BEGIN TRAN
--修改Region内容
INSERT INTO Region VALUES('10','Test New Land')
COMMIT

事务1的查询结果:第二次SELECT比第一次SELECT多出了一行,这一行便是幻影行

 

以上三类问题的解决均有对应的事务隔离等级可以解决

1.READ UNCOMMITTED

允许事务读脏数据

2.READ COMMITTED

只能读取提交的数据,不允许脏读,但是不能重复读

3.REPEATABLE READ

允许重复读,但是不能禁止幻影行

4.SERIALIZABLE

禁止幻影行

 

通过在会话层级设置命令

SET TRANSACTION ISOLATION LEVEL [......事务隔离等级] 设置当前会话的事务隔离级别

 

实际上,事务的隔离机制与数据库的锁机制有关,下面我们分别看看四种隔离模式下的数据库锁机制,

通过查询动态视图获取事务的锁  sys.dm_tran_locks

(以下为粗略测试-)

1.SELECT 查询

READ UNCOMMITTED:不加锁

READ COMMITTED:SELECT 语句执行时加入共享锁,SELECT 语句结束时释放

REPEATABLE READ:SELECT 语句执行时加入共享锁,并保持到整个事务结束

SERIALIZABLE:SELECT 语句执行时加入范围共享锁,并保持到整个事务结束

 

2.UPDATE

READ UNCOMMITTED:意向排他锁和排他锁,事务结束时释放

READ COMMITTED:意向排他锁和排他锁,事务结束时释放

REPEATABLE READ:意向排他锁和排他锁,事务结束时释放

SERIALIZABLE:范围更新锁,范围排他锁,事务结束时释放

 

3.DELETE

READ UNCOMMITTED:意向排他锁,事务结束时释放

READ UNCOMMITTED:意向排他锁和排他锁,事务结束时释放

REPEATABLE READ:意向排他锁和排他锁,事务结束时释放

SERIALIZABLE:意向排他锁和范围排他锁,事务结束时释放

 

4.INSERT

READ UNCOMMITTED:意向排他锁和排他锁,事务结束时释放

READ COMMITTED:意向排他锁和排他锁,事务结束时释放

REPEATABLE:意向排他锁和排他锁,事务结束时释放

SERIALIZABLE:意向排他锁和排他锁,事务结束时释放

 

所以(粗略估计):

脏读的原因在于,READ UNCOMMITTED在读取数据的时候不加共享锁,所以可以读取

排他锁的数据.

不可重复读取的原因在于:共享锁只在查询过程执行时候锁定,本次查询和下一次

查询之间允许用户提交修改的数据,而REPEATABLE将共享锁保持到事务结束,所以排它锁会

阻塞直到本次事务完成.

幻影行的原因在于:查询时候的共享锁只针对当前的行,而不针对新增的行,新增行提交后

再进行查询,读出的数据包含当前行和新增的行.使用SERIALIZABLE,保证序列化,会阻塞

插入和删除.

 

本次实验环境较为简陋,包含的是少量数据和粗略结果以及猜想,望各位大牛指正.