讲师中心 微信公众号

工具推荐

视频效率加速
Laper
Laper

剧本策划到成片协作

AniShort
AniShort

AI短剧协同制作平台

updream

一键生成高质量推广成片

立刻MV
立刻MV

低成本歌曲 MV、视频生成

SQL Transcation的一些总结分享

秋晨酱_9230

秋晨酱_9230

发布时间:2016-06-07 18:07:55

|

1336人浏览过

|

来源于php中文网

原创

相信大家对于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

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

DBeaver Community 26.2.1
DBeaver Community 26.2.1

DBeaver Community 26.2.1 Windows x64 官方安装包,适用于数据库连接、SQL 开发、数据浏览和管理。

下载
图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、在事务中尽量使访问的数据量最小。

热门AI工具

更多
立刻MV
立刻MV Hot

立刻MV是一款AI文本写作工具,AI 音乐视频(MV)创作工具。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

Flowith
Flowith Hot

Flowith是一款基于画布和智能体工作流的 AI 协作工作空间。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

讯飞绘文

讯飞绘文是一款由科大讯飞推出的一站式 AIGC 内容运营平台。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

小橡皮AI

一款AI工具,主要用于小橡皮一键去AI味,将机器味重的内容改得更有活人感,防止因为 AI 内容被平台打上 AI 标签,以及限流,适合需要提升相关任务效率的用户。

UpDream
UpDream Hot

一款AI视频创作工具,主要用于哔哩哔哩推出的自研AI视频创作工具,适合需要提升相关任务效率的用户。

Lovart
Lovart Hot

一款面向视觉设计创作的AI设计平台,可通过智能体和画布工作流辅助制作海报、Logo、网页、PPT及其他视觉内容。

相关专题

更多
Nginx CVE-2026-123007排查与防护指南
Nginx CVE-2026-123007排查与防护指南

Nginx HTTP/2漏洞该怎么修复?本专题围绕CVE-2026-123007,介绍安全版本升级、模块兼容检查和配置恢复方法,以及日志排查、权限设置等后续加固操作,方便你按实际部署情况查找对应教程。

0

2026.11.20

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

40

2026.11.17

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

60

2026.11.17

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

80

2026.11.17

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

20

2026.11.17

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

80

2026.11.17

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

40

2026.11.17

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

180

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

140

2026.11.12

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Navicat Monitor 3 用户手册
Navicat Monitor 3 用户手册

共0课时 | 0人学习

SQLAlchemy 2.1 官方统一教程
SQLAlchemy 2.1 官方统一教程

共0课时 | 0人学习

DBeaver生态工具dbvr官方手册
DBeaver生态工具dbvr官方手册

共0课时 | 0人学习

关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn