SQL事务的使用

一个事务被定义为作为一个单元执行的符合所谓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)

GO

SELECT @@TRANCOUNT AS [Transaction Count]

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

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

ROLLBACK TRAN

GO

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，因为过程已经处于一个事务中并且调用实例可以在错误发生时回滚事务。

转载于:https://www.cnblogs.com/sherrys/archive/2007/03/02/661186.html