SQLTranscation的一些总结分享

2022-07-21 0 894
相信大家对于SQLTranscation再熟悉不过,它确保了数据库的数据一致性和安全性,尤其在对数据执行增删时,如果发生异常和错误它就会触发事务回滚,从而确保了我们数据的一致性和安全性,下面我们将通过分四部分介绍事件(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 上面我们通过输出事务的计数(@@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、灵活地使用更低的游标并发选项,例如开放式并发选项。

收藏 (0) 打赏

感谢您的支持,我们会继续努力的!

打开支付宝/微信/QQ扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
点赞 (0)

1. 本站所有教学内容和资源均来源于本站原创和网络收集以及用户自主上传,如有侵权请带证联系本站客服进行处理!邮箱:team@lcwl.fun
2. 对于非本站原创的资源不保证所提供下载资源的准确性、安全性和完整性,资源仅供下载学习之用!如有链接无法下载、失效或广告,请联系本站客服处理,有奖励!
3. 除本站原创教学或资源外其余资源您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容资源!如用于商业或者非法用途,与本站无关,一切后果请用户自负!
4. 本站原创资源如需商用还请申请商业授权,否则本站有权追究其法律责任!
5. 如果您也有好的资源或教程,您可以投稿发布,成功分享后有学币奖励额外收入!

凌晨资源网 后端开发 SQLTranscation的一些总结分享 https://www.lingchenw.cn/2451.html

常见问题
  • 本站所有付费教程均可单独付费或者开会员学习,自己购买或者开了会员就可以学习,来凌晨学院报名学习送会员,并且有讲师一对一辅导培训教学,师生互动化,定期布置作业等
查看详情
  • 本站所有资源版权均属于原作者所有,资源中标注可商用的可以商用的代表是本团队自主研发公布可商用的产品,未标注可商用所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。
查看详情

相关文章

发表评论
暂无评论
官方客服团队

为您解决烦忧 - 24小时在线 专业服务

学习手机编程软件开发
手机编程软件开发

你知道吗?用手机也能开发制作软件,满足自己的编程心,做属于自己的软件

报名学习
自媒体短视频
副业做自媒体短视频

想做自媒体短视频剪辑给自己多一份副业,只需一部手机你也可以

报名学习
搭建网站必备云互联主机
链未云互联

搭建网站,后台云服务等必备云服务器互联主机,IDC云主机

现在使用
Fa快捷助手
用手点一点就能开发做软件

不用记编程语法,不用一行一行手敲代码,用手点一点就能开发制作软件

点击下载