[SQL Server].NET SqlBulkCopy與Tabe Lock

2016-01-29

4783
0
SQL Server

為減少資料爭用避免擴大鎖定，我們常調整T-SQL或預存程序使用小量分批更新或寫入。
常用SqlBulkCopy來提升.NET大量資料寫入效能(真的很快)，那麼SqlBulkCopy會不會造成資料表鎖定(Tabe Lock)?
(1)會 (2)不會 (3)不一定

(1)我們先建立一個資料表，用C1欄位用識別值IDENTITY並作為PK，使用叢集索引。

--建立資料表
CREATE TABLE TblBulkCopy
(
C1 INT IDENTITY,
C2 VARCHAR(20),
C3 DATETIME DEFAULT GETDATE(), 
C4 CHAR(4000) DEFAULT '占空間'
)
--建立主鍵
ALTER TABLE TblBulkCopy
ADD CONSTRAINT PK_TblBulkCopy PRIMARY KEY CLUSTERED(C1)

(2)模擬線上交易的資料行為，這邊假設每0.5秒打1筆交易進來(C2=SSMS)。

DECLARE @I INT = 0;
WHILE @I < 100
BEGIN
  WAITFOR DELAY  '00:00:00.500'
  INSERT INTO TblBulkCopy (C2) VALUES('FromSSMS') 
END

(3)寫一段SqlBulkCopy，假設寫入10萬筆交易(C2=SqlBulkCopy)。

//(0)取得來源資料
DataTable dt = BuildDataTable();
//(1)BulkCopy寫入資料
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["CN"].ConnectionString,
SqlBulkCopyOptions.KeepIdentity))
{
    bulkcopy.BulkCopyTimeout = 1200;
    bulkcopy.DestinationTableName = "TblBulkCopy";
    bulkcopy.BatchSize = 2500;
    bulkcopy.ColumnMappings.Add("C2", "C2");
    bulkcopy.ColumnMappings.Add("C4", "C4");
    bulkcopy.WriteToServer(dt);
    bulkcopy.Close();
}

10萬筆資料源:

public DataTable BuildDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("C2", typeof(string));
    dt.Columns.Add("C4", typeof(string));
    for (int i = 0; i < 100000; i++)
    {
        int j = i + 1;
        dt.Rows.Add(new object[] { "SqlBulkCopy", i.ToString().PadLeft(100, '0') });
    }
    return dt;
}

實驗的劇本有2種

A.SqlBulkCopy設定Batch Size=2500，自動每批2500筆。
B.SqlBulkCopy不設定Batch Size，預設是0，代表10萬筆一口氣寫入。

A.SqlBulkCopy設定Batch Size，每批2500筆
10萬筆寫入的時間約花8秒，觀察SSMS寫入的序號及時間還是很連續(每秒2筆)，並沒有受到批次寫入影響，看起來沒有引發擴大鎖定，
另外也從MSDN上找到SqlBulkCopyOptions.TableLock說明，預設不開啟，使用資料列鎖定。

B.SqlBulkCopy不設定Batch Size，預設是0，代表10萬筆一口氣寫入。
10萬筆寫入的時間約花4秒，觀察到SSMS寫入的序號及時間不再連續，受到批次寫入影響而暫時封鎖(Block)，SSMS序號11過後就是100012，等待整批10萬筆寫入後才繼續，看起來引發了擴大鎖定。