刪除大量資料造成Lock escalation鎖定升級 SQL Server
情況:
在MS-SQL1.開一視窗Begin Transaction，delete from tableA (一萬多筆資料)
2.開另一視窗 insert into TableA () values()
發現步驟2怎麼都不會成功，就一直在執行
於是我將
步驟1刪除的資料縮小範圍，變成幾百筆，步驟2就馬上插入成功了!
於是，我上網問原來是因為Lock escalation(鎖升級)所造成的!
當在一個交易中(還未提交或rollback)，操作了大量資料，應用程式會取得無數個row lock
，當row lock的數量超過了特定的值，為了減少locklist的負載，將row lock升級成table lock，
也就是整個表都被鎖起來了!導致我步驟2無法成功執行(另外一個連接)。
因為SQL Server Management在刪除大量資料時，是會全部找出來一起刪除，並不是一次一次刪除
為了避免鎖定升級的情況，我們可以採取分批次來刪除資料
批次刪除語法:
/**每次刪除1000筆表A的資料**/DoItAgain:
DELETE TOP (1000)
FROM TableA
IF @@ROWCOUNT > 0
GOTO DoItAgain
_________________________________________________________
後來我還是想不通，我慢慢地把TOP的筆數提高，測試他的極限，發現7000又發生鎖定升級
DoItAgain:
DELETE TOP (7000)
FROM TableA
IF @@ROWCOUNT > 0
GOTO DoItAgain
____________________________________________________________
如何觀察鎖定升級(Lock escalation)?
1. SQL Management studio選取Tool->SQL Server Profiler
2.連接對應的DB
3.如下圖選擇
之後你開著追蹤視窗，邊下達指令來實驗，當鎖定升級的情況發生，會看到如下紀錄:
___________________________________________________________
_____________________________________________________________________
When the lock escalation happens in MSSQL Server
"The locks option also affects when lock escalation occurs. When locks is set to 0, lock escalation occurs when the memory used by the current lock structures reaches 40 percent of the Database Engine memory pool. When locks is not set to 0, lock escalation occurs when the number of locks reaches 40 percent of the value specified for locks."_____________________________________________________________________
How to set Using SQL Server Management Studio that will impact lock escalation:
To configure the locks option In Object Explorer, right-click a server and select Properties .
Click the Advanced node.
Under Parallelism , type the desired value for the locks option.
Use the locks option to set the maximum number of available locks, such limiting the amount of memory SQL Server uses for them.
__________________________________________________________
結論:
1.當鎖定升級發生時，會造成timeout問題，此時要審視是否有sql語句造成問題，
1.當鎖定升級發生時，會造成timeout問題，此時要審視是否有sql語句造成問題，
2.如果在一個交易中，刪除大量資料時，請用批次刪除法，請不要一次刪除大量資料，造成鎖定升級。
以下是我的提問:
留言
張貼留言