sql server锁的理解

今天分享一下小编对sql server中锁(lock)的理解。本文会先介绍引入锁的原因,然后讲解锁两个重要的概念锁模式和粒度,最后会教大家如何通过sql server查看锁的状态。

一、 引入锁的原因

多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:

丢失更新

A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统

脏读

A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致

不可重复读

A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致 并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

二、 锁的分类

锁的类别有两种分法:

1. 从数据库系统的角度来看:分为独占锁(即排它锁),共享锁和更新锁

SQL Server 使用以下资源锁模式。

锁模式 描述

共享 (S)用于不更改或不更新数据的操作(只读操作),如 SELECT 语句。

更新 (U)用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

排它 (X)用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

2. 从程序员的角度看:分为乐观锁和悲观锁。

乐观锁:完全依靠数据库来管理锁的工作。

悲观锁:程序员自己管理数据或对象上的锁处理。 MS-SQLSERVER 使用锁在多个同时在数据库内执行修改的用户间实现悲观并发控制

当一个事务需要访问的资源加了其所不兼容的锁,SQL Server会阻塞当前的事务来达成所谓的隔离性。直到其所请求资源上的锁被释放,如下图所示。

三、 锁的粒度

锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小。

锁是加在数据库对象上的。而数据库对象是有粒度的,比如同样是1这个单位,1行,1页,1个B树,1张表所含的数据完全不是一个粒度的。因此,所谓锁的粒度,是锁所在资源的粒度。所在资源的信息也就是前面图3中以Resource开头的信息。

对于查询本身来说,并不关心锁的问题。就像你开车并不关心哪个路口该有红绿灯一样。锁的粒度和锁的类型都是由SQL Server进行控制的(当然你也可以使用锁提示,但不推荐)。锁会给数据库带来阻塞,因此越大粒度的锁造成更多的阻塞,但由于大粒度的锁需要更少的锁,因此会提升性能。而小粒度的锁由于锁定更少资源,会减少阻塞,因此提高了并发,但同时大量的锁也会造成性能的下降。因此锁的粒度对于性能和并发的关系如下图所示。

在SQL Server中,锁的粒度如下所示。

有一个有趣的现象叫锁的升级。前面说到锁的粒度和性能的关系。实际上,每个锁会占96字节的内存,如果有大量的小粒度锁,则会占据大量的内存。

下面我们来看一个例子,当我们选择几百行数据时(总共3W行),SQL Server会加对应行数的Key锁,

但当所取得的行的数目增大时,比如说6000(表中总共30000多条数据),此时如果用6000个键锁的话,则会占用大约96*6000=600K左右的内存,所以为了平衡性能与并发之间的关系,SQL Server使用一个表锁来替代6000个key锁,这就是所谓的锁升级。

四、如何查看锁

查看锁的手段有两种

一是采用sys.dm_tran_locks这个DMV。

SQL Server提供了sys.dm_tran_locks这个DMV来查看当前数据库中的锁,前面的图2就是通过这个DMV来查看的.

这里值得注意的是sys.dm_tran_locks这个DMV看到的是在查询时间点的数据库锁的情况,并不包含任何历史锁的记录。可以理解为数据库在查询时间点加锁情况的快照。sys.dm_tran_locks所包含的信息分为两类,以resource为开头的描述锁所在的资源的信息,另一类以request开头的信息描述申请的锁本身的信息。如下图所示。更详细的说明可以查看MSDN(http://msdn.microsoft.com/en-us/library/ms190345.aspx)

另一种方式是使用sql server 自带的profiler工具,这里不展开,仅展示一个截图:

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181003G0048H00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券