SQL Transcation的一些总结分享

2024-08-31 00:44:52

字体：大中小

来源：转载

供稿：网友

1.1.1 摘要
相信大家对于SQL Transcation再熟悉不过，它确保了数据库的数据一致性和安全性，尤其在对数据执行增删时，如果发生异常和错误它就会触发事务回滚，从而确保了我们数据的一致性和安全性，下面我们将通过分四部分介绍事件（Transcation）。

1.1.2 正文
首先让我们通过一个具体的例子介绍Transcation的使用，假如我们的数据库中有一个表UserInfo，它包含三个字段分别为：UserID(自增)、UserName (nvarchar)和LuckyNumber (tinyint)，如下图所示：

SQL Transcation的一些总结分享

图2执行存储过程的消息

通过上图我们知道在执行存储过程中发生了异常，而且是由于值“20111111”数据超出了tinyint的范围产生的，现在让我们看一下数据插入的情况。

SQL Transcation的一些总结分享

图4 UserInfo表中数据

我们发现结果和没有添加Transcation处理一样，数据依然插入到表中。这究竟是什么原因呢？也许细心的你已经发现了，我们没有添加事务回滚――ROLLBACK。
但我们究竟要在哪里添加事务回滚（ROLLBACK）呢？或更具体地说：“究竟什么时候我们要触发事务回滚（ROLLBACK）呢”？
由于我们数据插入失败是因为插入过程发生了异常情况，那么我们就要捕获异常和处理异常，那就是TRY/CATCH的设计了，好让我们继续完善我们的存储过程吧。

复制代码代码如下:

-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO

现在我们给存储过程添加了异常处理机制TRY/CATCH（注意：SQLSERVER 2005或之后的版本才支持TRY/CATCH），接下来让我们再执行一次存储过程。

SQL Transcation的一些总结分享

图6 执行存储过程消息

SQL Transcation的一些总结分享 -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);

COMMIT TRANSACTION

END TRY
BEGIN CATCH
PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH

END
GO

-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo

AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);

COMMIT TRANSACTION

END TRY
BEGIN CATCH
PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH

END
GO

上面我们通过输出事务的计数（@@TRANCOUNT）来查看在事务回滚时，事务计数器的变化。

SQL Transcation的一些总结分享 -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT 'Rolled back successful in SPAddDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT 'Rolled back successful in SPMultiDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO

现在我们增加了事务计数器的判断，当计数器为0时，不进行事务回滚，这样就没有了之前事务计数器异常了。

SQL Transcation的一些总结分享

图9 存储过程执行消息

现在对于事务我们有了进一步的了解，而且把以上的事务定义成为一个日常通用的模板，如下给出了一个基本的Transaction模板。

复制代码代码如下:

-- =============================================
-- Transaction Temp
-- =============================================
BEGIN TRY
BEGIN TRANSACTION
--
-- You code here.
--
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
-- Adds store procedure
-- Writes the error into ErrorLog table.
ROLLBACK TRANSACTION
END IF
END CATCH

1.1.3 总结
事务是作为单个逻辑工作单元执行的一系列操作。可以是一条SQL语句也可以是多条SQL语句。
事务具有四个特性
原子性：不可分隔、成则具成、败则具败。
一致性：事务在完成时，必须使所有的数据都保持一致状态
隔离性：独立的执行互不干扰。由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。
持久性：务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
应用程序主要通过指定事务启动和结束的时间来控制事务。
启动事务：使用 API 函数和 Transact-SQL 语句，可以按显式、自动提交或隐式的方式来启动事务。
结束事务：您可以使用 COMMIT（成功）或 ROLLBACK（失败）语句，或者通过 API 函数来结束事务。
事务模式分为：显示事务模式、隐式事务模式、自动事务模式。在SQL常用的是显示模式。
创建事务的原则：
尽可能使事务保持简短很重要，当事务启动后，数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源、以保证事务的正确安全执行。
特别是在大量并发的系统中，保持事务简短以减少并发资源锁定争夺，将先得更为重要。
1、事务处理，禁止与用户交互，在事务开始前完成用户输入。
2、在浏览数据时，尽量不要打开事务
3、尽可能使事务保持简短。
4、考虑为只读查询使用快照隔离，以减少阻塞。
5、灵活地使用更低的事务隔离级别。
6、灵活地使用更低的游标并发选项，例如开放式并发选项。
7、在事务中尽量使访问的数据量最小。

上一篇：SQL的Join使用图解教程

下一篇：SQL Join的一些总结(实例)