SQL Server2005中的事务和锁（管理大批量并发处理）

1.       何为并发

同时访问一种资源的用户被视为并发访问资源。并发数据访问需要某些机制，以防止多个用户试图修改其他用户正在使用的资源时产生负面影响。

2.       并发影响

丢失更新

未提交的依赖关系（脏读）

不一致的分析（不可重复读）

幻读

3.       SQL Server2005新的隔离级别

SQL Server2005支持使用行版本控制的两个事务隔离级别。

一个是已提交读隔离级别的新实现；

另一个是快照隔离级别。

4.       行版本控制

（1）       应用

生成触发器中插入的和删除的表。

支持多个活动的结果集（MARS）。

支持指定ONLINE选项的索引操作。

支持基于行版本控制的事务隔离级别：

                    新实现的已提交读隔离级别，使用行版本控制提供语句级的读取一致性。

                    新快照隔离级别，提供事务级的读取一致性。

（2）       Tempdb数据库必须具有足够的空间用于版本存储区。

（3）       读取数据时的行为

当在基于行版本控制的隔离级别下运行的事务读取数据时，读取操作不会获取正被读取的数据上的共享锁，因此不会阻塞正在修改数据的事务。另外，锁定资源的开销随着所获取的锁的数量的减少降至最低。使用行版本控制的已提交读和快照隔离级别旨在提供副本数据的语句级和事务级读取一致性。

与读未提交隔离级别的区别：读未提交隔离级别读取的是正在修改的数据，每次读取可能都不一样，会产生脏读；行版本控制读取的是修改数据前保存下来的副本，每次读取都会是一致的。

使用行版本控制的已提交读，事务2可以读取事务1中正在修改的数据。

（4）       修改数据时的行为

在使用行版本控制的已提交读事务中，使用阻塞性扫描：其中读取数据值时将在数据行上采用更新锁完成选择要更新的行。这与不使用行版本控制的已提交读事务相同。

在快照隔离级别下运行的事务对数据修改采用乐观方法：直到数据被修改时才获取数据上的锁。当数据行符合更新标准时（数据要进行更新前），快照事务将验证数据行未被并发事务修改【在快照事务开始（执行了获取指定资源的语句，如update或select）后提交的修改】。如果数据行已在快照事务以外修改，则将出现更新冲突，同时快照事务也将终止。更新冲突由数据库引擎处理，无法禁用更新冲突检测。

5.       设置SQL Server2005新的隔离级别

（1）       将READ_COMMITED_SNAPSHOT数据库选项设置为ON时，已提交读隔离使用行版本控制提供语句级别的读取一致性。读取操作只需要SCH-S表级别的锁，不需要页锁或行锁。

（2）       将READ_COMMITED_SNAPSHOT数据库选项设置为OFF（默认设置）时，已提交读隔离的行为与在SQL Server的早期版本中相同。两个实现都满足已提交读隔离的ANSI定义。

（3）       快照隔离级别使用行版本控制来提供事务级别的读取一致性。读取操作不获取页锁或行锁，只获取SCH-S表锁。读取其他事务修改的行时，读取操作将检索启动事务时存在的行的版本。将ALLOW_SHAPSHOT_ISOLATION数据库选项设置为ON时，将启用快照隔离。默认情况下，用户数据库的此选项设置为OFF。

（4）       语法

ALTER DATABASE <database_name>

SET READ_COMMITTED_SNAPSHOT ON

WITH ROLLBACK AFTER 5

 

ALTER DATABASE <database_name>

SET ALLOW_SNAPSHOT_ISOLATION ON

6.       DEMO

示例1：基于行版本控制的读提交隔离级别

事务1：

use AdventureWorks

go

 

--当前连接必须为连接到指定数据库的唯一连接才能修改成功

alter database AdventureWorks

set read_committed_snapshot on

go

 

--设置读提交隔离级别

set transaction isolation level read committed

go

 

begin tran

 

--第一次读取

select VacationHours from HumanResources.Employee

where employeeid=4

 

--第二次读取（事务2中更新数据后读取）

select VacationHours from HumanResources.Employee

where employeeid=4

 

--第三次读取（事务2中更新数据并提交后读取）

select VacationHours from HumanResources.Employee

where employeeid=4

 

--更新数据(在事务2中已经更新并提交了该数据)

update HumanResources.Employee set VacationHours=VacationHours-8

where employeeid=4

 

--更新后读取

select VacationHours from HumanResources.Employee

where employeeid=4

 

rollback

 

事务2：

use AdventureWorks

go

 

begin tran

 

--更新数据

update HumanResources.Employee set VacationHours=VacationHours-8

where employeeid=4

 

select VacationHours from HumanResources.Employee

where employeeid=4

 

commit

 

在事务1中将事务隔离级别设置为快照。读取记录（employeeid=4），第一次读取，VacationHours值为48。在事务2中更新VacationHours为40。在事务1中第二次读取，VacationHours值仍为48。将事务2提交。在事务1中第三次读取VacationHours，值为40。在事务1中更新数据，数据被更新成功（不会发生更新冲突）。

 

示例2：快照隔离级别-提供事务级的读取一致性

事务1：

use AdventureWorks

go

 

--修改数据库选项

alter database AdventureWorks

set allow_snapshot_isolation on

go

 

--设置快照隔离级别

set transaction isolation level snapshot

go

 

begin transaction

 

--第一次读取

select employeeid,VacationHours from HumanResources.Employee

where employeeid=4

 

--第二次读取（事务2中更新后读取）

select employeeid,VacationHours from HumanResources.Employee

where employeeid=4

 

--第三次读取（事务2中更新并提交后读取）

select employeeid,VacationHours from HumanResources.Employee

where employeeid=4

 

commit

 

--第四次读取（提交事务1后读取）

select employeeid,VacationHours from HumanResources.Employee

where employeeid=4

 

事务2：

use AdventureWorks

go

 

begin tran

 

--更新数据

update HumanResources.Employee set VacationHours=VacationHours-8

where employeeid=4

 

--更新后读取

select employeeid,VacationHours from HumanResources.Employee

where employeeid=4

 

commit

 

在事务1中将事务隔离级别设置为快照。读取记录（employeeid=4），第一次读取，VacationHours值为48。在事务2中更新VacationHours为40。在事务1中第二次读取，VacationHours值仍为48。将事务2提交。在事务1中第三次读取VacationHours，值仍为48。将事务1提交。第四次读取VacationHours，值为40。

 

示例3：快照隔离-更新冲突

事务1：

use AdventureWorks

go

 

--修改数据库选项

alter database AdventureWorks

set allow_snapshot_isolation on

go

 

--设置快照隔离级别

set transaction isolation level snapshot

go

 

begin transaction

 

--读取数据

select VacationHours from HumanResources.Employee

where employeeid=4

 

--更新在事务2中已经修改并提交的数据

update HumanResources.Employee set VacationHours=VacationHours-8

where employeeid=4

 

rollback tran

 

事务2：

use AdventureWorks

go

 

begin tran

 

--更新数据

update HumanResources.Employee set VacationHours=VacationHours-8

where employeeid=4

 

commit

 

在事务1中将事务隔离级别设置为快照。读取记录（employeeid=4），VacationHours值为48。在事务2中更新VacationHours为40并提交。在事务1中更新数据，将会发生更新冲突，并回滚事务。提示如下图所示：

此时再去ROLLBAKC TRAN会提示：

7.       选择

对于大多数应用程序，建议应用使用行版本控制的已提交读隔离级别，而不要应用快照隔离级别，原因如下：

（1）       已提交读隔离比快照隔离占用的tempdb空间少。

（2）       已提交读隔离可用于分布式事务，而快照隔离不能用于分布式事务。

（3）       已提交读隔离可用于大多数现有应用程序，而不需进行任何更改。

（4）       快照隔离会产生更新冲突，RCSI（Read Committed Snapshot Isolation，基于行版本控制的读提交隔离级别）不会产生更新冲突。

快照隔离提供事务级读取的一致性。数据快照在快照事务启动时产生并在事务持续时间内保持一致。发生下列情况时，使用快照隔离：

（1）       需要开放式并发控制（比如要采用乐观并发控制）

（2）       由于更新冲突必须回滚事务的可能性较低

（3）       应用程序需要基于必须具有时点一致性的长时间运行的多语句查询生成报告。快照隔离具有可重复读取的优点，而不使用共享锁。

8.       最小化阻塞

（1）       事务尽量小

（2）       不要在批中包含交互

（3）       索引（是否有效，是否需要重建等）解决锁阻塞的问题，最先要考虑索引

（4）       避免在高峰期运行需要长时间运行的语句

9.       其他技术

（1）       数据库快照

（2）       数据库快照是数据库（源数据库）的只读、静态视图。多个快照可以位于一个源数据库中，并且可以作为数据库始终驻留在同一服务器实例上。创建快照时，每个数据库快照在事务上与源数据库一致。在数据库所有者显式删除之前，快照始终存在。

（3）       快照可用于报表。另外，如果源数据库出现用户错误，还可将源数据库恢复到创建快照时的状态。丢失的数据仅限于创建快照后数据库更新的数据。

10.   数据库快照

Database Snapshot

（1）       数据库某个时间点的快照

即时创建的

只读性的

（2）       基础数据库继续变化

快照不影响、限制对基础数据库的更新

（3）       快照名与基础数据库名不同

（4）       返回到以前创建的快照可挽救误操作

（5）       极其有效的空间管理

（6）       采用“copy on write”机制

无需复制数据的完整备份

共享无变化的数据库页面

仅存储已变化的数据页

（7）       示例

--创建数据库快照

use master

go

create database TEST_Snapshot

on(name='test',filename='c:\TestSnapshot.ss') –-‘test’为TEST数据库数据文件的逻辑名

as snapshot of TEST

go

 

--从快照还原数据库

use master

go

restore database TEST from database_snapshot='TEST_Snapshot'

go

 

--删除快照

use master

go

drop database TEST_Snapshot

go

11.   查看数据库是否启用了快照隔离

alter database AdventureWorks

set ALLOW_SNAPSHOT_ISOLATION on

go

 

--0 = 快照隔离状态为 OFF（默认值）。不允许使用快照隔离。

--1 = 快照隔离状态为 ON。允许使用快照隔离。

select snapshot_isolation_state from sys.databases

where name='AdventureWorks'

go

12.   查看数据库是否启用了RCSI

alter database AdventureWorks

set read_committed_snapshot on

go

 

--1 = READ_COMMITTED_SNAPSHOT 选项为 ON。read-committed 隔离级别下的读操作基于快照扫描，没有获取锁。

--0 = READ_COMMITTED_SNAPSHOT 选项为 OFF（默认）。read-committed 隔离级别下的读操作使用共享锁。

select is_read_committed_snapshot_on from sys.databases

where name='AdventureWorks'

go