存储过程中的TRY和事务

[wdxgdiy]

存储过程里的 TRY和事务 咋写呢。杂结合捏。小弟初学。大家指点下

[--小F--]

SQL里面仍然有TRY。。CATCH

[--小F--]

每个 TRY...CATCH 构造都必须位于一个批处理、存储过程或触发器中。例如，不能将 TRY 块放置在一个批处理中而将关联的 CATCH 块放置在另一个批处理中。下面的脚本将生成一个错误：
 复制代码
BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

CATCH 块必须紧跟 TRY 块。 
TRY…CATCH 构造可以是嵌套式的。这意味着可以将 TRY…CATCH 构造放置在其他 TRY 块和 CATCH 块内。当嵌套的 TRY 块中出现错误时，程序控制将传递到与嵌套的 TRY 块关联的 CATCH 块。 
若要处理给定的 CATCH 块中出现的错误，请在指定的 CATCH 块中编写 TRY...CATCH 块。 
TRY...CATCH 块不处理导致数据库引擎关闭连接的严重性为 20 或更高的错误。但是，只要连接不关闭，TRY...CATCH 就会处理严重性为 20 或更高的错误。 
严重性为 10 或更低的错误被视为警告或信息性消息，TRY...CATCH 块不处理此类错误。 
即使批处理位于 TRY...CATCH 构造的作用域内，关注消息仍将终止该批处理。分布式事务失败时，Microsoft 分布式事务处理协调器 (MS DTC) 将发送关注消息。MS DTC 用于管理分布式事务

[wdxgdiy]

那遇到异常 怎样用事务进行回滚呢 （我知道有TRY 提问前就在网上看到了，就是没搜到事务）

[wzy_love_sly]

BEGIN TRAN
  BEGIN TRY
     --SQL
     COMMIT TRAN
  END TRY
  BEGIN CATCH
     ROLLBACK
  END CATCH

[liangCK]

CREATE TABLE tb(id int,val int CONSTRAINT chk CHECK (val > 100))
GO
BEGIN TRANSACTION 

BEGIN TRY
    INSERT tb VALUES(1,101)
    INSERT tb VALUES(2,99)
    COMMIT
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 0
        ROLLBACK TRANSACTION
END CATCH



SELECT * FROM tb;

DROP TABLE tb;

[dawugui]

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 TRAN

[wdxgdiy]

[Quote=引用 4 楼 wzy_love_sly 的回复:]
SQL codeBEGINTRANBEGIN TRY--SQLCOMMITTRANEND TRYBEGIN CATCHROLLBACKEND CATCH
[/Quote]
[Quote=引用 5 楼 liangck 的回复:]
SQL codeCREATETABLE tb(idint,valintCONSTRAINT chkCHECK (val>100))GOBEGINTRANSACTIONBEGIN TRYINSERT tbVALUES(1,101)INSERT tbVALUES(2,99)COMMITEND TRYBEGIN CATCHIF ERROR_NUMBER()<>0ROLLBACKTRANSACTIONEN¡­
[/Quote]
消息 170，级别 15，状态 1，第 2 行
第 2 行: 'TRY' 附近有语法错误。
消息 156，级别 15，状态 1，第 5 行
在关键字 'END' 附近有语法错误。

[liangCK]

你的是2005或以上版本?.2000不支持try

[wzy_love_sly]

你是sql server 2000？2005开始才支持

[wdxgdiy]

[Quote=引用 8 楼 liangck 的回复:]
你的是2005或以上版本?.2000不支持try
[/Quote]

倒。原来是这样 谢了

[中国风]

參照聯機，SQL用begin try
1、
begin try
begin tran

..SQL
commit tran
end try
begin catch
...
rollback 
end catch

2、
set xact_abort on ;

begin try
begin tran

..SQL
commit tran
end try
begin catch
...
	if (xact_state())=-1
		rollback tran
	if (xact_state())=1
		commit tran
end catch
3、
begin tran
	begin try
SQL
	end try
begin catch


	if @@trancount>0
		rollback tran
end catch
	if @@trancount>0
		commit tran