sql server事务全攻略(转载)

2007年2月

sql server事务全攻略

一事务的属性

事务具有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 c
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 TR
---------------------------------------------------------------

事务

比如在sql中，通过设置隔离级别，可以有read uncommitted read commit, read committed,repeatable read,serializzbel.那么在oracle中呢，oracle中的机制和sql中的又有什么优缺点吗

而且，在事务中，我们都知道，锁是保证一致性的，在sql中,我写一个简要的事务
create table table1 (s_id int primary key)
create table table2 (n_id int primary key)
insert into table1 values (1)
go
declare @i int
declare @n_id int
set @i=0
while (@i<5000)
begin
begin tran
select @n_id=s_id from table1
update table1 set s_id=@n_id+1
if @@error<>0
begin
print 'update error'
rollback
end
insert into table2 values (@n_id)
if @@error<>0
begin
print 'insert error'
rollback
end
set @i=@i+1
commit
end
go

如果两个session同时执行这段话,肯定会在update何insert出现大量的错,这是因为这两个事务开始的时候，都是以select开始的，所以加的都是共享锁，所以导致两个事务读出来的有可能是同一个数，从而在在update何insert时会发生违反主键约束的错误。
但是，只需把select和update换一下位置，就可以保证不发生上述情况，见下
create table table1 (s_id int primary key)
create table table2 (n_id int primary key)
insert into table1 values (1)
go
declare @i int
declare @n_id int
set @i=0
while (@i<5000)
begin
begin tran
update table1 set s_id=s_id+1
if @@error<>0
begin
print 'update error'
rollback
end
select @n_id=s_id from table1
insert into table2 values (@n_id)
if @@error<>0
begin
print 'insert error'
rollback
end
set @i=@i+1
commit
end
go
互换了一下位置，就不会出现select取重号的问题，这是事务先进行的是update语句，而update会加一个排他锁，这是很关键，因为它不会像select的共享锁一样，用完会释放，排他锁会一直维持到事务的结束（当然这种排他锁是在同一资源上的排他锁，而不是转化成了完全的事务锁，否则就蜕化成了serializable了），所以实际上，在update之后的select还是有排他锁保护的，所以不会出现重号的问题。
可见在sql中锁的概念并不只限于资源锁，而是可以延伸到事务层面的事务锁。
当然我不知道，这是sql在事务serializable和资源锁之间的一种故意的灵活性，还是出于一些别的目的，或者在别的方面还有更更加明显的体现或作用。
可以肯定的就是利用这种锁的机制，改变update何select的顺序，起到的效果比serializable要好得多，因为没有在一些没必要顺序化的语句间保持顺序,而是在需要保证资源一致性的地方用了顺序处理，而在涉及不同资源的时候发挥了并发的特点，可以说是最有效的一种解决方案。

sql server编程结构（五）--事务(trancation)

什么是事务:事务是用户对数据库进行的一系列的操作的集合,所以事务就是一系列的操作.
事务的特点:位于事务里面的操作要么都做,要么都不做.
事务的运行模式:sql server以三种模式来确定一个事务
A:自动提交事务:每条单独的一个sql语句就是一个事务[证明]
B:显示事务:必须以[begin tran]语句显式开始,以[commit tran]或[rollback tran]语句显示结束.
C:隐性事务:在前一个事务完成时新事务隐式启动,但每个事务仍以[COMMIT]或[ROLLBACK]语句显示完成.
[SET IMPLICIT_TRANSACTIONS ON] 必须打开
[当设置为ON时,SET IMPLICIT_TRANSACTIONS将连接设置为隐性事务模式.当设置为OFF时,则使连接返回到自动提交事务模式]
比如:SET IMPLICIT_TRANSACTIONSON
begin tran
update work set 基本工资=基本工资+100
commit tran
delete work
rollback tran [等一下再讲解]

显示事务:1:begin tran [开始一个事务]
2:commit tran[结束一个事务]
3:rollback tran[回滚一个事务]
4:save tran 名字 [设置保存点]
比如:begin tran
update work set 基本工资=基本工资+100
查看一下:select * from work
如果这个时候后悔了,可执行rollback tran来进行回滚事务(取消).如果确认了就执行commit tran确认前滚事务(执行事务)
试着执行以下语句:insert work(职工号) values(\'034\')
然后执行:commit tran或rollback tran看一下系统会提示什么
说明:A:只有运用了begin tran的事务,才可以用运用rollback tran或commit tran来回滚或前滚事务[隐性事务除外]
B:如果一个事务已经运用了commit tran则不能再运用rollback tran.
C:可以在一些操作中设置一些保存点:只取消保存点之后的操作.
比如:begin tran
update work set 基本工资=基本工资+100 where 性别=\'男\'
save tran ss[设置保存点]
delete work
如果后悔了,可以执行:1:[rollback tran ss]则从[save tran ss]开始,下面的语句将取消
2:如果执行[rollback tran]将取消整个事务

1:rollback tran 保存点 [从下到上,一直到保存点之间的语句被取消]
2:先[rollback tran 保存点名],再[rollback tran],结果:整个事务取消.相当于从一开始就[rollback tran]整个事务.
3:如果先执行[rollback tran保存点名],再执行[commit tran],结果:是从保存点到[begin tran]之间的语句被提交.
4:可以设置多个保存点.
5:前滚保存点就是前滚整个事务.

conn.BeginTrans

On Error GoTo err:

conn.CommitTrans

err:
conn.RollbackTrans