SQL Server 事务

为什么需要事务?

使用事务保证操作数据的完整性。

我的理解是：事务是避免在操作数据库时，出现服务器宕机、断电、断网等一些问题时，来解决操作数据库的完整性！

---

 

示例：

银行转账问题

假定资金从账户A转到账户B，至少需要两步

1.账户A的资金减少

2.然后账户B的资金相应增加

假定张三的账户直接转账1000元到李四的账户

如果遇到，数据库操作错误就会出现下面的问题

张三的账户没有减少

但李四的账户却多了1000元

1000＋1001＝2001元

总额多出了1000元！

这样的问题如何解决呢？使用事务

---

 

什么是事务

1.事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作

2.多个操作作为一个整体向系统提交，要么都执行、要么都不执行

3.事务是一个不可分割的工作逻辑单元

转账过程就是一个事务

它需要两条UPDATE语句来完成，这两条语句是一个整体

如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元

---

 

事务的特性

事务必须具备以下四个属性，简称ACID 属性：

1.原子性（Atomicity）

事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

2.一致性（Consistency）

当事务完成时，数据必须处于一致状态

3.隔离性（Isolation）

并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

4.永久性（Durability）

事务完成后,它对数据库的修改被永久保持

 

---

 

如何创建事务

使用SQL语句管理事务

一.开始事务

BEGIN TRANSACTION

二.提交事务

COMMIT TRANSACTION

三.回滚（撤销）事务

ROLLBACK TRANSACTION

一旦事务提交或回滚，则事务结束

判断某条语句执行是否出错：

使用全局变量@@ERROR

@@ERROR只判断当前一条T-SQL语句执行是否有错

为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计

SET @errorSum=@errorSum+@@ERROR

事务可以嵌套

---

事务分类

显式事务

用BEGIN TRANSACTION明确指定事务的开始

最常用的事务类型

隐性事务 implicit_transctions on

1.通过设置SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开；（SET IMPLICIT_TRANSACTIONS OFF是关闭）

2.其后的T-SQL语句自动启动一个新事务

3.提交或回滚一个事务后，下一个 T-SQL 语句又将启动一个新事务

自动提交事务

SQL Server 的默认模式

每条单独的 T-SQL 语句视为一个事务

---

 

示例

从张三的账户转出1000元，存入李四的账户中

BEGIN TRANSACTION 
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT 
SET @errorSum=0                                             --初始化为0，即无错误
/*--转账：张三的账户减少1000元，李四的账户增加1000元*/
UPDATE bank SET currentMoney = currentMoney - 1000
   WHERE customerName = '张三'
SET @errorSum = @errorSum + @@ERROR --累计是否有错误
UPDATE bank SET currentMoney = currentMoney + 1000
   WHERE customerName = '李四'
SET @errorSum = @errorSum + @@ERROR

BEGIN TRANSACTION

开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）

SET @errorSum = @errorSum + @@ERROR

累计是否有错误

IF @errorSum<>0  --如果SQL语句执行出错
  BEGIN
    PRINT '交易失败，回滚事务'
    ROLLBACK TRANSACTION 
  END  
ELSE
  BEGIN
    PRINT '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION   
  END
GO
PRINT '查看转账事务后的余额'
SELECT * FROM bank  
GO 

根据执行是否有错误，决定提交事务，或撤销事务

如果有错，则回滚操作，事务结束

如果成功，则提交操作，事务结束

 

---

 

使用显式事务完成批量插入10个学生考试成绩的操作

使用全局变量@@ERROR判断插入操作是否成功

使用IF语句判断@@ERROR值。如果插入成功，提交事务；否则回滚事务

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--插入数据--*/
… …
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(10012,1,'2009-5-20',102)      --分数违反约束
SET @errorSum=@errorSum+@@ERROR
… …
IF(@errorSum<>0) --如果有错误
  BEGIN
    PRINT '插入失败，回滚事务'
    ROLLBACK TRANSACTION 
  END  
ELSE
  BEGIN
    PRINT '插入成功，提交事务'
    COMMIT TRANSACTION   
  END

 

需求说明： 将毕业学生的基本信息和考试成绩分别保存到历史表中

提示：

1.使用显式事务

2.查询Result表中所有Y2学生的考试成绩，保存到表HistoreResult中

3.删除Result表中所有Y2学生的考试成绩

4.查询Student表中所有Y2的学生记录，保存到表HistoryStudent中

5.删除Studet表中所有Y2学生记录

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--查询Result表中所有Y2学生的考试成绩，保存到新表HistoreResult*/
SELECT Result.* INTO HistoreResult FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo
INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
WHERE GradeName='Y2'
SET @errorSum=@errorSum@@ERROR
/*--删除Result表中所有Y2学生的考试成绩*/
DELETE  Result FROM  Result JOIN Student ON Result.StudentNo=Student.StudentNo
INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@ERROR
/*--将Student表中所有Y2的学生记录，保存到新表HistoryStudent*/
… …
/*--删除Studet表中所有Y2学生记录*/
… …
/*--根据是否有错误，确定事务是提交还是撤销--*/

 

 

---

 

笔记

--银行转账业务
--角色：张三、李四   功能：张三扣100  李四加100

--张三扣100
declare @money money=100
update bank 
set usermoney=usermoney-@money
where name='张三' --数据库不支持+= -=
update bank 
set usermoney=usermoney+@money
where name='李四' --数据库不支持+= -=
--不合理  两条语句不是一个整体
--数据库三种事务
--insert添加  delete 删除  update 修改     select查询(不会影响数据)
--第一种：显示事务
--系统函数:捕获T-SQL语句执行的错误编号@@error
begin transaction  --开始事务
declare @money money=100
declare @sumError int=0--默认无错
update bank 
set usermoney=usermoney-@money
where name='张三'  
set @sumError=@sumError+@@error
update bank 
set usermoney=usermoney+@money
where name='李四'  
set @sumError=@sumError+@@error

if @sumError=0
begin
    print '转账成功,提交';--分号代表一条语句的结束
    commit transaction --提交事务
end
else
begin
    print '转账失败,回滚'+cast(@sumError as varchar) 
    rollback transaction --回滚事务
end
 
--第二种：自动事务commit提交
--第三种：隐式事务
SET IMPLICIT_TRANSACTIONS off --打开隐式事务 cmd
--如果打开隐式事务，使用操作数据语句 事务锁表
--正在等待下一步指令
--提交 commit（提交:执行操作）,手动提交一种，关闭当前回话也会提交
--取消 rollback(回滚:取消操作)
commit
rollback 
--oracle 数据库 默认隐式事务开启，sqlserver默认隐式事务关闭
--sqlconnection 连接对象 conn.close()

--sqlserver2014工具 服务
--如果和金融相关数据结构 数据还原

 

转载于:https://www.cnblogs.com/cplvfx/articles/10478855.html