SQL事务的使用

最新推荐文章于 2021-02-11 03:48:52 发布

weixin_30394981

最新推荐文章于 2021-02-11 03:48:52 发布

阅读量61

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/leilei821006/archive/2008/12/30/1364911.html

版权

一个事务被定义为作为一个单元执行的符合所谓ACID属性的一序列的操作。

原子性 每一个事务是一个工作单元。它不能被分割成更小的部分。这个属性意味着在事务中定义的一切数据更改要么都完成，要么都不完成。
一致性 一个事务不能违背定义在数据库中的任何完整性检查。为了维护一致性，所有的规则、约束、检查和触发都会应用在事务中。由于所有的数据更改在事务期间内进行，这些数据在事务开始和事务结束前会被确保为一致的。
隔离性事务必须与其他事务进行的数据更改相隔离。这意味着没有其他操作可以改变中间态(没有提交的)的数据。为了避免中间态数据被更改，事务必须要么等待来自其他事务的更改被提交，要么只能查看到处于上一个提交状态的数据。
持久性 在一个事务完成，并且客户端应用程序已经被提示这个事务已经成功完成后，无论发生任何系统错误，这些更改的数据将永久存在。

使用事物时运用里面的错误处理

BEGIN TRY

BEGIN TRAN

INSERT INTO table1 (i,col1,col2)

VALUES (1,'First row','First row');

INSERT INTO table1 (i,col1,col2)

VALUES (2,NULL,'Second row');

INSERT INTO table1 (i,col1,col2)

VALUES (3,'Third row','Third row');

COMMIT TRAN;

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

RAISERROR('Error in Transaction!',14,1)

ROLLBACK TRAN

-- 获得一个返回所有错误信息的记录和一个自定义的、指出已发生错误的信息。

-- DECLARE @er nvarchar(max)

-- SET @er = 'Error: '+ ERROR_MESSAGE();

-- RAISERROR(@er,14,1);

-- ROLLBACK TRAN

END CATCH;

使用隐式事务

-- 键入并执行以下语句来设置连接为隐式事务模式

SET IMPLICIT_TRANSACTIONS ON

执行以下代码创建一个表检验是否已启动事务：

CREATE TABLE T1

(i INT PRIMARY KEY)

用@@TRANCOUNT来测试是否已经打开一个事务。执行如下所示的SELECT语句：

SELECT @@TRANCOUNT AS [Transaction Count]

结果是1，意思是当前连接已经打开了一个事务。0的意思是当前没有事务，一个大于1的数的意思是有嵌套事务。

现在执行以下语句在表中插入一行并再次检查@@TRANCOUNT

INSERT INTO T1 VALUES(5)

SELECT @@TRANCOUNT AS [Transaction Count]

@@TRANCOUNT的值仍然是1。由于已经有一个打开的事务，因此SQL Server没有开始一个新的事务。

现在执行以下语句回滚这个事务并再次检查@@TRANCOUNT。可以看出，在ROLLBACK TRAN 语句执行之后，@@TRANCOUNT 的值变成了0。

ROLLBACK TRAN

SELECT @@TRANCOUNT AS [Transaction Count]

尝试对表T1执行SELECT 语句：

SELECT * FROM T1

由于表不复存在，所以会得到一个错误信息。这个隐式事务起始于CREATE TABLE语句，并且ROLLBACK TRAN语句取消了第一个语句后所做的所有工作。

执行以下代码关闭隐式事务：

SET IMPLICIT_TRANSACTIONS OFF

事务的嵌套

PRINT 'Trancount before transaction: ' + CAST(@@TRANCOUNT as char(1))

BEGIN TRAN

PRINT 'After first BEGIN TRAN: ' + CAST(@@TRANCOUNT as char(1))

BEGIN TRAN

PRINT 'After second BEGIN TRAN: ' + CAST(@@TRANCOUNT as char(1))

COMMIT TRAN

PRINT 'After first COMMIT TRAN: ' + CAST(@@TRANCOUNT as char(1))

COMMIT TRAN

PRINT 'After second COMMIT TRAN: ' + CAST(@@TRANCOUNT as char(1))

在结果中，可以看到每一个BEGIN TRAN 语句都会使@@TRANCOUNT增加1并且每一个COMMIT TRAN语句都会使其减少1。如前所述，一个值为0的@@TRANCOUNT意味着没有打开的事务。因此，在@@TRANCOUNT值从1降到0时结束的事务发生在外层事务提交的时候。因此，每一个内部事务都需要提交。由于事务起始于第一个BEGIN TRAN并结束于最后一个COMMIT TRAN，因此最外层的事务决定了是否完全提交内部的事务。如果最外层的事务没有被提交，其中嵌套的事务也不会被提交。

键入并执行以下批来检验事务回滚时所发生的情况：

BEGIN TRAN

PRINT 'After 1st BEGIN TRAN: ' + CAST(@@TRANCOUNT as char(1))

BEGIN TRAN

PRINT 'After 2nd BEGIN TRAN: ' + CAST(@@TRANCOUNT as char(1))

BEGIN TRAN

PRINT 'After 3rd BEGIN TRAN: ' + CAST(@@TRANCOUNT as char(1))

UPDATE Data1

SET value1 = 1000000

WHERE Id = 1

COMMIT TRAN

PRINT 'After first COMMIT TRAN: ' + CAST(@@TRANCOUNT as char(1))

ROLLBACK TRAN

PRINT 'After ROLLBACK TRAN: ' + CAST(@@TRANCOUNT as char(1))

SELECT * FROM Data1

WHERE Id = 1;

在这个示例中，数据表Data1在一个嵌套事务中被更新，这会被立即提交。然后ROLLBACK TRAN被执行。ROLLBACK TRAN将@@TRANCOUNT减为0并回滚整个事务及其中嵌套的事务，无论它们是否已经被提交。因此，嵌套事务中所做的更新被回滚，数据没有任何改变。

始终牢记，在嵌套的事务中，只有最外层的事务决定着是否提交内部事务。每一个COMMIT TRAN语句总是应用于最后一个执行的BEGIN TRAN。因此，对于每一个COMMIT TRAN，必须调用一个COMMIT TRAN来提交事务。ROLLBACK TRAN语句总是属于最外层的事务，并且因此总是回滚整个事务而不论其中打开了多少嵌套事务。正因为此，管理嵌套事务很复杂。如果每一个嵌套存储过程都在自身中开始一个事务，那么嵌套事务大部分会发生在嵌套存储过程中。要避免嵌套事务，可以在过程开始处检查@@TRANCOUNT的值，以此来确定是否需要开始一个事务。如果@@TRANCOUNT大于0，因为过程已经处于一个事务中并且调用实例可以在错误发生时回滚事务。

create procedure up_aa
as
begin tran aa
..
exec up_bb
if .. then rollback aa
commit tran aa

create procedure up_bb
as
begin tran bb
....
if .. then rollback tran bb （在这里报错）
commit tran bb
在上面这段话中，执行存储过程up_aa时就会报错。
如果是循环嵌套事务，则up_bb应写成以下：

create procedure up_bb
as
begin tran bb
save trans qq //将第二层的事务用这种方法保存，具体的语法是否为save tran我倒是忘记了
..
if .. then rollback tran qq //在RollBack时要RollBack QQ
commit tran bb //在RollBack后一定要跟上Commit Tran BB，注意这里的BB和QQ的区别

commit tran bb

嵌套的事务处理的确是复杂的. 不是一两句话就能说清楚.

我上面的写法如果是事务的提交, 不做更多的事情，是有可能在有错误发生的时候提交部分数据的变更, 导致数据错误.

但不表示那样的写法就不能用, 它取决于内层事务代码的写法. 我只是给出一种方法，并不表示在提交/回卷事务的前后不去做任何事情来决定批的流程。

如果在内层的事务中有错误时进行了回卷(用rollback tran, 而不是用 save tran A - rollback tran A 的形式), 直接执行外层事务的commit/rollback就会出错, 这样的情况, 就需要判断@@TRANCOUNT了。这时，@@TRANCOUNT也成为用来判断内层事务的代码执行中是否发生过错误的标志。