为什么需要事务?
使用事务保证操作数据的完整性。
我的理解是:事务是避免在操作数据库时,出现服务器宕机、断电、断网等一些问题时,来解决操作数据库的完整性!
示例:
银行转账问题
假定资金从账户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工具 服务 --如果和金融相关数据结构 数据还原