T-SQL复习15--事务

• 概念
• SQL Server事务
• 创建提交事务
• 标记一个事务
• 回滚事务
• 在事务内设置保存点
• 在存储过程中使用事务

概念

事务是单个工作单元，如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分，如果该事务遇到错误且必须取消或回滚，则所有数据修改均被清楚

SQL Server事务

• 自动提交事务：每条单独的语句都是一个事务
• 显式事务：每个事务均已BEGIN TRANSACTION语句显示开始，已COMMIT或ROLLBACK语句显式结束
• 隐式事务：在前一个事务完成时新事务隐式启动，但每个事务仍已COMMIT或ROLLBACK语句显式结束

创建提交事务

示例：

BEGIN TRANSACTION UP_STU --表示开始一个事务，BEGIN TRANSACTION使@@TRANCOUNT值按1递增
--SELECT @@TRANCOUNT  --查询@@TRANCOUNT

UPDATE dbo.STUDENT SET Age=10 WHERE NAME='Hello'

COMMIT TRANSACTION UP_STU  --表示提交一个事务，仅当对数据库的操作全部正确时，才可以提交事务。COMMIT TRANSACTION时@@TRANCOUNT值按1递减
--SELECT @@TRANCOUNT

标记一个事务

示例：

BEGIN TRANSACTION UP_STU
WITH MARK '修改学生年龄' --使用WITH MARK来标记事务
UPDATE dbo.STUDENT SET Age=10 WHERE NAME='Hello'
COMMIT TRANSACTION UP_STU

注：标记事务时事务名于事务日志中，在还原数据库时可将数据库还原到标记的事务

回滚事务

示例：

BEGIN TRANSACTION UP_STU
UPDATE dbo.STUDENT SET Age=10 WHERE NAME='Hello'
ROLLBACK TRANSACTION UP_STU --回滚事务到事务的起点，清除自事务起点所作的所有数据的修改，ROLLBACK TRANSACTION使@@TRANCOUNT值递减到0

在事务内设置保存点

示例：

BEGIN TRANSACTION UP_STU
UPDATE dbo.STUDENT SET Age=10 WHERE NAME='Hello'
SAVE TRANSACTION TRAN_SAVE
UPDATE dbo.STUDENT SET Age=12 WHERE NAME='World'
ROLLBACK TRANSACTION TRAN_SAVE --回滚到事务的保存点位置，ROLLBACK TRANSACTION savePointName 不影响@@TRANCOUNT
UPDATE dbo.STUDENT SET Age=13 WHERE NAME='World'
COMMIT TRANSACTION UP_STU

在存储过程中使用事务

示例：

USE SCHOOL
GO
IF OBJECT_ID('P_STUDENT','P') IS NOT NULL
DROP PROCEDURE P_STUDENT
GO
CREATE PROCEDURE P_STUDENT
AS
BEGIN
    BEGIN TRY
		BEGIN TRANSACTION TRAN1 --外层事务
			UPDATE STUDENT SET Age=10 WHERE NAME='Hello'
			BEGIN TRANSACTION SUBTRAN1  --嵌套事务
				INSERT INTO STUDENT(NAME,Age,SEX) VALUES('JAME',12,'男')
			COMMIT TRANSACTION SUBTRAN1
		COMMIT TRANSACTION TRAN1
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 --@@TRANCOUNT>0表示在TRY块中的事务没有成功提交，那么就回滚整个外层事务
		BEGIN
			PRINT @@TRANCOUNT
			SELECT ERROR_LINE(),ERROR_MESSAGE(),ERROR_PROCEDURE()
			ROLLBACK TRANSACTION TRAN1
		END
		ELSE  --ELSE就是@@TRANCOUNT的值为0，说明事务都已经全部提交了
		SELECT * FROM dbo.STUDENT
	END CATCH
END
GO