开始还自我感觉良好,后面我就发现不对了,每到周日,这个脚本一执行就是一整天,运行的时间有点长是小事,重点是这大好周日,我再想读这张表的数据,怎么也读不出来了,怎是一句空虚了得,我好难啊!为什么编不下去了,真实背景是公司中遇到的一张有海量数据表,每次一旦执行历史数据的清理,我们的程序就因为读不到这张表的数据,疯狂地报错,后面一查了解到,原来是因为定时删除的语句设计不合理,导致数据库中数据由行锁(Row lock)升级为表锁(Table lock)了.解决这个问题的过程中把数据库锁相关的学习了一下,这里把学习成果,分享给大家,希望对大家有所帮助.我将讨论SQL Server锁机制以及如何使用SQL Server标准动态管理视图监视SQL Server 中的锁,相信其他数据的锁也大同小异,具有一定参考意义.铺垫知识在我开始解释SQL Server锁定体系结构之前让我们花点时间来描述ACID原子性一致性隔离性和持久性是什么。ACID是指数据库管理系统DBMS在写入或更新资料的过程中为保证事务transaction是正确可靠的所必须具备的四个特性原子性atomicity或称不可分割性、一致性consistency、隔离性isolation又称独立性、持久性durability。ACID#原子性(Atomicity)#一个事务transaction中的所有操作或者全部完成或者全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。即事务不可分割、不可约简。一致性(Consistency)#在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。隔离性(Isolation)#数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括未提交读Read uncommitted、提交读read committed、可重复读repeatable read和串行化Serializable。持久性(Durability)#事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。来源:维基百科 https://zh.wikipedia.org/wiki/ACID事务 (Transaction:)#事务是进程中最小的堆栈不能分成更小的部分。此外某些事务处理组可以按顺序执行但正如我们在原子性原则中所解释的那样即使其中一个事务失败所有事务块也将失败。锁定 (Lock)#锁定是一种确保数据一致性的机制。SQL Server在事务启动时锁定对象。事务完成后SQL Server将释放锁定的对象。可以根据SQL Server进程类型和隔离级别更改此锁定模式。这些锁定模式是锁定层次结构#SQL Server具有锁定层次结构用于获取此层次结构中的锁定对象。数据库位于层次结构的顶部行位于底部。下图说明了SQL Server的锁层次结构。共享S锁 (Shared (S) Locks)#当需要读取对象时会发生此锁定类型。这种锁定类型不会造成太大问题。独占X锁定 (Exclusive (X) Locks)#发生此锁定类型时会发生以防止其他事务修改或访问锁定对象。更新U锁 (Update (U) Locks)#此锁类型与独占锁类似但它有一些差异。我们可以将更新操作划分为不同的阶段读取阶段和写入阶段。在读取阶段SQL Server不希望其他事务有权访问此对象以进行更改,因此SQL Server使用更新锁。意图锁定 (Intent Locks)#当SQL Server想要在锁定层次结构中较低的某些资源上获取共享S锁定或独占X锁定时会发生意图锁定。实际上当SQL Server获取页面或行上的锁时表中需要设置意图锁。SQL Server locking了解了这些背景知识后我们尝试再SQL Server找到这些锁。SQL Server提供了许多动态管理视图来访问指标。要识别SQL Server锁我们可以使用sys.dm_tran_locks视图。在此视图中我们可以找到有关当前活动锁管理的大量信息。在第一个示例中我们将创建一个不包含任何索引的演示表并尝试更新此演示表。CopyCREATE TABLE TestBlock (Id INT , Nm VARCHAR(100)) INSERT INTO TestBlock values(1,CodingSight) In this step, we will create an open transaction and analyze the locked resources. BEGIN TRAN UPDATE TestBlock SET NmNewValue_CodingSight where Id1 select SPID再获取到了SPID后我们来看看sys.dm_tran_lock视图里有什么。Copyselect * from sys.dm_tran_locks WHERE request_session_id74此视图返回有关活动锁资源的大量信息,但是是一些我们难以理解的一些数据。因此我们必须将sys.dm_tran_locksjoin 一些其他表。CopySELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type OBJECT THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_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_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id dm_tran_locks.resource_associated_entity_id LEFT JOIN sys.indexes ON indexes.OBJECT_ID partitions.OBJECT_ID AND indexes.index_id partitions.index_id WHERE resource_associated_entity_id 0 AND resource_database_id DB_ID() and request_session_id74 ORDER BY request_session_id, resource_associated_entity_id在上图中您可以看到锁定的资源。SQL Server获取该行中的独占锁。RID用于锁定堆中单个行的行标识符同时SQL Server获取页中的独占锁和TestBlock表意向锁。这意味着在SQL Server释放锁之前任何其他进程都无法读取此资源,这是SQL Server中的基本锁定机制。现在我们将在测试表上填充一些合成数据。CopyTRUNCATE TABLE TestBlock DECLARE K AS INT0 WHILE K 8000 BEGIN INSERT TestBlock VALUES(K, CAST(K AS varchar(10)) Value ) SET KK1 END --After completing this step, we will run two queries and check the sys.dm_tran_locks view. BEGIN TRAN UPDATE TestBlock set Nm New_Value where Id5000在上面的查询中SQL Server获取每一行的独占锁。现在我们将运行另一个查询。CopyBEGIN TRAN UPDATE TestBlock set Nm New_Value where Id7000在上面的查询中SQL Server在表上创建了独占锁因为SQL Server尝试为这些将要更新的行获取大量RID锁,这种情况会导致数据库引擎中的大量资源消耗,因此SQL Server会自动将此独占锁定移动到锁定层次结构中的上级对象(Table)。我们将此机制定义为Lock Escalation, 这就是我开篇所说的锁升级,它由行锁升级成了表锁。根据官方文档的描述存在以下任一条件则会触发锁定升级单个Transact-SQL语句在单个非分区表或索引上获取至少5,000个锁。单个Transact-SQL语句在分区表的单个分区上获取至少5,000个锁并且ALTER TABLE SET LOCK_ESCALATION选项设置为AUTO。数据库引擎实例中的锁数超过了内存或配置阈值。https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms184286(vsql.105)如何避免锁升级防止锁升级的最简单最安全的方法是保持事务的简短并减少昂贵查询的锁占用空间以便不超过锁升级阈值,有几种方法可以实现这一目标.将大批量操作分解为几个较小的操作#例如在我开篇所说的在几十亿条数据中删除小姐姐的数据Copydelete from 后宫佳丽 where age18我们可以不要这么心急,一次只删除500个可以显着减少每个事务累积的锁定数量并防止锁定升级。例如CopySET ROWCOUNT 500 delete_more: delete from 后宫佳丽 where age18 IF ROWCOUNT 0 GOTO delete_more SET ROWCOUNT 0创建索引使查询尽可能高效来减少查询的锁定占用空间#