数据库|如何锁住数据库中几十亿小姐姐？锁住|sql|数据库|锁定|事务|se

数据库中有一张叫后宫佳丽的表，每天都有几百万新的小姐姐插到表中，光阴荏苒，夜以继日，日久生情，时间长了，表中就有了几十亿的小姐姐数据。

文章图片

图片来自 Pexels
看到几十亿的小姐姐，每到晚上，我可愁死了，这么多小姐姐，我翻张牌呢"/>
文章图片

再获取到了 SPID 后，我们来看看 sys.dm_tran_lock 视图里有什么：
select * from sys.dm_tran_locks WHERE request_session_id=74
文章图片

此视图返回有关活动锁资源的大量信息,但是是一些我们难以理解的一些数据。
因此，我们必须将 sys.dm_tran_locks join 一些其他表：
SELECT 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_id=74 ORDER BY request_session_id, resource_associated_entity_id
文章图片

在上图中，您可以看到锁定的资源。SQL Server 获取该行中的独占锁。(RID：用于锁定堆中单个行的行标识符)同时，SQL Server 获取页中的独占锁和 TestBlock 表意向锁。
这意味着在 SQL Server 释放锁之前，任何其他进程都无法读取此资源，这是 SQL Server 中的基本锁定机制。
现在，我们将在测试表上填充一些合成数据：
TRUNCATE TABLE TestBlock DECLARE @K AS INT=0 WHILE @K <8000 BEGIN INSERT TestBlock VALUES(@K, CAST(@K AS varchar(10)) + ' Value' ) SET @K=@K+1 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 Id<5000
文章图片

在上面的查询中，SQL Server 获取每一行的独占锁。现在，我们将运行另一个查询：
BEGIN TRAN UPDATE TestBlock set Nm ='New_Value' where Id<7000
文章图片

在上面的查询中，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(v=sql.105)如何避免锁升级
防止锁升级的最简单，最安全的方法是保持事务的简短，并减少昂贵查询的锁占用空间，以便不超过锁升级阈值，有几种方法可以实现这一目标。
①将大批量操作分解为几个较小的操作
例如，在我开篇所说的在几十亿条数据中删除小姐姐的数据：
delete from `后宫佳丽` where age>18我们可以不要这么心急,一次只删除 500 个，可以显着减少每个事务累积的锁定数量并防止锁定升级。
例如：
SET ROWCOUNT 500 delete_more: delete from `后宫佳丽` where age>18 IF @@ROWCOUNT > 0 GOTO delete_more SET ROWCOUNT 0 ②创建索引使查询尽可能高效来减少查询的锁定占用空间
如果没有索引会造成表扫描可能会增加锁定升级的可能性，更可怕的是，它增加了死锁的可能性，并且通常会对并发性和性能产生负面影响。
根据查询条件创建合适的索引，最大化提升索引查找的效率，此优化的一个目标是使索引查找返回尽可能少的行，以最小化查询的的成本。
③如果其他 SPID 当前持有不兼容的表锁，则不会发生锁升级
锁定升级始总是升级成表锁，而不会升级到页面锁定。
如果另一个 SPID 持有与升级的表锁冲突的 IX(intent exclusive)锁定，则它会获取更细粒度的级别(行，key 或页面)锁定，定期进行额外的升级尝试。
表级别的 IX(intent exclusive)锁定不会锁定任何行或页面，但它仍然与升级的 S(共享)或 X(独占)TAB 锁定不兼容。
如下所示，如果有个操作始终在不到一小时内完成，您可以创建包含以下代码的 SQL，并安排在操作的前执行：
BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0WAITFOR DELAY '1:00:00'COMMIT TRAN
此查询在 mytable 上获取并保持 IX 锁定一小时，这可防止在此期间对表进行锁定升级。
Happy Ending
好了，不说了，小姐姐们因为不想离我开又打起来了(死锁)。

文章图片

作者：阿宇
编辑：陶家龙
出处：https://www.cnblogs.com/CoderAyu/
【来源：嘿丝儿科技】
【数据库|如何锁住数据库中几十亿小姐姐？】声明：转载此文是出于传递更多信息之目的。若有来源标注错误或侵犯了您的合法权益，请作者持权属证明与本网联系，我们将及时更正、删除，谢谢。邮箱地址：newmedia@xxcb.cn