时间：2021-07-01 10:21:17 帮助过：11人阅读
1 begin tran -- 开启事务，transcation 的简写 2 declare @errorNo int --定义变量，用于记录事务执行过程中的错误次数 3 set @errorNo=0 4 begin try 5 update Student set C_S_Id=‘2‘ where S_StuNo=‘003‘ 6 set @errorNo=@errorNo+@@ERROR 7 select ‘S_StuNo=003 已经修改啦‘ 8 9 update Student set C_S_Id=‘3‘ where S_StuNo=‘002‘ 10 set @errorNo=@errorNo+@@ERROR -- @@ERROR 系统全局变量，记录错误次数，出现一次错误 @@ERROR 值+1 11 select ‘S_StuNo=002 已经修改啦‘ 12 13 if(@errorNo>0) 14 begin 15 --抛出自定义的异常,在最后的catch块中统一处理异常 16 RAISERROR(233333,16,3) 17 end 18 19 end try 20 begin catch 21 select ERROR_NUMBER() errorNumber, --错误代码 22 ERROR_SEVERITY() errorSeverity, --错误严重级别，级别小于10 try catch 捕获不到 23 ERROR_STATE() errorState, --错误状态码 24 ERROR_PROCEDURE() errorProcedure, --出现错误的存储过程或触发器的名称 25 ERROR_LINE() errorLine, --发生错误的行号 26 ERROR_MESSAGE() errorMessage --错误的具体信息 27 28 if(@@trancount>0) -- @@trancount 系统全局变量，事务开启 @@trancount 值+1，判断事务是否开启 29 begin 30 rollback tran; -- 回滚事务 31 end 32 end catch 33 34 if(@@trancount>0) 35 begin 36 commit tran; -- 提交事务 37 end 38 39 select * from Student
这里由于外键约束的原因，所以第二条 update 语句导致失败，以上结果可以看出第一条数据肯定是执行过了，但是在 catch 语句里面回滚了，所以数据还是原来的状态。
现在只需要把第二条 update 语句 C_S_Id 列的值改为 5 即可。
1 begin tran -- 开启事务，transcation 的简写 2 declare @errorNo int --定义变量，用于记录事务执行过程中的错误次数 3 set @errorNo=0 4 begin try 5 update Student set C_S_Id=‘2‘ where S_StuNo=‘003‘ 6 set @errorNo=@errorNo+@@ERROR 7 select ‘S_StuNo=003 已经修改啦‘ 8 9 update Student set C_S_Id=‘5‘ where S_StuNo=‘002‘ 10 set @errorNo=@errorNo+@@ERROR -- @@ERROR 系统全局变量，记录错误次数，出现一次错误 @@ERROR 值+1 11 select ‘S_StuNo=002 已经修改啦‘ 12 13 if(@errorNo>0) 14 begin 15 --抛出自定义的异常,在最后的catch块中统一处理异常 16 RAISERROR(233333,16,3) 17 end 18 19 end try 20 begin catch 21 select ERROR_NUMBER() errorNumber, --错误代码 22 ERROR_SEVERITY() errorSeverity, --错误严重级别，级别小于10 try catch 捕获不到 23 ERROR_STATE() errorState, --错误状态码 24 ERROR_PROCEDURE() errorProcedure, --出现错误的存储过程或触发器的名称 25 ERROR_LINE() errorLine, --发生错误的行号 26 ERROR_MESSAGE() errorMessage --错误的具体信息 27 28 if(@@trancount>0) -- @@trancount 系统全局变量，事务开启 @@trancount 值+1，判断事务是否开启 29 begin 30 rollback tran; -- 回滚事务 31 end 32 end catch 33 34 if(@@trancount>0) 35 begin 36 commit tran; -- 提交事务 37 end 38 39 select * from Student
关于 RAISERROR 自定义抛出异常可以看这里：http://www.cnblogs.com/Brambling/p/6687068.html
设置 xact_abort：
设置 xact_abort on/off , 指定是否回滚当前事务，为 on 时如果当前 sql 出错，回滚整个事务，为 off 时如果 sql 出错回滚当前 sql 语句，其它语句照常运行读写数据库。
xact_abort 只对运行时出现的错误有用。
1 set xact_abort off 2 3 begin tran -- 开启事务，transcation 的简写 4 declare @errorNo int --定义变量，用于记录事务执行过程中的错误次数 5 set @errorNo=0 6 begin try 7 update Student set C_S_Id=‘2‘ where S_StuNo=‘003‘ 8 set @errorNo=@errorNo+@@ERROR 9 select ‘S_StuNo=003 已经修改啦‘ 10 11 update Student set C_S_Id=‘3‘ where S_StuNo=‘002‘ 12 set @errorNo=@errorNo+@@ERROR -- @@ERROR 系统全局变量，记录错误次数，出现一次错误 @@ERROR 值+1 13 select ‘S_StuNo=002 已经修改啦‘ 14 15 if(@errorNo>0) 16 begin 17 --抛出自定义的异常,在最后的catch块中统一处理异常 18 RAISERROR(233333,16,3) 19 end 20 21 end try 22 begin catch 23 select ERROR_NUMBER() errorNumber, --错误代码 24 ERROR_SEVERITY() errorSeverity, --错误严重级别，级别小于10 try catch 捕获不到 25 ERROR_STATE() errorState, --错误状态码 26 ERROR_PROCEDURE() errorProcedure, --出现错误的存储过程或触发器的名称 27 ERROR_LINE() errorLine, --发生错误的行号 28 ERROR_MESSAGE() errorMessage --错误的具体信息 29 30 if(@@trancount>0) -- @@trancount 系统全局变量，事务开启 @@trancount 值+1，判断事务是否开启 31 begin 32 rollback tran; -- 回滚事务 33 end 34 end catch 35 36 select * from Student
xact_abort 设置为 off 时，虽然也出现了异常，但是可以看出第一天数据还是修改了，并没有回滚。因为它只是回滚出错的 sql 语句，并不全部回滚。
1 set xact_abort on 2 3 begin tran -- 开启事务，transcation 的简写 4 declare @errorNo int --定义变量，用于记录事务执行过程中的错误次数 5 set @errorNo=0 6 begin try 7 update Student set C_S_Id=‘2‘ where S_StuNo=‘003‘ 8 set @errorNo=@errorNo+@@ERROR 9 select ‘S_StuNo=003 已经修改啦‘ 10 11 update Student set C_S_Id=‘3‘ where S_StuNo=‘002‘ 12 set @errorNo=@errorNo+@@ERROR -- @@ERROR 系统全局变量，记录错误次数，出现一次错误 @@ERROR 值+1 13 select ‘S_StuNo=002 已经修改啦‘ 14 15 if(@errorNo>0) 16 begin 17 --抛出自定义的异常,在最后的catch块中统一处理异常 18 RAISERROR(233333,16,3) 19 end 20 21 end try 22 begin catch 23 select ERROR_NUMBER() errorNumber, --错误代码 24 ERROR_SEVERITY() errorSeverity, --错误严重级别，级别小于10 try catch 捕获不到 25 ERROR_STATE() errorState, --错误状态码 26 ERROR_PROCEDURE() errorProcedure, --出现错误的存储过程或触发器的名称 27 ERROR_LINE() errorLine, --发生错误的行号 28 ERROR_MESSAGE() errorMessage --错误的具体信息 29 30 if(@@trancount>0) -- @@trancount 系统全局变量，事务开启 @@trancount 值+1，判断事务是否开启 31 begin 32 rollback tran; -- 回滚事务 33 end 34 end catch 35 36 select * from Student
xact_abort 设置为 on 时，出现了异常，回滚整个事务。
事务死锁：
打开两个查询窗口，把下面的语句，分别放入2个查询窗口，在5秒内运行2个事务模块。
1 begin tran 2 update Student set C_S_Id=‘2‘ where S_StuNo=‘002‘ 3 4 waitfor delay ‘0:0:5‘ 5 6 update Student set C_S_Id=‘5‘ where S_StuNo=‘003‘ 7 commit tran 8 9 select * from Student
1 begin tran 2 update Student set C_S_Id=‘5‘ where S_StuNo=‘003‘ 3 4 waitfor delay ‘0:0:5‘ 5 6 update Student set C_S_Id=‘2‘ where S_StuNo=‘002‘ 7 commit tran 8 9 select * from Student
因为事务在执行过程中会将事务中用到的表和数据进行锁定，直到事务结束（提交或回滚），才会释放。
在很多用户都同时使用事务访问同一个数据资源的情况下，就会造成以下几种数据错误：
1、更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
2、不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。
3、脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据。
4、幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集进行增删改操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。
然而锁定，就是为解决这些问题的，它的存在使得一个事务对它自己的数据块进行操作的时候，而另外一个事务则不能插足这些数据块。这就是所谓的锁定。
锁兼容性具体参见：http://msdn.microsoft.com/zh-cn/library/ms186396.aspx
锁粒度和层次结构参见：http://msdn.microsoft.com/zh-cn/library/ms189849(v=sql.105).aspx
什么是死锁，为什么会产生死锁。见上面的例子。
例子是这样的：
第一个事务（称为A）：先更新表 Student S_StuNo=‘003‘ 这条数据 --->>停顿5秒---->>更新表 Student S_StuNo=‘002‘ 这条数据
第二个事务（称为B）：先更新表 Student S_StuNo=‘002‘ 这条数据--->>停顿5秒---->>更新表 Student S_StuNo=‘003‘ 这条数据
先执行事务A----5秒之内---执行事务B，出现死锁现象。
过程是这样子的：
这样相互等待对方释放资源，造成资源读写拥挤堵塞的情况，就被称为死锁现象，也叫做阻塞。而为什么会产生，上例就列举出来啦。
然而数据库并没有出现无限等待的情况，是因为数据库搜索引擎会定期检测这种状况，一旦发现有情况，立马选择一个事务作为牺牲品。牺牲的事务，将会回滚数据。
但是我们可以指定具体哪个事务作为牺牲品：
语法：
1 set deadlock_priority <级别>
死锁处理的优先级别为 low < normal < high，不指定的情况下默认为normal，牺牲品为随机。如果指定，牺牲品为级别低的。
还可以使用数字来处理标识级别：-10 到 -5 为 low，-5 为 normal，-5 到 10 为 high。
死锁耗时耗资源，然而在大型数据库中，高并发带来的死锁是不可避免的，尽管死锁不能完全避免，但遵守特定的编码惯例可以将发生死锁的机会降至最低。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务：
回滚，撤消事务执行的所有工作。
由于死锁时回滚而由应用程序重新提交。
下列方法有助于将死锁减至最少：
1、按同一顺序访问数据库对象资源。
2、避免事务中的用户交互，即事务中等待用户输入、提交等操作。
3、保持事务简短并处于一个批处理中，在同一数据库中并发执行多个需要长时间运行的事务时通常会发生死锁。事务的运行时间越长，它持有排他锁或更新锁的时间也就越长，从而会阻塞其他活动并可能导致死锁。
4、使用较低的隔离级别，确定事务是否能在较低的隔离级别上运行。实现已提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短。这样就减少了锁争用。
5、尽可能使用分区表，分区视图，把数据放置在不同的磁盘和文件组中，分散访问保存在不同分区的数据，减少因为表中放置锁而造成的其它事务长时间等待。
可参考：http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx
查看锁和事务活动情况：
1 --查看锁活动情况 2 select * from sys.dm_tran_locks 3 --查看事务活动情况 4 dbcc opentran
可参考：http://msdn.microsoft.com/zh-cn/library/ms190345.aspx
事务隔离级别：
事物隔离级别，分为5种。就是并发事务对同一资源的读取深度层次。
1、read uncommitted：这个隔离级别最低，可以读取到一个事务正在处理的数据，但事务还未提交，这种级别的读取叫做脏读。
2、read committed：这个级别是默认选项，不能脏读，不能读取事务正在处理没有提交的数据，但能修改。
3、repeatable read：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。
4、snapshot：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。
5、serializable：最高事务隔离级别，只能看到事务处理之前的数据。
语法：
1 -- 设置事务隔离级别 2 set tran isolation level <级别>
read uncommitted 隔离级别的例子：
1 begin tran 2 set deadlock_priority low -- 设置死锁处理的优先级别为 low 3 4 update Student set C_S_Id=‘2‘ where S_StuNo=‘002‘ 5 6 waitfor delay ‘0:0:5‘ -- 等待5秒执行下面的回滚事务 7 rollback tran 8 9 select * from Student
5秒内在另外一个查询窗口执行下面语句：
1 set tran isolation level read uncommitted -- 设置事务隔离级别为 read uncommitted 2 3 select * from Student -- 读取的数据为正在修改的数据，脏读 4 5 waitfor delay ‘0:0:5‘ -- 5秒之后数据已经回滚 6 7 select * from Student -- 回滚之后的数据
read committed 隔离级别的例子：
1 begin tran 2 set deadlock_priority low -- 设置死锁处理的优先级别为 low 3 4 update Student set C_S_Id=‘2‘ where S_StuNo=‘002‘ -- 修改为 2 5 6 waitfor delay ‘0:0:5‘ -- 等待5秒执行下面的回滚事务 7 rollback tran 8 9 select * from Student
1 set tran isolation level read committed -- 设置事务隔离级别为 read committed 2 3 select * from Student -- 读取不到正在修改的数据，不能脏读 4 5 update Student set C_S_Id=‘5‘ where S_StuNo=‘002‘ -- 修改为 5 6 7 waitfor delay ‘0:0:5‘ -- 5秒之后上一个事务已经回滚 8 9 select * from Student -- 修改之后的数据
设置锁超时时间：
发生死锁的时候，数据库引擎会自动检测死锁，解决问题，然而这样子是很被动，只能在发生死锁后，等待处理。
然而我们也可以主动出击，设置锁超时时间，一旦资源被锁定阻塞，超过设置的锁定时间，阻塞语句自动取消，释放资源，报1222错误。
任何事情都具有两面性，调优的同时，也有他的不足之处，那就是一旦超过时间，语句取消，释放资源，但是当前报错事务，不会回滚，会造成数据错误，你需要在程序中捕获1222错误，用程序处理当前事务的逻辑，使数据正确。
1 --查看锁超时时间,默认为-1 2 select @@lock_timeout 3 4 --设置锁超时时间 5 set lock_timeout 0 --为0时，即为一旦发现资源锁定，立即报错，不再等待，当前事务不回滚，设置时间需谨慎处理
之前从没接触过事务，今天跟着学习了一下，哇！！！事务还挺好理解的，不过事务并发死锁这类问题就需要实际经验了。所以我理解也不是太深刻，不过大家可以看下面这篇文章，我就是跟着学习的，个人觉得写的很不错。
学习地址：
http://www.cnblogs.com/knowledgesea/p/3714417.html
SQL Server创建事务——锁
标签：回滚 情况 持久性 pen 约束 包含 需要 存取款 ram