背景在数据库中，有一张表叫后宫佳人。每天都有数以百万计的新女孩被插入餐桌。数据里有几十亿小姐姐，看到几十亿小姐姐，每天晚上，我好担心，那么多小姐姐，怎么翻一张卡？解决办法当然是更高效简单，删掉那些年龄>18的，给小姐姐们留个位子。。。所以我在数据库里加了一个定时执行的小程序。每个星期天自动启动下面的脚本deletefrom`harembeauties`whereage>18自我感觉还不错，后来发现不对。每个星期天，该脚本都会执行一整天。运行时间长是小事。重点是今天是个很棒的星期天，我想再读一遍这张表的数据，我都看不下去了，一句怎么这么空洞，难受啊！为什么我不能继续编辑它？真实的背景是在公司遇到的一张数据量很大的表。每次都清理一次历史数据，因为我们的程序读取不到这张表的数据，就疯狂报错了。后来发现是因为定时删除的语句设计不合理，导致数据库中的数据从行锁升级到表锁。（表锁）😂。在解决这个问题的过程中，我了解了数据库锁。在这里我将学习成果分享给大家。希望对您有所帮助。我将讨论SQLServer锁机制以及如何使用SQLServer标准动态管理视图来监控SQLServer中的锁。相信其他数据的锁也大同小异，有一定的参考意义。在开始解释SQLServer锁定体系结构之前，让我们花点时间描述一下ACID（原子一致性、一致性、隔离性和持久性）。ACID是指数据库管理系统（DBMS）在写入或更新数据的过程中，为保证事务正确可靠而必须具备的四个特性：原子性（atomicity，或不可分割性）、一致性、隔离性（也称为独立性）和耐用性。ACID#atomicity(原子性)#一个事务(transaction)中的所有操作要么全部完成，要么全部未完成，不会在中间某个环节结束。如果事务执行过程中出现错误，则会回滚（Rollback）到事务开始前的状态，就好像事务从未执行过一样。也就是说，交易是不可分割和不可减少的。一致性（Consistency）#事务开始前和事务结束后，不违反数据库的完整性。这意味着写入的数据必须完全遵守所有预设的约束、触发器、级联回滚等。隔离（Isolation）#数据库允许多个并发事务同时读取、写入和修改其数据。隔离可以防止多个事务并发执行时交叉执行导致的数据不一致。事务隔离分为不同的级别，包括未提交读（Readuncommitted）、提交读（readcommitted）、可重复读（repeatableread）和序列化（Serializable）。持久性（Durability）#事务完成后，对数据的修改是永久性的，即使系统出现故障也不会丢失。来源：维基百科https://zh.wikipedia.org/wiki/ACIDTransaction(Transaction:)#事务是一个流程中最小的栈，不能再分割成更小的部分。此外，某些事务组可以顺序执行，但正如我们在原子性原则中解释的那样，即使其中一个事务失败，所有事务块也会失败。锁（Lock）#Locking是一种保证数据一致性的机制。SQLServer在事务开始时锁定对象。事务完成后，SQLServer将释放锁定的对象。这种锁定模式可以根据SQLServer进程类型和隔离级别而改变。这些锁模式是：LockHierarchy#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')在这一步中，我们将创建一个opentransactionandanalyzethelockedresources.BEGINTRANUPDATETestBlockSETNm='NewValue_CodingSight'whereId=1select@@SPID获取SPID后，让我们看看sys_tranm_tran中有什么。select*fromsys.dm_tran_locksWHERErequest_session_id=74这个视图返回了很多主动锁资源的信息，但是有些数据我们很难理解。所以我们必须将sys.dm_tran_locks连接到其他一些表。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--完成这一步后，wewillruntwoqueriesandcheckthesys.dm_tran_locksview.BEGINTRAN0ValwhereTestBlock_setNue5在上面的查询,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个，可以显着减少每次事务中累积的锁数，防止Lock升级。例如：SETROWCOUNT500delete_more:deletefrom`后宫美女`whereage>18IF@@ROWCOUNT>0GOTOdelete_moreSETROWCOUNT0创建一个索引，让查询尽可能高效，减少查询的锁空间#如果没有索引，表扫描可能增加的可能性锁升级，这更可怕不幸的是，它增加了死锁的可能性并且通常对并发性和性能产生负面影响。根据查询条件创建合适的索引，最大限度地提高索引查找的效率。此优化的一个目标是使索引查找返回尽可能少的行，以最大限度地降低查询成本。如果其他SPID当前持有不兼容的表锁，则不会发生锁升级#Lockescalation总是升级为表锁，而不是页锁。如果另一个SPID持有与升级表锁冲突的IX（意向排他）锁，它将获取更细粒度级别（行、键或页）锁，定期进行额外的升级尝试。表级IX（意向排他）锁定不会锁定任何行或页，但它仍然与升级的S（共享）或X（排他）TAB锁不兼容。如下图，如果有一个操作总是不到一个小时就完成，那么可以创建一个包含如下代码的sql，安排执行BEGINTRANSELECT*FROMmytable(UPDLOCK,HOLDLOCK)WHERE1=0WAITFORDELAY'1:00：00在操作'COMMITTRAN之前此查询获取并持有mytable上的IX锁一小时，这可防止在此期间对表进行锁升级。HappyEnding还好，不说了，小姐姐们因为不想离开我又吵架了（僵局）。
