SQL SERVER 事务相关

1 准备数据 及 涉及到的几个设置

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  --设置事务会话的隔离等级（默认值为 READ UNCOMMITTED ），只对当前进程有效（就是说只对打开SSMS当前查询窗口有效，再打开另一个查询窗口就无效了）

SET XACT_ABORT OFF  --设置精确终止（暂且这么叫）开关（默认值为 OFF）   ，只对当前进程有效（就是说只对打开SSMS当前窗口有效，再打开另一个就无效了）

查看 XACT_ABORT 是否启用

SELECT (CASE WHEN (16384 & @@OPTIONS) = 16384 THEN 'ON' ELSE 'OFF' END) AS XACT_ABORT;  

 

 

 

IF NOT OBJECT_ID('Score') IS NULL  
DROP TABLE [Score]  
GO  
IF NOT OBJECT_ID('Student') IS NULL  
DROP TABLE [Student]  
GO  
  
CREATE TABLE Student  
(stuid int NOT NULL PRIMARY KEY,  
stuName Nvarchar(20)  
)  
CREATE TABLE Score  
(stuid int NOT NULL REFERENCES Student(stuid),--外键  
scoreValue int  
)  
GO  
  
INSERT INTO Student VALUES (101,'胡一刀')  
INSERT INTO Student VALUES (102,'袁承志')  
INSERT INTO Student VALUES (103,'陈家洛')  
INSERT INTO student VALUES (104,'张三丰')  
GO  

2 回滚事例

--最简单回滚
--下面语句可以分开执行，一次执行一行

BEGIN TRAN --开启事务，设置事务开始点
/*
命令已成功完成。
*/

SELECT * FROM score
/*
(0 行受影响)
说明：因为表里没有数据，所以查询返回条数为0.
*/

INSERT INTO score VALUES (101, 59)  --插入数据，此时如果查看消息：(1 行受影响)
/*
(1 行受影响)
*/

SELECT * FROM score
/*
stuid    scoreValue
101      59

(1 行受影响)
说明：本进程内（本查询窗口）可以查到此条已经插入的数据，但是其它进程（查询窗口）是无法查到数据的。因为现在事务并没有提交，SQLSERVER 的默认隔离级别为 READ COMMITTED ,此隔离级别在其它进行中是无法读取到未提交数据的。

*/


ROLLBACK
/*
命令已成功完成。
说明：回滚未提交事务。
*/


SELECT * FROM score
/*
(0 行受影响)
因为已经回滚了，所以查不出数据了。
*/

--常见回滚 
--情景1
BEGIN TRAN --开启事务，设置事务开始点

INSERT INTO score  VALUES(101,999) --此记录可以插入
INSERT INTO score  VALUES(109,999) --无法插入该数据，因为外键约束，所以查询分析器会报错出来
INSERT INTO score  VALUES(102,888) --此记录可以插入
COMMIT TRAN;



SELECT * FROM score

/*
stuid    scoreValue
101        999
102        888
说明：这里会查询到两行数据，也就是说出错的那条记录没有进数据库，但是同时SQL任务并没有停止运行，而是把剩余的所有语句都运行了，直至任务完结。
其实这并不是我们所期望的，我们期望的是，如果此三条数据有问题，则全部不插入数据库。
*/


--情景2
BEGIN TRY
    BEGIN TRAN; --开启事务，设置事务开始点
    INSERT INTO score  VALUES(101, 999); --此记录可以插入
    INSERT INTO score  VALUES(109, 999); --无法插入该数据，因为外键约束，所以查询分析器会报错出来
    INSERT INTO score  VALUES(102, 888); --此记录可以插入
    COMMIT TRAN;
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK;
    END;
END CATCH;


SELECT * FROM score
/*
(0 行受影响)
说明：try 获取到了事务中的错误，所以进行了回滚。这时就是对整个事务的回滚了，所以没有数据插入到数据库。
这是我们所希望看到的，如果3条语句中有一句有错误，则全部不提交
*/


--情景3

SET XACT_ABORT ON;

BEGIN TRAN; --开启事务，设置事务开始点
    INSERT INTO score  VALUES(101, 999); --此记录可以插入
    INSERT INTO score  VALUES(109, 999); --无法插入该数据，因为外键约束，所以查询分析器会报错出来
    INSERT INTO score  VALUES(102, 888); --此记录可以插入
COMMIT TRAN;


SELECT * FROM score

/*
(0 行受影响)
说明：XACT_ABORT 设置为ON 时，这时就是对整个事务的回滚了，所以没有数据插入到数据库。
这是我们所希望看到的，如果3条语句中有一句有错误，则全部不提交。
SET XACT_ABORT ON; 只对当前进程有效（当前查询窗口）。
*/

二、捕获错误的常用函数 

1、ERROR_NUMBER()  返回错误号。

2、ERROR_SEVERITY()  返回严重级别。

3、ERROR_STATE()  返回错误状态号。

4、ERROR_PROCEDURE()  返回出现错误的存储过程或触发器的名称。

5、ERROR_LINE()  返回导致错误的行号。

6、ERROR_MESSAGE()  返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。

 综上所以如果想让整个语句块，多条语句要么全部提交，要么全部不提交的方法为 情景2 和 情景3 中所描述。