数据库的事务和锁机制（SQL Server）

说到事务，首先我们就要知道为什么需要事务，这就要先看看锁机制的相关概念！锁的概述

一. 为什么要引入锁

多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:

丢失更新
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统

脏读
A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致

不可重复读
A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致幻像读A用户读取数据,随后B用户插入/删除了几条数据,此时A用户再读取数据时发现数据条数不一致
并发控制的主要方法是*,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

二锁的分类

锁的类别有两种分法：

1. 从数据库系统的角度来看：分为独占锁（即排它锁），共享锁和更新锁

MS-SQL Server 使用以下资源锁模式。

锁模式描述
共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
意向锁用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
架构锁在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。

共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

更新锁
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。

意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

锁模式描述
意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。
意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。
与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。

独占锁：只允许进行锁定操作的程序使用，其他任何对他的操作均不会被接受。执行数据更新命令时，SQL Server会自动使用独占锁。当对象上有其他锁存在时，无法对其加独占锁。
共享锁：共享锁锁定的资源可以被其他用户读取，但其他用户无法修改它，在执行Select时，SQL Server会对对象加共享锁。
更新锁：当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。

2. 从程序员的角度看：分为乐观锁和悲观锁。
乐观锁：完全依靠数据库来管理锁的工作。
悲观锁：程序员自己管理数据或对象上的锁处理。

MS-SQLSERVER 使用锁在多个同时在数据库内执行修改的用户间实现悲观并发控制

三锁的粒度
锁粒度是被*目标的大小,*粒度小则并发性高,但开销大,*粒度大则并发性低但开销小

SQL Server支持的锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁

资源描述
RID 行标识符。用于单独锁定表中的一行。
键索引中的行锁。用于保护可串行事务中的键范围。
页 8 千字节 (KB) 的数据页或索引页。
扩展盘区相邻的八个数据页或索引页构成的一组。
表包括所有数据和索引在内的整个表。
DB 数据库。

四锁定时间的长短

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。

用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用 READ COMMITTED 的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下一页上获取锁时才释放锁。如果指定 HOLDLOCK 提示或者将事务隔离级别设置为 REPEATABLE READ 或 SERIALIZABLE，则直到事务结束才释放锁。

根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下一次提取或关闭游标（以先发生者为准）时才释放滚动锁。但是，如果指定 HOLDLOCK，则直到事务结束才释放滚动锁。

用于保护更新的排它锁将直到事务结束才释放。
如果一个连接试图获取一个锁，而该锁与另一个连接所控制的锁冲突，则试图获取锁的连接将一直阻塞到：

将冲突锁释放而且连接获取了所请求的锁。

连接的超时间隔已到期。默认情况下没有超时间隔，但是一些应用程序设置超时间隔以防止无限期等待

五 SQL Server 中锁的自定义

1 处理死锁和设置死锁优先级

死锁就是多个用户申请不同*,由于申请者均拥有一部分*权而又等待其他用户拥有的部分*而引起的无休止的等待

可以使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。

2 处理超时和设置锁超时持续时间。

@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒

SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息

示例
下例将锁超时期限设置为 1,800 毫秒。
SET LOCK_TIMEOUT 1800

3 设置事务隔离级别。(见下文)

4 对 SELECT、INSERT、UPDATE 和 DELETE 语句使用表级锁定提示。(见下文)

5 配置索引的锁定粒度
可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度

六查看锁的信息

1 执行 EXEC SP_LOCK 报告有关锁的信息
2 查询分析器中按Ctrl+2可以看到锁的信息

七使用注意事项

如何避免死锁
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3 优化程序，检查并避免死锁现象出现；
4 .对所有的脚本和SP都要仔细测试，在正式版本之前。
5 所有的SP都要有错误处理（通过@error）
6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁

解决问题如何对行、表、数据库加锁

八几个有关锁的问题

1 如何锁一个表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK)

加锁语句：
sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ；
加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁

几个例子帮助大家加深印象
设table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它锁
新建两个连接
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select * from table1
where B='b2'
commit tran

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran

在第二个连接中执行以下语句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个事务释放共享锁转为排它锁后才能执行即要等待30秒

3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran

在第二个连接中执行以下语句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran

同时执行，系统会检测出死锁，并中止进程

补充一点:
Sql Server2000支持的表级锁定提示

HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

NOLOCK 语句执行时不发出共享锁，允许脏读，等于 READ UNCOMMITTED事务隔离级别

PAGLOCK 在使用一个表锁的地方用多个页锁

READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

ROWLOCK 强制使用行锁

TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

UPLOCK 强制在读表时使用更新而不用共享锁

应用程序锁:
应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁

处理应用程序锁的两个过程

sp_getapplock 锁定应用程序资源

sp_releaseapplock 为应用程序资源解锁

注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除转自：http://www.infosecurity.org.cn/article/dbsec/sqlserver/23948.html#top ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- 一事务的属性

　　事务具有ACID属性，即 Atomic原子性，Consistent一致性， Isolated隔离性， Durable永久性

　　原子性

　　就是事务应作为一个工作单元，事务处理完成，所有的工作要么都在数据库中保存下来，要么完全
回滚，全部不保留

　　一致性

　　事务完成或者撤销后，都应该处于一致的状态

　　隔离性

　　多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，
不合理的存取和不完整的读取数据

　　永久性

　　事务提交以后，所做的工作就被永久的保存下来

　　二事务并发处理会产生的问题

　　丢失更新

　　当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、
每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。

　　脏读

　　当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。

　　不可重复读

　　当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。

　　幻像读

　　当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。

　　三事务处理类型

　　自动处理事务

　　系统默认每个T－SQL命令都是事务处理由系统自动开始并提交

　　隐式事务

　　当有大量的DDL 和DML命令执行时会自动开始，并一直保持到用户明确提交为止，切换隐式事务可以用SET IMPLICIT_TRANSACTIONS 为连接设置隐性事务模式.当设置为 ON 时，SET IMPLICIT_TRANSACTIONS 将连接设置为隐性事务模式。当设置为 OFF 时，则使连接返回到自动提交事务模式

　　用户定义事务

　　由用户来控制事务的开始和结束命令有: begin tran commit tran rollback tran 命令

　　分布式事务

　　跨越多个服务器的事务称为分布式事务，sql server 可以由DTc microsoft distributed transaction coordinator
来支持处理分布式事务，可以使用 BEgin distributed transaction 命令启动一个分布式事务处理

四事务处理的隔离级别

　　使用SET TRANSACTION ISOLATION LEVEL来控制由连接发出的所有语句的默认事务锁定行为

　　从低到高依次是 READ UNCOMMITTED

　　执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。

　　举例，设table1(A，B，C)

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　新建两个连接

　　在第一个连接中执行以下语句

select * from table1
begin tran
update table1 set c='c'
select * from table1
waitfor delay '00:00:10' --等待10秒
rollback tran
select * from table1

　　在第二个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
print '脏读'
select * from table1
if @@rowcount>0
begin
waitfor delay '00:00:10'
print '不重复读'
select * from table1
end

　　第二个连接的结果

　　脏读

A	B	C
a1	b1	c1
a2	b2	c
a3	b3	c

　　'不重复读'

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　READ COMMITTED

　　指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。

　　在第一个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin tran
print '初始'
select * from table1
waitfor delay '00:00:10' --等待10秒
print '不重复读'
select * from table1
rollback tran

　　在第二个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
update table1 set c='c'

　　第一个连接的结果

　　初始

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　不重复读

A	B	C
a1	b1	c
a2	b2	c
a3	b3	c

　　REPEATABLE READ

　　锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

　　在第一个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
print '初始'
select * from table1
waitfor delay '00:00:10' --等待10秒
print '幻像读'
select * from table1
rollback tran

　　在第二个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
insert table1 select 'a4'，'b4'，'c4'

　　第一个连接的结果

　　初始

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　幻像读

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3
a4	b4	c4

　SERIALIZABLE

　　在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。

　　在第一个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
print '初始'
select * from table1
waitfor delay '00:00:10' --等待10秒
print '没有变化'
select * from table1
rollback tran

　　在第二个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
insert table1 select 'a4'，'b4'，'c4'

　　第一个连接的结果

　　初始

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　没有变化

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　 五事务处理嵌套的语法和对@@TRANCOUNT的影响

BEGIN TRAN @@TRANCOUNT＋1
COMMIT TRAN @@TRANCOUNT－1
ROLLBACK TRAN

转自：http://tech.it168.com/db/s/2006-07-29/200607291423202.shtml ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- C#数据库事务原理及实践

什么是数据库事务
　　数据库事务是指作为单个逻辑工作单元执行的一系列操作。
　　设想网上购物的一次交易，其付款过程至少包括以下几步数据库操作：
　　 · 更新客户所购商品的库存信息
　　 · 保存客户付款信息--可能包括与银行系统的交互
　　 · 生成订单并且保存到数据库中
　　 · 更新用户相关信息，例如购物数量等等
　　正常的情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成功地更新。但是，如果在这一系列过程中任何一个环节出了差错，例如在更新商品库存信息时发生异常、该顾客银行帐户存款不足等，都将导致交易失败。一旦交易失败，数据库中所有信息都必须保持交易前的状态不变，比如最后一步更新用户信息时失败而导致交易失败，那么必须保证这笔失败的交易不影响数据库的状态--库存信息没有被更新、用户也没有付款，订单也没有生成。否则，数据库的信息将会一片混乱而不可预测。
　　数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。
数据库事务的ACID属性
　　事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性：
　　 · 原子性
　　事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。
　　 · 一致性
　　事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。
　　 · 隔离性
　　由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。
　　 · 持久性
　　事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。
DBMS的责任和我们的任务
　　企业级的数据库管理系统（DBMS）都有责任提供一种保证事务的物理完整性的机制。就常用的SQL Server2000系统而言，它具备锁定设备隔离事务、记录设备保证事务持久性等机制。因此，我们不必关心数据库事务的物理完整性，而应该关注在什么情况下使用数据库事务、事务对性能的影响，如何使用事务等等。
　　本文将涉及到在.net框架下使用C#语言操纵数据库事务的各个方面。
体验SQL语言的事务机制
　　作为大型的企业级数据库，SQL Server2000对事务提供了很好的支持。我们可以使用SQL语句来定义、提交以及回滚一个事务。
　　如下所示的SQL代码定义了一个事务，并且命名为"MyTransaction"（限于篇幅，本文并不讨论如何编写SQL语言程序，请读者自行参考相关书籍）：
[code]
DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'
BEGIN TRANSACTION @TranNameGOUSE pubs
GO
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'
GO
COMMIT TRANSACTION MyTransaction
GO
[/code]
　　这里用到了SQL Server2000自带的示例数据库pubs，提交事务后，将为所有畅销计算机书籍支付的版税增加 10%。
　　打开SQL Server2000的查询分析器，选择pubs数据库，然后运行这段程序，结果显而易见。
　　可是如何在C#程序中运行呢？我们记得在普通的SQL查询中，一般需要把查询语句赋值给SalCommand.CommandText属性，这里也就像普通的SQL查询语句一样，将这些语句赋给SqlCommand.CommandText属性即可。要注意的一点是，其中的"GO"语句标志着SQL批处理的结束，编写SQL脚本是需要的，但是在这里是不必要的。我们可以编写如下的程序来验证这个想法：
[code]
//TranSql.csusing System;
using System.Data;
using System.Data.SqlClient;
namespace Aspcn
{
　 public class DbTranSql
　 {
　　 file://将事务放到SQL Server中执行
　　 public void DoTran()
　　 {
　　　 file://建立连接并打开
　　　 SqlConnection myConn=GetConn();myConn.Open();
　　　 SqlCommand myComm=new SqlCommand();
　　　 try
　　　 {
　　　　 myComm.Connection=myConn;
　　　　 myComm.CommandText="DECLARE @TranName VARCHAR(20) ";
　　　　 myComm.CommandText+="SELECT @TranName = 'MyTransaction' ";
　　　　 myComm.CommandText+="BEGIN TRANSACTION @TranName ";
　　　　 myComm.CommandText+="USE pubs ";
　　　　 myComm.CommandText+="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%' ";
　　　　 myComm.CommandText+="COMMIT TRANSACTION MyTransaction ";
　　　　 myComm.ExecuteNonQuery();
　　　 }
　　　 catch(Exception err)
　　　 {
　　　　 throw new ApplicationException("事务操作出错，系统信息："+err.Message);
　　　 }
　　　 finally
　　　 {
　　　　 myConn.Close();
　　　 }
　　 }
　　 file://获取数据连接
　　 private SqlConnection GetConn()
　　 {
　　　 string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password=";
　　　 SqlConnection myConn=new SqlConnection(strSql);
　　　 return myConn;
　　 }
　 }
　 public class Test
　 {
　　 public static void Main()
　　 {
　　　 DbTranSql tranTest=new DbTranSql();
　　　 tranTest.DoTran();
　　　 Console.WriteLine("事务处理已经成功完成。");
　　　 Console.ReadLine();
　　 }
　 }
}
[/code]
　　注意到其中的SqlCommand对象myComm，它的CommandText属性仅仅是前面SQL代码字符串连接起来即可，当然，其中的"GO"语句已经全部去掉了。这个语句就像普通的查询一样，程序将SQL文本事实上提交给DBMS去处理了，然后接收返回的结果（如果有结果返回的话）。
　　很自然，我们最后看到了输出"事务处理已经成功完成"，再用企业管理器查看pubs数据库的roysched表，所有title_id字段以"PC"开头的书籍的royalty字段的值都增加了0.1倍。
　　这里，我们并没有使用ADO.net的事务处理机制，而是简单地将执行事务的SQL语句当作普通的查询来执行，因此，事实上该事务完全没有用到.net的相关特性。
了解.net中的事务机制
　　如你所知，在.net框架中主要有两个命名空间(namespace)用于应用程序同数据库系统的交互：System.Data.SqlClient和System.Data.OleDb。前者专门用于连接Microsoft公司自己的SQL Server数据库，而后者可以适应多种不同的数据库。这两个命名空间中都包含有专门用于管理数据库事务的类，分别是System.Data.SqlClient.SqlTranscation类和System.Data.OleDb.OleDbTranscation类。
　　就像它们的名字一样，这两个类大部分功能是一样的，二者之间的主要差别在于它们的连接机制，前者提供一组直接调用 SQL Server 的对象，而后者使用本机 OLE DB 启用数据访问。事实上，ADO.net 事务完全在数据库的内部处理，且不受 Microsoft 分布式事务处理协调器 (DTC) 或任何其他事务性机制的支持。本文将主要介绍System.Data.SqlClient.SqlTranscation类，下面的段落中，除了特别注明，都将使用System.Data.SqlClient.SqlTranscation类。
事务的开启和提交
　　现在我们对事务的概念和原理都了然于心了，并且作为已经有一些基础的C#开发者，我们已经熟知编写数据库交互程序的一些要点，即使用SqlConnection类的对象的Open()方法建立与数据库服务器的连接，然后将该连接赋给SqlCommand对象的Connection属性，将欲执行的SQL语句赋给它的CommandText属性，于是就可以通过SqlCommand对象进行数据库操作了。对于我们将要编写的事务处理程序，当然还需要定义一个SqlTransaction类型的对象。并且看到SqlCommand对象的Transcation属性，我们很容易想到新建的SqlTransaction对象应该与它关联起来。
　　基于以上认识，下面我们就开始动手写我们的第一个事务处理程序。我们可以很熟练地写出下面这一段程序：
[code]
//DoTran.csusing System;
using System.Data;
using System.Data.SqlClient;
namespace Aspcn
{
　 public class DbTran
　 {
　　 file://执行事务处理
　　 public void DoTran()
　　 {
　　　 file://建立连接并打开
　　　 SqlConnection myConn=GetConn();
　　　 myConn.Open();
　　　 SqlCommand myComm=new SqlCommand();
　　　 SqlTransaction myTran=new SqlTransaction();
　　　 try
　　　 {
　　　 myComm.Connection=myConn;
　　　 myComm.Transaction=myTran;
　　　　 file://定位到pubs数据库　
　　　　 myComm.CommandText="USE pubs";
　　　　 myComm.ExecuteNonQuery();
　　　　 file://更新数据
　　　　 file://将所有的计算机类图书
　　　　 myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%'";
　　　　 myComm.ExecuteNonQuery();//提交事务
　　　　 myTran.Commit();
　　　 }
　　　 catch(Exception err)
　　　 {
　　　　 throw new ApplicationException("事务操作出错，系统信息："+err.Message);
　　　 }
　　　 finally
　　　 {
　　　　 myConn.Close();
　　　 }
　　 }
　　 file://获取数据连接
　　 private SqlConnection GetConn()
　　 {
　　　 string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password=";
　　　 SqlConnection myConn=new SqlConnection(strSql);
　　　 return myConn;
　　 }
　 }
　 public class Test{public static void Main()
　 {
　　 DbTran tranTest=new DbTran();
　　 tranTest.DoTran();
　　 Console.WriteLine("事务处理已经成功完成。");
　　 Console.ReadLine();
　 }
}
}
[/code]
　　显然，这个程序非常简单，我们非常自信地编译它，但是，出乎意料的结果使我们的成就感顿时烟消云散：
　　 error CS1501: 重载"SqlTransaction"方法未获取"0"参数
　　是什么原因呢？注意到我们初始化的代码：
[code]
SqlTransaction myTran=new SqlTransaction();
[/code]
　　显然，问题出在这里，事实上，SqlTransaction类并没有公共的构造函数，我们不能这样新建一个SqlTrancaction类型的变量。在事务处理之前确实需要有一个SqlTransaction类型的变量，将该变量关联到SqlCommand类的Transcation属性也是必要的，但是初始化方法却比较特别一点。在初始化SqlTransaction类时，你需要使用SqlConnection类的BeginTranscation()方法：
[code]
SqlTransaction myTran; myTran=myConn.BeginTransaction();
[/code]
　　该方法返回一个SqlTransaction类型的变量。在调用BeginTransaction()方法以后，所有基于该数据连接对象的SQL语句执行动作都将被认为是事务MyTran的一部分。同时，你也可以在该方法的参数中指定事务隔离级别和事务名称，如：
[code]
SqlTransaction myTran;
myTran=myConn.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
[/code]
　　关于隔离级别的概念我们将在随后的内容中探讨，在这里我们只需牢记一个事务是如何被启动，并且关联到特定的数据链接的。
　　先不要急着去搞懂我们的事务都干了些什么，看到这一行：
[code]
myTran.Commit();
[/code]
　　是的，这就是事务的提交方式。该语句执行后，事务的所有数据库操作将生效，并且为数据库事务的持久性机制所保持--即使系统在这以后发生致命错误，该事务对数据库的影响也不会消失。
　　对上面的程序做了修改之后我们可以得到如下代码（为了节约篇幅，重复之处已省略，请参照前文）：
[code]
//DoTran.cs……｝
file://执行事务处理
public void DoTran()
{
　 file://建立连接并打开
　 SqlConnection myConn=GetConn();
　 myConn.Open();
　 SqlCommand myComm=new SqlCommand();
　 [url=file://SqlTransaction]file://SqlTransaction[/url] myTran=new SqlTransaction();
　 file://注意，SqlTransaction类无公开的构造函数
　 SqlTransaction myTran;
　 file://创建一个事务
　 myTran=myConn.BeginTransaction();
　 try
　 {
　　 file://从此开始，基于该连接的数据操作都被认为是事务的一部分
　　 file://下面绑定连接和事务对象
　　 myComm.Connection=myConn;
　　 myComm.Transaction=myTran; file://定位到pubs数据库
　　 myComm.CommandText="USE pubs";
　　 myComm.ExecuteNonQuery();//更新数据
　　 file://将所有的计算机类图书
　　 myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%'";
　　 myComm.ExecuteNonQuery();
　　 file://提交事务
　　 myTran.Commit();
　 }
　 catch(Exception err)
　 {
　　 throw new ApplicationException("事务操作出错，系统信息："+err.Message);
　 }
　 finally
　 {
　　 myConn.Close();
　 }
}
……
[/code]
　　到此为止，我们仅仅掌握了如何开始和提交事务。下一步我们必须考虑的是在事务中可以干什么和不可以干什么。

另一个走向极端的错误
　　满怀信心的新手们可能为自己所掌握的部分知识陶醉不已，刚接触数据库库事务处理的准开发者们也一样，踌躇满志地准备将事务机制应用到他的数据处理程序的每一个模块每一条语句中去。的确，事务机制看起来是如此的诱人——简洁、美妙而又实用，我当然想用它来避免一切可能出现的错误——我甚至想用事务把我的数据操作从头到尾包裹起来。
　　看着吧，下面我要从创建一个数据库开始：

[code]
using System;
using System.Data;
using System.Data.SqlClient;
namespace Aspcn
{
　 public class DbTran
　 {
　　 file://执行事务处理
　　 public void DoTran()
　　 {
　　　 file://建立连接并打开
　　　 SqlConnection myConn=GetConn();
　　　 myConn.Open();
　　　 SqlCommand myComm=new SqlCommand();
　　　 SqlTransaction myTran;
　　　 myTran=myConn.BeginTransaction();
　　　 file://下面绑定连接和事务对象
　　　 myComm.Connection=myConn;
　　　 myComm.Transaction=myTran;
　　　 file://试图创建数据库TestDB
　　　 myComm.CommandText="CREATE database TestDB";
　　　 myComm.ExecuteNonQuery();
　　　 file://提交事务
　　　 myTran.Commit();
　　 }
　　 file://获取数据连接
　　 private SqlConnection GetConn()
　　 {
　　　 string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password=";
　　　 SqlConnection myConn=new SqlConnection(strSql);
　　　 return myConn;
　　 }
　 }
　 public class Test
　 {
　　 public static void Main()
　　 {
　　　 DbTran tranTest=new DbTran();
　　　 tranTest.DoTran();
　　　 Console.WriteLine("事务处理已经成功完成。");
　　　 Console.ReadLine();
　　 }
　 }
}
　　//---------------
[/code]
　　未处理的异常： System.Data.SqlClient.SqlException: 在多语句事务内不允许使用 CREATE DATABASE 语句。
[code]
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Aspcn.DbTran.DoTran()
at Aspcn.Test.Main()
[/code]
　　注意，如下的SQL语句不允许出现在事务中：

ALTER DATABASE  修改数据库
BACKUP LOG  备份日志
CREATE DATABASE  创建数据库
DISK INIT  创建数据库或事务日志设备
DROP DATABASE  删除数据库
DUMP TRANSACTION  转储事务日志
LOAD DATABASE  装载数据库备份复本
LOAD TRANSACTION  装载事务日志备份复本
RECONFIGURE  更新使用 sp_configure 系统存储过程更改的配置选项的当前配置（sp_configure 结果集中的 config_value 列）值。
RESTORE DATABASE  还原使用BACKUP命令所作的数据库备份
RESTORE LOG  还原使用BACKUP命令所作的日志备份
UPDATE STATISTICS  在指定的表或索引视图中，对一个或多个统计组（集合）有关键值分发的信息进行更新

　　除了这些语句以外，你可以在你的数据库事务中使用任何合法的SQL语句。
事务回滚
　　事务的四个特性之一是原子性，其含义是指对于特定操作序列组成的事务，要么全部完成，要么就一件也不做。如果在事务处理的过程中，发生未知的不可预料的错误，如何保证事务的原子性呢？当事务中止时，必须执行回滚操作，以便消除已经执行的操作对数据库的影响。
　　一般的情况下，在异常处理中使用回滚动作是比较好的想法。前面，我们已经得到了一个更新数据库的程序，并且验证了它的正确性，稍微修改一下，可以得到：
[code]
//RollBack.cs
using System;
using System.Data;
using System.Data.SqlClient;
namespace Aspcn
{
　 public class DbTran
　 {
　　 file://执行事务处理
　　 public void DoTran()
　　 {
　　　 file://建立连接并打开
　　　 SqlConnection myConn=GetConn();
　　　 myConn.Open();
　　　 SqlCommand myComm=new SqlCommand();
　　　 SqlTransaction myTran;
　　　 file://创建一个事务
　　　 myTran=myConn.BeginTransaction();
　　　 file://从此开始，基于该连接的数据操作都被认为是事务的一部分
　　　 file://下面绑定连接和事务对象
　　　 myComm.Connection=myConn;
　　　 myComm.Transaction=myTran;
　　　 try
　　　 {
　　　　 file://定位到pubs数据库
　　　　 myComm.CommandText="USE pubs";
　　　　 myComm.ExecuteNonQuery();
　　　　 myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%'";
　　　　 myComm.ExecuteNonQuery();
　　　　 file://下面使用创建数据库的语句制造一个错误
　　　　 myComm.CommandText="Create database testdb";
　　　　 myComm.ExecuteNonQuery();
　　　　 myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.20 WHERE title_id LIKE 'Ps%'";
　　　　 myComm.ExecuteNonQuery();
　　　　 file://提交事务
　　　　 myTran.Commit();
　　　 }
　　　 catch(Exception err)
　　　 {
　　　　 myTran.Rollback();
　　　　 Console.Write("事务操作出错，已回滚。系统信息："+err.Message);
　　　 }
　　 }
　　 file://获取数据连接
　　 private SqlConnection GetConn()
　　 {
　　　 string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password=";
　　　 SqlConnection myConn=new SqlConnection(strSql);
　　　 return myConn;
　　 }
　 }
　 public class Test
　 {
　　 public static void Main()
　　 {
　　　 DbTran tranTest=new DbTran();
　　　 tranTest.DoTran();
　　　 Console.WriteLine("事务处理已经成功完成。");
　　　 Console.ReadLine();
　　 }
　 }
}
[/code]
　　首先，我们在中间人为地制造了一个错误——使用前面讲过的Create database语句。然后，在异常处理的catch块中有如下语句：
[code]
myTran.Rollback();
[/code]
　　当异常发生时，程序执行流跳转到catch块中，首先执行的就是这条语句，它将当前事务回滚。在这段程序可以看出，在Create database之前，已经有了一个更新数据库的操作——将pubs数据库的roysched表中的所有title_id字段以“PC”开头的书籍的royalty字段的值都增加0.1倍。但是，由于异常发生而导致的回滚使得对于数据库来说什么都没有发生。由此可见，Rollback()方法维护了数据库的一致性及事务的原子性。
使用存储点
　　事务只是一种最坏情况下的保障措施，事实上，平时系统的运行可靠性都是相当高的，错误很少发生，因此，在每次事务执行之前都检查其有效性显得代价太高——绝大多数的情况下这种耗时的检查是不必要的。我们不得不想另外一种办法来提高效率。
　　事务存储点提供了一种机制，用于回滚部分事务。因此，我们可以不必在更新之前检查更新的有效性，而是预设一个存储点，在更新之后，如果没有出现错误，就继续执行，否则回滚到更新之前的存储点。存储点的作用就在于此。要注意的是，更新和回滚代价很大，只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用存储点才会非常有效。
　　使用.net框架编程时，你可以非常简单地定义事务存储点和回滚到特定的存储点。下面的语句定义了一个存储点“NoUpdate”：
[code]
myTran.Save("NoUpdate");
[/code]
　　当你在程序中创建同名的存储点时，新创建的存储点将替代原有的存储点。
　　在回滚事务时，只需使用Rollback()方法的一个重载函数即可：
[code]
　　 myTran.Rollback("NoUpdate");
[/code]
　　下面这段程序说明了回滚到存储点的方法和时机： [code]
using System;
using System.Data;
using System.Data.SqlClient;
namespace Aspcn
{
　 public class DbTran
　 {　
　　 file://执行事务处理
　　 public void DoTran()
　　 {
　　　 file://建立连接并打开
　　　 SqlConnection myConn=GetConn();
　　　 myConn.Open();
　　　 SqlCommand myComm=new SqlCommand();
　　　 SqlTransaction myTran;
　　　 file://创建一个事务
　　　 myTran=myConn.BeginTransaction();
　　　 file://从此开始，基于该连接的数据操作都被认为是事务的一部分
　　　 file://下面绑定连接和事务对象
　　　 myComm.Connection=myConn;
　　　 myComm.Transaction=myTran;
　　　 try
　　　 {
　　　　 myComm.CommandText="use pubs";
　　　　 myComm.ExecuteNonQuery();
　　　　 myTran.Save("NoUpdate");
　　　　 myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%'";
　　　　 myComm.ExecuteNonQuery();
　　　　 file://提交事务
　　　　 myTran.Commit();
　　　 }
　　　 catch(Exception err)
　　　 {
　　　　 file://更新错误，回滚到指定存储点
　　　　 myTran.Rollback("NoUpdate");
　　　　 throw new ApplicationException("事务操作出错，系统信息："+err.Message);
　　　 }
　　 }
　　 file://获取数据连接
　　 private SqlConnection GetConn()
　　 {
　　　 string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password=";
　　　 SqlConnection myConn=new SqlConnection(strSql);
　　　 return myConn;
　　 }
　 }
　 public class Test
　 {
　　 public static void Main()
　　 {
　　　 DbTran tranTest=new DbTran();
　　　 tranTest.DoTran();
　　　 Console.WriteLine("事务处理已经成功完成。");
　　　 Console.ReadLine();
　　 }
　 }
}

[/code]
　　很明显，在这个程序中，更新无效的几率是非常小的，而且在更新前验证其有效性的代价相当高，因此我们无须在更新之前验证其有效性，而是结合事务的存储点机制，提供了数据完整性的保证。
隔离级别的概念
　　企业级的数据库每一秒钟都可能应付成千上万的并发访问，因而带来了并发控制的问题。由数据库理论可知，由于并发访问，在不可预料的时刻可能引发如下几个可以预料的问题：
脏读：包含未提交数据的读取。例如，事务1 更改了某行。事务2 在事务1 提交更改之前读取已更改的行。如果事务1 回滚更改，则事务2 便读取了逻辑上从未存在过的行。
不可重复读取：当某个事务不止一次读取同一行，并且一个单独的事务在两次（或多次）读取之间修改该行时，因为在同一个事务内的多次读取之间修改了该行，所以每次读取都生成不同值，从而引发不一致问题。
幻象：通过一个任务，在以前由另一个尚未提交其事务的任务读取的行的范围中插入新行或删除现有行。带有未提交事务的任务由于该范围中行数的更改而无法重复其原始读取。
　　如你所想，这些情况发生的根本原因都是因为在并发访问的时候，没有一个机制避免交叉存取所造成的。而隔离级别的设置，正是为了避免这些情况的发生。事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。
　　根据隔离级别的不同，DBMS为并行访问提供不同的互斥保证。在SQL Server数据库中，提供四种隔离级别：未提交读、提交读、可重复读、可串行读。这四种隔离级别可以不同程度地保证并发的数据完整性：

隔离级别	丢失的更新	脏读	不可重复读取	幻像
未提交读	否	是	是	是
提交读	否	否	是	是
可重复读	否	否	否	是
可串行读	否	否	否	否

　　可以看出，“可串行读”提供了*别的隔离，这时并发事务的执行结果将与串行执行的完全一致。如前所述，*别的隔离也就意味着最低程度的并发，因此，在此隔离级别下，数据库的服务效率事实上是比较低的。尽管可串行性对于事务确保数据库中的数据在所有时间内的正确性相当重要，然而许多事务并不总是要求完全的隔离。例如，多个作者工作于同一本书的不同章节。新章节可以在任意时候提交到项目中。但是，对于已经编辑过的章节，没有编辑人员的批准，作者不能对此章节进行任何更改。这样，尽管有未编辑的新章节，但编辑人员仍可以确保在任意时间该书籍项目的正确性。编辑人员可以查看以前编辑的章节以及最近提交的章节。这样，其它的几种隔离级别也有其存在的意义。
　　在.net框架中，事务的隔离级别是由枚举System.Data.IsolationLevel所定义的：　　
[code]
[Flags]
[Serializable]
public enum IsolationLevel
[/code]
　　其成员及相应的含义如下：

成员  含义
Chaos  无法改写隔离级别更高的事务中的挂起的更改。
ReadCommitted  在正在读取数据时保持共享锁，以避免脏读，但是在事务结束之前可以更改数据，从而导致不可重复的读取或幻像数据。
ReadUncommitted  可以进行脏读，意思是说，不发布共享锁，也不接受独占锁。
RepeatableRead  在查询中使用的所有数据上放置锁，以防止其他用户更新这些数据。防止不可重复的读取，但是仍可以有幻像行。
Serializable  在DataSet上放置范围锁，以防止在事务完成之前由其他用户更新行或向数据集中插入行。
Unspecified  正在使用与指定隔离级别不同的隔离级别，但是无法确定该级别。

　　显而意见，数据库的四个隔离级别在这里都有映射。
　　默认的情况下，SQL Server使用ReadCommitted(提交读)隔离级别。
　　关于隔离级别的最后一点就是如果你在事务执行的过程中改变了隔离级别，那么后面的命名都在最新的隔离级别下执行——隔离级别的改变是立即生效的。有了这一点，你可以在你的事务中更灵活地使用隔离级别从而达到更高的效率和并发安全性。
最后的忠告
　　无疑，引入事务处理是应对可能出现的数据错误的好方法，但是也应该看到事务处理需要付出的巨大代价——用于存储点、回滚和并发控制所需要的CPU时间和存储空间。
　　本文的内容只是针对Microsoft SQL Server数据库的，对应于.net框架中的System.Data.SqlClient命名空间，对于使用OleDb的情形，具体的实现稍有不同，但这不是本文的内容

秒客网

数据库的事务和锁机制（SQL Server）

相关文章