SQL Server SQL Transcation的一些总结分享

2022-11-12 09:49:47

内容摘要

这篇文章主要为大家详细介绍了SQL Server SQL Transcation的一些总结分享，具有一定的参考价值，可以用来参考一下。对此感兴趣的朋友，看看idc笔记做的技术笔记！1.1.1 摘要相信

文章正文

这篇文章主要为大家详细介绍了SQL Server SQL Transcation的一些总结分享，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

1.1.1 摘要相信大家对于SQL Transcation再熟悉不过，它确保了数据库的数据一致性和安全性，尤其在对数据执行增删时，如果发生异常和错误它就会触发事务回滚，从而确保了我们数据的一致性和安全性，下面我们将通过分四部分介绍事件（Transcation）。1.1.2 正文首先让我们通过一个具体的例子介绍Transcation的使用，假如我们的数据库中有一个表UserInfo，它包含三个字段分别为：UserID(自增)、UserName (nvarchar)和LuckyNumber (tinyint)，如下图所示：【图片暂缺】图1 UserInfo表UserInfo表的sql代码如下：

代码如下:

 
-- The definition of UserInfo. 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[UserInfo]( 
[UserID] [int] IDENTITY(1,1) NOT NULL, 
[UserName] [nvarchar](50) NOT NULL, 
[LuckyNumber] [tinyint] NOT NULL 
) ON [PRIMARY]

接着我们要把数据插入到表UserInfo中，这里使用一个存储过程把数据插入到该表中，存储过程SPAddDataToUserInfo的定义如下：

代码如下:

 
-- ============================================= 
-- Author: JKhuang 
-- Create date: 12/8/2011 
-- Description: Inserts data 
-- ============================================= 
CREATE PROCEDURE SPAddDataToUserInfo 

AS 
BEGIN 
-- 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('JKRush', 23); 
INSERT INTO UserInfo VALUES('Jackson', 20111111); 
END 
GO

现在我们已经定义了一个存储过程，接着让我们执行该存储过程。

【图片暂缺】

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

【图片暂缺】

图3 UserInfo表中数据我们发现只插入了两行数据，而第三行数据没有成功插入，但为了确保数据完整性，我们要把数据全部插入或全部不插入，这时我们可以考虑使用Transcation来确保数据完整性和安全性。接着让我们修改一下存储过程SPAddDataToUserInfo，在存储过程中添加Transcation。

代码如下:

 
-- ============================================= 
-- Author: JKhuang 
-- Create date: 12/8/2011 
-- Description: Inserts data 
-- ============================================= 
Alter PROCEDURE SPAddDataToUserInfo 
AS 
BEGIN 
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 
GO

现在我们再执行一次存储过程看一下是否全部不插入到表中。

【图片暂缺】

图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），接下来让我们再执行一次存储过程。【图片暂缺】图5 UserInfo表中数据看上去我们已经把问题的解决了，我们知道存储过程可以内嵌存储过程或函数，所以我们把上面的存储过程SPAddDataToUserInfo内嵌到存储过程SPMultiDataToUserInfo中，SPMultiDataToUserInfo的定义如下：

代码如下:

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

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('Cris', 1); 
EXEC SPAddDataToUserInfo 
INSERT INTO UserInfo VALUES('Ada', 32); 

COMMIT TRANSACTION 

END TRY 
BEGIN CATCH 

ROLLBACK TRANSACTION 

END CATCH 

END 
GO

我们知道存储过程SPAddDataToUserInfo会发生异常，它会回滚事务（ROLLBACK），但SPMultiDataToUserInfo是发生回滚还是继续插入数据呢？

【图片暂缺】

图6 执行存储过程消息【图片暂缺】图7 UserInfo表中数据在插入的过程发生了异常，检查UserInfo表数据并没有插入表中，这符合我们设计的意图，但我们发现异常不仅仅是产生于插入的数据超出了数值的范围，还包含事务计数异常。由于定义了两个存储过程，而且SPAddDataToUserInfo内嵌在SPMultiDataToUserInfo中，在执行这两个存储过程中，它们都发生了异常并且进行事务回滚（因为User为空）。由于插入的数据超出了数值的范围的异常是我们特意引起的，而事务计数异常这是预期之外的异常。接下来让我们看一下究竟是什么原因引起了该异常，这里我们通过输出Transactions计数来查看问题所在。

代码如下:

 
-- ============================================= 
-- Author: JKhuang 
-- Create date: 12/8/2011 
-- Description: Inserts data 
-- ============================================= 
Alter PROCEDURE SPAddDataToUserInfo 

AS 
BEGIN 
BEGIN TRY 
BEGIN TRANSACTION 
PRINT 'In [SPAddDataToUserInfo] 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('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）来查看在事务回滚时，事务计数器的变化。【图片暂缺】图8 存储过程执行消息通过上图我们发现在执行SPMultiDataToUserInfo和SPAddDataToUserInfo时，事务计数器分别加1，当遇到SPAddDataToUserInfo中的异常时，事务回滚事务计算器置零。当执行SPMultiDataToUserInfo中的事务时，由于事务计算器（@@TRANCOUNT）已经置零，导致抛出异常，现在我们明白了导致事务计数异常的原因，所以我们在进行事务回滚之前必须判断事务计算器（@@TRANCOUNT）是否为0，如果为0就不回滚事务。

代码如下:

 
-- ============================================= 
-- Author: JKhuang 
-- Create date: 12/8/2011 
-- Description: Inserts data 
-- ============================================= 
Alter PROCEDURE SPAddDataToUserInfo 
AS 
BEGIN 
BEGIN TRY 
BEGIN TRANSACTION 
PRINT 'In [SPAddDataToUserInfo] 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('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时，不进行事务回滚，这样就没有了之前事务计数器异常了。【图片暂缺】图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 Server SQL Transcation的一些总结分享的内容就先介绍到这里，更多相关文章的可以留意

代码注释

上一篇 SQL Server 锁表语句用法详细说明下一篇 SQL Server 建立登录WINDOWS帐号的解决办法

作者：喵哥笔记

学的不仅是技术，更是梦想！