Sql-Server

當我更改插入數據的方法時,源表上的鎖類型不同?

  • May 30, 2018

為什麼我在使用 SSIS 數據流任務插入目標表時在源表上遇到 IS(intent shared) 鎖,而在使用 T-SQL 語句時為同一任務插入 S(shared) 鎖?

我正在嘗試插入大約 10M 行。源上的並發插入一直在發生。因此,根據鎖兼容性矩陣,SSIS 數據流任務不會阻塞並行插入,但 T-SQL 插入會阻塞。

這是為什麼?

使用 SSIS 時,目標和源是獨立的操作。就 SQL Server 而言,它看到一個連接執行簡單的SELECT連接,而另一個連接執行批量載入或正常INSERT.

簡單SELECT可以受益於鎖定優化,在安全的情況下跳過行級共享鎖。這僅在頁面級別留下意圖共享鎖。

當 insert 和 select 在同一個 T-SQL 語句中組合時,會產生不同的執行計劃,並且不會應用特定的鎖定優化。

如果您希望在使用時在源表上產生相同的非阻塞行為,則INSERT...SELECT需要使用行版本控制隔離級別。

讀未送出隔離也不會採用共享鎖,但提供的一致性保證很少,甚至可能在修改掃描下的資料結構時拋出錯誤(錯誤 601)。

您遇到了鎖升級。每次您有超過 5000 個行或頁鎖時,MS SQL Server 都會嘗試將它們升級為表(或分區)鎖。這是因為每個鎖都需要 96 字節的記憶體。

是的,select 中的大量行也會導致您選擇的表上的鎖升級。但由於使用了頁鎖,它可能會超過 5000 行。

我建議您閱讀有關鎖升級的足夠乾淨的文章:

鎖定升級

有幾種方法可以避免鎖升級:

  1. 使用行/頁鎖數少於 5000 的“短”事務。
  2. 為特定表禁用它(自 SQL Server 2008 起)。
  3. 使用跟踪標誌1221禁用基於記憶體壓力或基於鎖數量的鎖升級。
  4. 使用跟踪標誌1224,它根據鎖的數量禁用鎖升級。

引用自:https://dba.stackexchange.com/questions/208119