数据库中有一张名为后宫佳人的表。每天都有数以百万计的新女孩被插入餐桌。光阴似箭，日日夜夜，情长日久。随着时间的推移，桌子上有数十亿。资料小姐。图片来自Pexels。看亿万名小姐，夜夜愁。这么多小姐姐，怎么翻个牌？解决的办法当然是更高效和精简管理，把那些年龄>18的删掉，给年轻的女士们留个位子给我们。。。所以我在数据库里加了一个定时执行的小程序。每个星期天，都会自动运行下面的脚本：deletefrom`harembeauties`whereage>18一开始我自我感觉良好，后来发现不对劲，每个星期天，这个脚本执行一整天。运行时间有点长是小事。关键是今天真是个好星期天。我无法再读取此表中的数据。怎么会这么空？我很难过……为什么？我不能再写了。真实的背景是在公司遇到的一张数据量很大的表。每次我们清理历史数据的时候，我们的程序都会因为读取不到这张表中的数据而疯狂报错。后来查了一下，原来是定时删除语句设计不合理，导致数据库中的数据从行锁（Rowlock）升级为表锁（Tablelock）！😂在解决这个问题的过程中，数据库我学到了一些关于锁的知识，在这里把学习的成果分享给大家，希望对大家有所帮助。我将讨论SQLServer锁机制以及如何使用SQLServer标准动态管理视图来监控SQLServer中的锁。相信其他数据的锁也大同小异，有一定的参考意义。在开始解释SQLServer锁定体系结构之前，让我们花点时间描述一下什么是ACID（原子性、一致性、隔离性和持久性）。ACID是指数据库管理系统（DBMS）在写入或更新数据的过程中，为保证事务正确可靠所必须具备的四个特性：原子性（或不可分割性）一致性一致性（isolation，又称独立性）耐久性（durability）ACID①原子性（Atomicity）一个事务（transaction）中的所有操作，或者全部完成，或者根本没有完成，都不会在中间某个环节结束。如果事务执行过程中出现错误，则会回滚（Rollback）到事务开始前的状态，就好像事务从未执行过一样。也就是说，交易是不可分割和不可减少的。②一致性（Consistency）在事务开始前和事务结束后，数据库的完整性没有被破坏。这意味着写入的数据必须完全遵守所有预设的约束、触发器、级联回滚等。③隔离（Isolation）数据库允许多个并发事务同时读取、写入和修改其数据。隔离可以防止多个事务并发执行时交叉执行导致的数据不一致。事务隔离分为不同的级别，包括未提交读（Readuncommitted）、提交读（readcommitted）、可重复读（repeatableread）和序列化（Serializable）。④持久性（Durability）事务完成后，对数据的修改是永久性的，即使系统出现故障也不会丢失。来源维基百科：https://zh.wikipedia.org/wiki/ACID事务（Transaction）事务是一个流程中最小的堆栈，不能被分割成更小的部分。此外，某些事务组可以顺序执行，但正如我们在原子性原则中解释的那样，即使其中一个事务失败，所有事务块也会失败。锁（Lock）锁是一种保证数据一致性的机制。SQLServer在事务开始时锁定对象。事务完成后，SQLServer释放锁定的对象。这种锁定模式可以根据SQLServer进程类型和隔离级别而改变。这些锁定模式是：①锁定层次结构SQLServer有一个锁定层次结构用于获取这个层次结构中的锁定对象。数据库位于层次结构的顶部，行位于底部。下图说明了SQLServer的锁层次结构：②共享（S）锁（Shared(S)Locks）这种锁类型发生在一个对象需要被读取的时候。这种类型的锁定不会造成太大问题。③排他(X)锁(Exclusive(X)Locks)当出现这种锁类型时，它的发生是为了防止其他事务修改或访问被锁定的对象。④更新(U)锁(Update(U)Locks)这种锁类型类似于排他锁，但又有些区别。我们可以将更新操作分为不同的阶段：读取阶段和写入阶段。在读取阶段，SQLServer不希望其他事务访问这个对象进行更改，所以SQLServer使用了更新锁。⑤意向锁（IntentLocks）意向锁发生在SQLServer想要获取锁层次结构中较低层的某些资源上的共享（S）锁或排它（X）锁时。实际上，当SQLServer获取页或行的锁时，需要在表上设置意向锁。SQLServer加锁了解了这些背景知识后，我们尝试在SQLServer中找到这些锁。SQLServer提供了许多动态管理视图来访问指标。要识别SQLServer锁，我们可以使用sys.dm_tran_locks视图。在这个视图中我们可以找到很多关于当前主动锁管理的信息。在第一个示例中，我们将创建一个没有任何索引的演示表并尝试更新此演示表：CREATETABLETestBlock(IdINT,NmVARCHAR(100))INSERTINTOTestBlockvalues(1,'CodingSight')在这一步中，我们将创建一个打开的事务并分析锁定的资源。BEGINTRANUPDATETestBlockSETNm='NewValue_CodingSight'whereId=1select@@SPID获取到SPID后，我们看一下sys.dm_tran_lock视图中的内容：select*fromsys.dm_tran_locksWHERErequest_session_id=74这个视图返回了很多关于activelock资源的信息，但是它是我们无法理解某些数据。因此，我们必须将sys.dm_tran_locksjoin一些其他表：SELECTdm_tran_locks.request_session_id,dm_tran_locks.resource_database_id,DB_NAME(dm_tran_locks.resource_database_id)ASdbname,CASEWHENresource_type='OBJECT'THENOBJECT_NAME(dm_tran_locks.resource_associated_entity_id)ELSEOBJECT_NAME(partitions.OBJECT_ID)ENDASObjectName,partitions.index_id,indexes.nameASindex_name,dm_tran_locks.resource_type,dm_tran_locks.resource_description,dm_tran_locks.resource_associated_entity_id,dm_tran_locks.request_mode,dm_tran_locks.request_statusFROMsys.dm_tran_locksLEFTJOINsys.partitionsONpartitions.hobt_id=dm_tran_locks.resource_associated_entity_idLEFTJOINsys.indexesONindexes.OBJECT_ID=partitions.OBJECT_IDANDindexes.index_id=partitions.index_idWHEREresource_associated_entity_id>0ANDresource_database_id=DB_ID()andrequest_session_id=74ORDERBYrequest_session_id,resource_associated_entity_id在上图中你可以看到锁定的资源。SQLServer获取行上的独占锁。（RID：用于在堆中锁定单个行的行标识符）同时，SQLServer获取页上的排它锁和TestBlock表上的意向锁。这意味着在SQLServer释放锁之前，没有其他进程可以读取此资源，这是SQLServer中的基本锁定机制。现在，我们将在测试表上填充一些合成数据：TRUNCATETABLETestBlockDECLARE@KASINT=0WHILE@K<8000BEGININSERTTestBlockVALUES(@K,CAST(@KASvarchar(10))+'Value')SET@K=@K+1END--Aftercompletingthisstep，我们将运行两个查询并检查thesys.dm_tran_locksview.BEGINTRANUPDATETestBlocksetNm='New_Value'whereId<5000在上面的查询中，SQLServer为每一行获取一个独占锁。现在，我们将运行另一个查询：BEGINTRANUPDATETestBlocksetNm='New_Value'whereId<7000在上面的查询中，SQLServer在表上创建了独占锁，因为SQLServer试图为这些将被更新的行获取大量RID锁。这种情况会导致数据库引擎大量消耗资源，因此SQLServer会自动将此排他锁移至锁层次结构中的上级对象（表）。我们把这个机制定义为LockEscalation，也就是我一开始提到的锁升级。由行锁升级为表锁。根据官方文档，以下任何一种情况都会触发锁升级：单个Transact-SQL语句在单个非分区表或索引上至少获取5,000个锁。单个Transact-SQL语句在ALTERTABLESETLOCK_ESCALATION选项设置为AUTO的分区表的单个分区上获取至少5,000个锁。数据库引擎实例中的锁数超过内存或配置阈值。链接如下：https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms184286(v=sql.105)如何避免锁升级最简单防止锁升级的方法，最安全的方法是保持事务简短并减少昂贵查询的锁占用空间，以便不超过锁升级阈值，有几种方法可以实现这一点。①将大批量操作分解为若干个较小的操作。比如从亿条数据中删除小姐姐的数据，一开始就说：deletefrom`harembeauties`whereage>18我们不能那么急躁，一次只删除500条，大大减少了每个事务累积的锁并防止锁升级。例如：SETROWCOUNT500delete_more:deletefrom`HaremBelle`whereage>18IF@@ROWCOUNT>0GOTOdelete_moreSETROWCOUNT0②创建索引，使查询尽可能高效，减少查询的锁空间。如果没有索引，表扫描可能会增加锁升级的可能性，更可怕的是，它增加了死锁的可能性，通常会对并发和性能产生负面影响。根据查询条件创建合适的索引，最大限度地提高索引查找的效率。此优化的一个目标是使索引查找返回尽可能少的行，以最大限度地降低查询成本。③如果其他SPID当前持有不兼容的表锁，则不会发生锁升级锁升级总是升级为表锁，而不是页锁。如果另一个SPID持有与升级表锁冲突的IX（意向排他）锁，它将获取更细粒度级别（行、键或页）锁，定期进行额外的升级尝试。表级IX（意向排他）锁定不会锁定任何行或页，但它仍然与升级的S（共享）或X（排他）TAB锁不兼容。如下所示，如果有一个操作总是在不到一个小时内完成，则可以创建包含以下代码的SQL并安排在该操作之前执行：BEGINTRANSELECT*FROMmytable(UPDLOCK,HOLDLOCK)WHERE1=0WAITFORDELAY'1:00:00'COMMITTRAN此查询在mytable上获取并持有IX锁一小时，这可防止在此期间对表进行锁升级。HappyEnding还好，不说了，小姐姐们因为不想离开我又吵架了（僵局）。作者：Ayu编辑：陶佳龙来源：https://www.cnblogs.com/CoderAyu/
