Sql-Server
當我更改插入數據的方法時,源表上的鎖類型不同?
為什麼我在使用 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 行。
我建議您閱讀有關鎖升級的足夠乾淨的文章:
有幾種方法可以避免鎖升級:
- 使用行/頁鎖數少於 5000 的“短”事務。
- 為特定表禁用它(自 SQL Server 2008 起)。
- 使用跟踪標誌1221禁用基於記憶體壓力或基於鎖數量的鎖升級。
- 使用跟踪標誌1224,它根據鎖的數量禁用鎖升級。