匿名通过本文主要向大家介绍了SQL,Transcation等相关知识,希望本文的分享对您有所帮助
相信大家对于SQL Transcation再熟悉不过，它确保了数据库的数据一致性和安全性，尤其在对数据执行增删时，如果发生异常和错误它就会触发事务回滚，从而确保了我们数据的一致性和安全性，下面我们将通过分四部分介绍事件（Transcation）
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
上面我们通过输出事务的计数（@@T