SQL Server 中锁的种类,Server提供悲观和乐观并发控制模式
分类:博客热点

本文章简单的介绍了关于SQL Server,事务和锁的常见问题与用法同时为初学者提供好的参考意见,有需要的可以参考一下。 最近在项目中进行压力测试遇到了数据库的死锁问题,简言之,如下的代码在 SERIALIZABLE 隔离级别造成了死锁:

大家好,欢迎回到性能调优培训。今天标志着第5个月培训的开始,这个月我们会谈论SQL Server里的锁、阻塞和死锁(Locking, Blocking, and Deadlocking)。

 代码如下复制代码 SELECT @findCount=COUNT(id) FROM MyTableWHERE [fk_related_id]=@ArgumentIF (@findCount  0)BEGINROLLBACK TRANSACTIONRETURN ERROR_CODEENDINSERT INTO MyTable ([fk_related_id],)VALUES (@Argument,)COMMIT TRANSACTIONRETURN SUCCESS_CODE

SQL Server提供悲观和乐观并发控制模式,它们用来定义并发查询的执行。这期我会给你讲解悲观并发控制模式里各种隔离级别概况,下周我会进一步介绍自SQL Server 2005起引入的乐观隔离级别情况。

在搞清楚这个问题的过程中做了不少的实验,与各位共享。这一篇是开篇,主要说明的是 SQL Server 的四种经典的事务隔离级别,以及在不同的隔离级别下锁的使用手段,以及所带来的不同的数据一致性。

悲观隔离级别(Pessimistic Isolation Levels)

悲观隔离级别意味着读查询(SELECT)阻塞写查询(INSERT,UPDATE,DELETE),而且写查询阻塞读查询。SQL Server对此行为使用所谓的锁(Locks)

  • 读操作获取共享锁(Shared Locks (S))
  • 写操作获取排它锁(Exclusive Locks (X))

2个锁之间互不兼容。这就是说不能同时读写一条记录。如果这个发生的话,就会出现所谓的阻塞(Blocking)情形。当你设置指定的事务隔离级别(transaction isolation level)后,你就直接影响读查询在写查询同时进行时如何把持它们的共享锁(S)。你不能影响写操作——当你在表上修改一条记录(INSERT,UPDATE,DELETE)总会拿到排它锁(X)。

默认情况每个查询在提交读(Read committed)隔离级别运行。提交读意味着SQL Server在记录读取时会在记录上获取一个共享锁(S),一旦记录完全读取或处理,共享锁(S)就是立即释放。当你对表进行扫描(Scan)运算符(单线程),在给定时间内只有一个共享锁(S)把持着。因为这个行为,其他事务随后修改记录是可能的。如果你在同个事务里读取同个记录,你应该使用所谓的不可重复读(Non-Repeatable Read):你多次读取一条记录,但却返回不同的值。

如果你不能忍受可重复读的行为,你可以使用限制更多的可重复读(Repeatable Read)。这个隔离级别给你可重复的读(因名而来),即当你读取一条记录时,SQL Server会保持共享锁(S)直到你事务的结束。因此在你读取的事务期间,没有人可以获取排它锁(X)来改变你的记录(因为这个不兼容性,排它锁会向阻塞让步)。这个方法有优点也有缺点:一方面你获得更准确的记录(可重复读),另一方面你会有更多的阻塞发生,因为读操作把持它们的共享锁(S)直到它们事务的结束。你要在并发控制和数据准确性之间权衡。

你还可以通过改变隔离级别为可串行化(Serializable)来进一步限制。使用那个在SQL Server里最有限制的隔离级别——你可以避免所谓的虚影记录(Phantom Records)。当你多次从表获取记录时,虚影记录会出现并消失。为了避免虚影记录,SQL Server使用所谓的Key Range Locking技术,即通过锁定你初次获取的范围数据。

因此没有其它的并发查询可以在锁定范围内插入记录。从范围内删除记录,或“移动”另一条记录到此范围的更新语句都是不可能的。这样的查询只会阻塞。在你行范围定义记录的查询谓语上,你也需要支持的索引。用支持的索引,SQL Server会锁定各个索引键。没有支持的索引,SQL Server就会锁定你的整张表,这会大大伤及你数据库的并发和工作量!

最后SQL Server支持未提交读(Read Uncommitted)隔离级别。使用提交读,当读取数据时,不需要获得共享锁(S)。因此从当前正在进行的事务中读到未提交的数据是可能的。那就是所谓的脏读(Dirty Read)。如果这样的事务回滚,你就读到了数据库里逻辑上不存在的数据。这是个并不推荐的隔离级别,用的时候要慎重考虑下。使用著名的NOLOCK查询提示就可以强制脏读。

悲观隔离级别并不复杂,是不是?隔离级别就是表示对于读取的数据共享锁(S)可以把持多久。基于此,隔离级别就定义了在数据读取期间,哪些是可以操作的,哪些是不能操作的。看下图就会明白。

                脏读(Dirty Read)  不可重复读(Non-Repeatable Read)  虚影记录(Phantom Records)

未提交读(Read Uncommitted)     是          是                  是

提交读(Read Committed)     否          是                  是

重复读(Repeatable Read)     否          否                  是

可序列话(Serializable)        否          否                  否

另外为了保证查询的正确性,对于指定的隔离级别,SQL Server会临时提升隔离级别。你可以围观这个文章:事务隔离级别神话与误解。 

SQL Server 中锁的种类 锁类型 描述 用于只读操作数据锁定 用于数据的更新,在数据真正的需要更新的时候会申请升级为X锁。 X 独占锁,用于数据的更改。 Key-Range Lock 仅仅在 Serializable 隔离级别保护数据,以避免任何有可能使得本事务第二次读取信息产生错误的数据插入操作 各个事务隔离级别下锁的使用SQL Server 中有四种事务隔离级别,具体的大家去参建 MSDN。下面列出在不同的事务隔离级别下这些锁是如何使用的: 隔离级别 读数据锁状态 写数据锁状态 锁持有时间 Read Uncommitted 不获得任何锁 不获得任何锁 Read Committed 数据获得S锁 对于 INSERT、DELETE、UPDATE的执行,获得X锁;对于UPDATE的标记,获得U锁; 读完即释放,并不持有至事务结束。 Repeatable Read 数据获得S锁 对于 INSERT、DELETE、UPDATE的执行,获得X锁;对于UPDATE的标记,获得U锁; 持有至事务结束 Serializable 数据获得S锁,同时获得Key-Range锁。 对于 INSERT、DELETE、UPDATE的执行,获得X锁;对于UPDATE的标记,获得U锁,同时获得Key-Range锁。 持有至事务结束 我们可以利用这些知识形象说明各个隔离级别下的数据一致性:Read Uncommitted 级别脏读更新丢失不可重复读幻读Read Committed 级别脏读更新丢失不可重复读幻读Repeatable Read 级别脏读更新丢失不可重复读幻读Serializable 级别脏读更新丢失不可重复读幻读我们从上图可以比较直观的看到以下的结论 脏读 更新丢失 不可重复读 幻读 Read Uncommitted 可能 可能 可能 可能 Read Committed 不可能 可能 可能 可能 Repeatable Read 不可能 不可能 不可能 可能 Serializable 不可能 不可能 不可能 不可能 这一篇到此为止,下一篇详细介绍 Key-Range Lock 并分析开篇提到的死锁问题

小结

今天你已经学习了SQL Server里各个悲观隔离级别的基础。当你对SQL Server里的锁和阻塞情况进行故障排除时,这是你必须知道的基础:读查询(SELECT)阻塞写查询(INSERT,UPDATE,DELETE),而且写查询阻塞读查询。

下周我们会谈论SQL Server支持的使用乐观并发控制(Optimistic Concurrency)组合的另外2个隔离级别。请继续关注!

本文由威尼斯在线注册平台发布于博客热点,转载请注明出处:SQL Server 中锁的种类,Server提供悲观和乐观并发控制模式

上一篇:php 获取图片尺寸 下一篇:没有了
猜你喜欢
热门排行
精彩图文