為什麼在我的場景中發生表鎖升級

我有一個表 Table1 使用以下查詢以小塊更新：

update top (1000) Table1
   set VarcharColumn1 = 'SomeValue'
from Table1
where ID in (select ID from Table2)
     and VarcharColumn1 is NULL

額外細節：

Table2有90000行，Table1中需要更新的總行數也是90000（1對1的關係）

此外，當 Table1 行更新時，Table1 上有一個觸發器，它將更新前的行插入到表 Table1History

所以這意味著當我更新 Table1 中的 1000 行時，1000 行插入到 Table1History

問題：

當我更新 top100 rows時，不會發生表鎖升級

我使用Extended Events "lock_escalation" event, 以及在Performance Monitor - SQLServer:Access Methods - Table Lock Escalations / sec

當我更新前 1000 或 500 行時，Table1 上發生了表鎖升級

So I wonder, what is the mechanism or formula that is used by SQL Server     
to escalate locking to table level ?  

在Google它說 5000 行是門檻值，但顯然在我的情況下 1000 或 500 行足以導致 Table1 上的表鎖升級

鎖升級並不完全基於 5k 行，而是一些鎖 pr。鎖定層次結構中的級別。一旦達到門檻值，鎖就會升級到下一個對象級別，直到最終達到表級別，這通常會對應用程序產生負面影響。要最簡單地解決這個問題，請考慮您在 DELETE TOP() 中影響了多少行以及與之相關的頁面數。頁面中的行數由行的寬度定義。如果您有很多列（需要大量字節儲存），那麼您的行寬會更大，那麼列數會更少。一個頁面可以包含大約 8k 字節的數據。

“線上圖書指出，當語句在一個對象的單個實例上獲得超過 5,000 個行或頁面級鎖時，達到鎖升級的門檻值。此外，一些部落格（我在 Internet 上遇到）只需要 5000 個值作為門檻值，不考慮其他參數。當行數超過 5000 時，嘗試將對像上的更高級別鎖（IS 或 IX 鎖）轉換為表上的 S 鎖或 X 鎖。如果失敗，可能是因為該表正被其他人使用，如果 SQL Server 在對象的同一實例上獲得另外 1,250 個鎖，SQL Server 將退出並重試。請記住，獲得的所有鎖必須來自同一個對象有資格超過鎖定門檻值的語句，因此來自自聯接的 tableA 的 4,000 個鎖定（即，另一組 4,000 把鎖）將不符合條件。”

$$ taken from book “Professional SQL Server 2008 Internals and Troubleshooting” by Christian Bolton et al. published by Wrox $$

SQL Server 中頁面對像中的好資訊： https ://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

作為表定義的一部分，有一個選項可以禁用鎖升級。但在大多數情況下不建議這樣做。在您嘗試設計時考慮到鎖升級，我建議您不要走這條路。在此處查看更多資訊：在 SQL Server 2016 中禁用鎖升級的缺點是什麼

引用自：https://dba.stackexchange.com/questions/245306