SqlServer 事务与锁
发布时间：2020-12-12 16:07:21 所属栏目：MsSql教程 来源：网络整理
导读：1.1.????????事务的属性 事务具有ACID属性 即?Atomic原子性,?Consistent一致性,?Isolated隔离性,?Durable永久性 原子性?? ???就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留 一致性 ???事务完成或
|
1.1.????????事务的属性
事务具有ACID属性 即?Atomic原子性,?Consistent一致性,?Isolated隔离性,?Durable永久性 原子性?? ???就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留 一致性 ???事务完成或者撤销后，都应该处于一致的状态 隔离性 ???多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据 永久性 ???事务提交以后，所做的工作就被永久的保存下来 1.2.????????事务并发处理会产生的问题 丢失更新 ????当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题、每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。 脏读 ?????当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。 ?????第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。 不可重复读 ?????当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。 ?????不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。 ?????然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。 幻像读 ??????当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。 ??????事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。 1.3.????????事务处理类型 自动处理事务 ???系统默认每个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?命令启动一个分布式事务处理。 1.4.????????事务处理的隔离级别 使用SET?TRANSACTION?ISOLATION?LEVEL来控制由连接发出的所有语句的默认事务锁定行为 从低到高依次是： READ?UNCOMMITTED 执行脏读或?0?级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置?NOLOCK?相同。这是四个隔离级别中限制最小的级别。 举例 设table1(A,B,C) A????B????C a1???b1???c1 a2???b2???c2 a3???b3???c3 新建两个连接 在第一个连接中执行以下语句 select?*?from?table1 begin?tran update?table1?set?c='c' waitfor?delay?'00:00:10'??--等待10秒 rollback?tran 在第二个连接中执行以下语句 SET?TRANSACTION?ISOLATION?LEVEL?READ?UNCOMMITTED print?'脏读' if?@@rowcount>0 begin ?waitfor?delay?'00:00:10'? ?print?'不重复读' ?select?*?from?table1 en 第二个连接的结果 a1???b1???c a2???b2???c a3???b3???c '不重复读' READ?COMMITTED 指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是?SQL?Server?的默认值。 SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED print?'初始' print?'不重复读' 第一个连接的结果 初始 ? 不重复读 REPEATABLE?READ 锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。 SET?TRANSACTION?ISOLATION?LEVEL?REPEATABLE?READ print?'幻像读' insert??table1?select?'a4','b4','c4' a4???b4???c4 ?SERIALIZABLE 在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有?SELECT?语句中的所有表上设置?HOLDLOCK?相同。 SET?TRANSACTION?ISOLATION?LEVEL?SERIALIZABLE print?'没有变化' 没有变化 1.5.????????事务处理嵌套语法和对@@TRANCOUNT的影响 l?????????????BEGIN?TRAN???????????@@TRANCOUNT＋1 l?????????????COMMIT?TRAN????????@@TRANCOUNT－1 l?????????????ROLLBACK?TRAN?????使@@TRANCOUNT回归0 l?????????????SAVE?TRAN?????????????不影响@@TRANCOUNT 例一 SELECT?'事务处理前',?@@TRANCOUNT??????--值为?0 BEGIN?TRAN ??SELECT?'第一个事务',?@@TRANCOUNT??????--值为?1 ????SELECT?*?FROM?table1 ????BEGIN?TRAN? ???????SELECT?'第二个事务',?@@TRANCOUNT??--值为?2 ?????????DELETE?table1 ????COMMIT?TRAN ????SELECT?'递交第二个事务',?@@TRANCOUNT?--值为?1 ROLLBACK?TRAN SELECT?'回滚第一个事务',?@@TRANCOUNT?--值为?0 例二 BEGIN?TRAN? ??SAVE?TRAN?t1 ??SELECT?'保存第一个事务后',?@@TRANCOUNT?--值为?1? ???ROLLBACK?TRAN?t1 ????SELECT?'回滚到保存点t1',?@@TRANCOUNT?--注意这里的值为?2? IF?@@TRANCOUNT>0 SELECT?'处理结束',?@@TRANCOUNT?--为?0 SET?XACT_ABORT 控制语句产生运行时错误时，是否自动回滚当前事务 比如 SET?XACT_ABORT?ON ??SELECT?*?FROM?一个不存在的表 ROLL?BACKTRAN PRINT?'处理完毕'??--执行结果没有到这一步 go SELECT?@@TRANCOUNT??--值为1?产生孤立事务 1.6.????????事务调试语句 DBCC?OPENTRAN 如果在指定数据库内存在最旧的活动事务和最旧的分布和非分布式复制事务，则显示与之相关的信息。 示例 下例获得当前数据库和?pubs?数据库的事务信息。 --?Display?transaction?information?only?for?the?current?database. GO --?Display?transaction?information?for?the?pubs?database. DBCC?OPENTRAN('pubs') 1.7.????????事务嵌套的例子 1)?如果内层事务出错就取消所有事务 BEGIN?TRAN?t1 ???UPDATE?tablename?SET?colname='37775'?WHERE?id='140' ??????? ?BEGIN?TRAN??t2 ???UPDATE?tablename?SET?colname='37775'?WHERE?id='140'?--id不存在 ?????IF?(@@rowcount=0)? ?????????BEING ???????????ROLLBAKC?TRAN? ??????????END ??????ELSE? ??????????BEGIN? ????????????COMMIT?TRAN??t2 ????????????COMMIT?TRAN??t1 2)?事务处理中只要有一个出错就回滚 ??????UPDATE?tablename?SET?colname='37775'?WHERE?id=170 ????????IF?(?@@rowcount=0?)? ??????????ROLLBACK?TRAN ??????UPDATE?tablename?SET?colname='37775'?WHERE?id=870??--此id不存在 3)?有关循环处理事务的问题，要求循环处理中，如果一个条件不满足就取消所有循环 DECLARE?@id?INT SET??@id=1 WHILE?(@id<280)? --注表中实际id最大为260，所以此循环执行到@id=261?update语句就没有作用了 ?BEGIN? ???BEGIN?TRAN ????UPDATE?tablename?SET?colname='37775'?WHERE?id=@id ?????IF?(?@@rowcount=0?)? ????SET??@id=@id+1 ??END
| 隔离级别 |
脏读 |
不可重复读 |
幻读 |
|---|