网站建设提供排名,网站公司上海,扬州网站建设suteng,毕业做网站运营好吗SQL SERVER的锁机制#xff08;一#xff09;——概述#xff08;锁的种类与范围#xff09; SQL SERVER的锁机制#xff08;二#xff09;——概述#xff08;锁的兼容性与可以锁定的资源#xff09; 本文上接SQL SERVER的锁机制#xff08;三#xff09;——概述一——概述锁的种类与范围 SQL SERVER的锁机制二——概述锁的兼容性与可以锁定的资源 本文上接SQL SERVER的锁机制三——概述锁与事务隔离级别 六、各种事务隔离级别发生的影响 修改数据的用户会影响同时读取或修改相同数据的其他用户。即这些用户可以并发访问数据。如果数据存储系统没有并发控制则用户可能会看到以下负面影响 · 未提交的依赖关系脏读 · 不一致的分析不可重复读 · 幻读 一脏读 例张某正在执行某项业务如下 begin traninsert tbUnRead select 3,张三unionselect 4,李四---延迟秒模拟真实交易情形用于处理业务逻辑waitfor delay 00:00:05rollback tran---此时李某对表中数据进行查询执行了以下语句set Transaction isolation level read uncommitted--查询数据select * from tbUnRead where name like 张% 则李某可以看到张某所执行的插入语句把数据添加到了数据库如下图。 但是张某最终是没有提交事务而是回滚了事务所以这条记录并没有真正插入到数据库中。从而发生李某将脏读的数据当成真实的查询结果。 要解决此问题就是要把数据库的事务隔离级别由未提交读修改成已提交读。只有当查询结果的正确性不是非常重要或者是隔一段时间查询一次情况下即使这一次查询结果是错而下次查询结果是对的并不会有太大影响这才适合使用未提交读。 二不可重复读 例张某正在查询数据如下 set Transaction isolation level read committedbegin transelect * from tbUnRead where ID2---延迟秒模拟真实交易情形用于处理业务逻辑waitfor delay 00:00:05select * from tbUnRead where ID2commit tran---此时李对表中数据进行了更新如下语句update tbUnReadset nameJack_updwhere ID2 上面的执行语句造成张某在同一个事务内两次相同的查询条件查询到不相同的结果如下图。 这是由于“已提交读”隔离级别对共享锁保留的时间是一旦查询完毕就立即释放而非事务完成才释放。所在张某虽然还在使用事务事务过程中的所有独占锁都会一直保留让事务中所更改的数据别人不可进行查询与更改直到事务完成。但是被查询的数据在事务过程中是查询完毕就立即释放共享锁所以别人仍然可以进行修改造成一笔事务中两次相同的查询条件可以得到不相同的结果。最佳的解决方案是将隔离级别设置为“可重复读”。 “可重复读”事务隔离级别让事务过程中所曾经建立的共享锁都一直保留到事务完成虽然可以避免“不可重复读”的问题但是也会导致数据锁定太久而别人无法读取数据影响并发率甚至提高了“死锁”的发生率。 三幻读 例张某正在查询数据如下 set Transaction isolation level REPEATABLE READbegin transelect * from tbUnRead where ID3---延迟秒模拟真实交易情形用于处理业务逻辑waitfor delay 00:00:05select * from tbUnRead where ID3commit tran--此时李某新增了一条记录如下语句INSERT TBUNreadselect 3,幻读 张某已经把隔离级别设置为“可重复读”虽然是曾经读取的数据不管是共享锁还是互斥锁都 保留到了事务结束但是无法阻止其他人运行新增操作导致第一次查询时没有数据第二次查询时却有了数据。被称为“幻读”。如下图。 为了避免此类问题可以将隔离级别设置为“可序列化”设置之后则其他人则无法新增数据。 七、各隔离级别所能防止的访问错误 隔离级别 脏读 不可重复读 幻读 未提交读 是 是 是 已提交读 否 是 是 可重复读 否 否 是 快照 否 否 否 可序列化 否 否 否 八、常用锁与事务隔离级别间的交互影响 已提交读 可重复读 快照 可序列化 共享 读完数据后就释放 事务结束才释放 不加锁以版本来控制 事务结束才释放 更新 读完数据后就释放或是升级成独占锁 读完数据后就释放或是升级成独占锁 不加锁以版本来控制 读完数据后就释放或升级成独占锁 独占 事务结束才释放 事务结束才释放 不加锁以版本来控制 事务结束才释放 九、动态锁定管理 数据库引擎使用动态锁定管理策略来控制锁定和系统的最佳成本效益。数据库引擎可以动态调整数据粒度与锁定类型当使用最低一级的行锁而非更大范围的页锁时可以降低两个事务要求相同范围的数据锁定的可能性增强并行访问的能力可同时服务更多的用户减小死锁的机率。相反低级锁转为高级锁可以减小系统的资源负担但会增加并行争用的可能性。 此机制由锁管理器进行管理每一个锁都需要内存去记录并且要与锁管理器进行合作才能完成数据访问操作你可以想像当表中有100万条记录时你执行一条没有where语句的update指令时在默认情况下数据库引擎会采用行锁但这要记录100万条行锁记录以及相关的意向共享锁必定会消耗掉大量的系统资源当系统资源不足时数据库引擎会自动提升锁的级别也就是由行锁提升为页锁如果资源还是不足则会再次提升提升为表锁。 就以上例子来说如果每个页可以放200条记录则100万表记录的行锁转为5000个页锁还省掉了大量的意向共享锁。如果资源还是一足则可以再次提升锁级别提升到表锁这样就只需要一个锁就可以了。 愈大范围的锁花费在管理锁之上的资源就愈少。但相对来说同时上线并发访问该资源的人数就越少。例如或采用行锁则你访问你的记录我访问我的记录互相不影响但如果升级到页锁则如果你先抢到该分页而我要访问的记录又恰恰在这一分页上则我必须要等你释放该分页之后才能访问。如果升级到表锁则同一时间该表中的记录只能一个人才能访问其他人不能访问。如下图。 一般情况下是不需要手工去设置锁定范围的可以由Microsoft SQL Server 数据库引擎视情况而定使用动态锁定策略确定最经济的锁。 执行查询时数据库引擎会根据架构和查询的特点自动决定最合适的锁。 例如为了缩减锁定的开销优化器可能在执行索引扫描时在索引中选择页级锁。 动态锁定具有下列优点 · 简化数据库管理。 数据库管理员不必调整锁升级阈值。 · 提高性能。 数据库引擎通过使用适合任务的锁使系统开销减至最小。 · 应用程序开发人员可以集中精力进行开发。 数据库引擎将自动调整锁定。 在 SQL Server 2008 中锁升级的行为已发生改变其中引入了 LOCK_ESCALATION选项转载于:https://www.cnblogs.com/chillsrc/archive/2013/04/27/3047547.html
