MS-SQL中的事务

一 事务的属性事务具有ACID属性即 Atomic原子性, Consistent一致性, Isolated隔离性, Durable永久性原子性就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留一致性事务完成或者撤销后，都应该处于一致的状态隔离性多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据永久性事务提交以后，所做的工作就被永久的保存下来二 事务并发处理会产生的问题丢失更新当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。脏读当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。不可重复读当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。幻像读当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。三 事务处理类型自动处理事务系统默认每个T－SQL命令都是事务处理 由系统自动开始并提交隐式事务当有大量的DDL 和DML命令执行时会自动开始，并一直保持到用户明确提交为止，切换隐式事务可以用SET IMPLICIT_TRANSACTIONS为连接设置隐性事务模式.当设置为 ON 时，SET IMPLICIT_TRANSACTIONS 将连接设置为隐性事务模式。当设置为 OFF 时，则使连接返回到自动提交事务模式用户定义事务由用户来控制事务的开始和结束 命令有: begin tran commit tran rollback tran 命令分布式事务跨越多个服务器的事务称为分布式事务,sql server 可以由DTc microsoft distributed transaction coordinator来支持处理分布式事务,可以使用 BEgin distributed transaction 命令启动一个分布式事务处理四 事务处理的隔离级别使用SET TRANSACTION ISOLATION LEVEL来控制由连接发出的所有语句的默认事务锁定行为从低到高依次是READ UNCOMMITTED执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。举例设table1(A,B,C)A B Ca1 b1 c1a2 b2 c2a3 b3 c3新建两个连接在第一个连接中执行以下语句select * from table1begin tranupdate table1 set c='c'select * from table1waitfor delay '00:00:10' --等待10秒rollback transelect * from table1在第二个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDprint '脏读'select * from table1if @@rowcount>0beginwaitfor delay '00:00:10'print '不重复读'select * from table1end第二个连接的结果脏读A B Ca1 b1 ca2 b2 ca3 b3 c'不重复读'A B Ca1 b1 c1a2 b2 c2a3 b3 c3READ COMMITTED指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。在第一个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL READ COMMITTEDbegin tranprint '初始'select * from table1waitfor delay '00:00:10' --等待10秒print '不重复读'select * from table1rollback tran在第二个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL READ COMMITTEDupdate table1 set c='c'第一个连接的结果初始A B Ca1 b1 c1a2 b2 c2a3 b3 c3不重复读A B Ca1 b1 ca2 b2 ca3 b3 cREPEATABLE READ锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。在第一个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL REPEATABLE READbegin tranprint '初始'select * from table1waitfor delay '00:00:10' --等待10秒print '幻像读'select * from table1rollback tran在第二个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL REPEATABLE READinsert table1 select 'a4','b4','c4'第一个连接的结果初始A B Ca1 b1 c1a2 b2 c2a3 b3 c3幻像读A B Ca1 b1 c1a2 b2 c2a3 b3 c3a4 b4 c4SERIALIZABLE在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。在第一个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL SERIALIZABLEbegin tranprint '初始'select * from table1waitfor delay '00:00:10' --等待10秒print '没有变化'select * from table1rollback tran在第二个连接中执行以下语句SET TRANSACTION ISOLATION LEVEL SERIALIZABLEinsert table1 select 'a4','b4','c4'第一个连接的结果初始A B Ca1 b1 c1a2 b2 c2a3 b3 c3没有变化A B Ca1 b1 c1a2 b2 c2a3 b3 c3五 事务处理嵌套的语法和对@@TRANCOUNT的影响BEGIN TRAN @@TRANCOUNT＋1COMMIT TRAN @@TRANCOUNT－1ROLLBACK TRAN

三种使用事务的方式：

1)放在一个try~catch里面alter PROCEDURE [dbo].[dd]ASBEGIN    begin try         begin tran              insert into student values('new111111111111111111111111111','1',2,'23',3,getdate())              insert into person values('new21312',5,'45')            commit tran    end  try    begin catch        print 'a'        rollback tran    end catchend2）set xact_abord  on有错误发生时，整个自动回滚ALTER PROCEDURE [dbo].[dd2]ASBEGIN    begin tran                SET XACT_ABORT  on        insert into person values('n',5,'45')        insert into student values('new111111111111111111111111111123','1',2,'23',3,getdate())    commit tranend3）自定义变量统计@@errordeclare @err intset @err=0begin tran --操作1set @err=@err+abs(@@error) --操作2set @err=@err+abs(@@error) --操作3set @err=@err+abs(@@error)if @err=0    commitelse   rollback