我试图插入1,500,000记录到表中。我在插入期间面临表锁问题。所以我想出了下面的批量插入。
DECLARE @BatchSize INT = 50000
WHILE 1 = 1
BEGIN
INSERT INTO [dbo].[Destination]
(proj_details_sid,
period_sid,
sales,
units)
SELECT TOP(@BatchSize) s.proj_details_sid,
s.period_sid,
s.sales,
s.units
FROM [dbo].[SOURCE] s
WHERE NOT EXISTS (SELECT 1
FROM dbo.Destination d
WHERE d.proj_details_sid = s.proj_details_sid
AND d.period_sid = s.period_sid)
IF @@ROWCOUNT < @BatchSize
BREAK
END
我在Destination表(proj_details_sid ,period_sid )上有一个聚集索引。NOT EXISTS部分只是限制已插入的记录再次插入到表
我做得对吗，这会避免表锁吗?或者有更好的方法。
注意:使用批量插入和不使用批量插入所花费的时间基本相同
锁升级不太可能与语句的SELECT部分相关。
插入大量行是很自然的结果
当没有使用ALTER table SET LOCK_ESCALATION选项在表上禁用锁升级，并且存在以下条件之一时，会触发锁升级:
- 单个Transact-SQL语句在单个非分区表或索引上至少获得5,000个锁。
- 单个Transact-SQL语句在一个分区表的单个分区上至少获得5,000个锁，并且ALTER table SET LOCK_ESCALATION选项被设置为AUTO。
- 数据库引擎实例中的锁数量超过内存或配置阈值。
如果由于锁冲突而无法升级锁，数据库引擎会在每获得1,250个新锁时周期性地触发锁升级。
您可以通过在Profiler中跟踪锁升级事件或简单地用不同的批处理大小尝试下面的操作来轻松地自己看到这一点。对于我来说，TOP (6228)显示持有6250个锁，但TOP (6229)在锁升级开始时突然暴跌到1。确切的数字可能会有所不同(取决于数据库设置和当前可用的资源)。使用试错法找到为您显示锁升级的阈值。
CREATE TABLE [dbo].[Destination]
(
proj_details_sid INT,
period_sid INT,
sales INT,
units INT
)
BEGIN TRAN --So locks are held for us to count in the next statement
INSERT INTO [dbo].[Destination]
SELECT TOP (6229) 1,
1,
1,
1
FROM master..spt_values v1,
master..spt_values v2
SELECT COUNT(*)
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
COMMIT
DROP TABLE [dbo].[Destination]
您正在插入50,000行，因此几乎肯定会尝试锁升级。
如何解决SQL Server中锁升级引起的阻塞问题这篇文章已经很老了，但是很多建议仍然有效。
- 将大批量操作分解为几个较小的操作(即使用较小的批量大小)
- 如果不同的SPID当前持有不兼容的表锁，则不会发生锁升级-他们给出的例子是执行 的不同会话
BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN
另一个选择是ALTER TABLE blah SET (LOCK_ESCALATION = DISABLE)，但这仍然不是很有针对性，因为它影响对表的所有查询，而不仅仅是您这里的单个场景。
所以我会选择选项1或选项2，并将其他选项贴现。
与其检查Destination中的数据是否存在，不如先将所有数据存储在临时表中，然后批量插入到Destination
DECLARE @batch int = 100
DECLARE @curRecord int = 1
DECLARE @maxRecord int
-- remove (nolock) if you don't want to have dirty read
SELECT row_number over (order by s.proj_details_sid, s.period_sid) as rownum,
s.proj_details_sid,
s.period_sid,
s.sales,
s.units
INTO #Temp
FROM [dbo].[SOURCE] s WITH (NOLOCK)
WHERE NOT EXISTS (SELECT 1
FROM dbo.Destination d WITH (NOLOCK)
WHERE d.proj_details_sid = s.proj_details_sid
AND d.period_sid = s.period_sid)
-- change this maxRecord if you want to limit the records to insert
SELECT @maxRecord = count(1) from #Temp
WHILE @maxRecord >= @curRecord
BEGIN
INSERT INTO [dbo].[Destination]
(proj_details_sid,
period_sid,
sales,
units)
SELECT proj_details_sid, period_sid, sales, units
FROM #Temp
WHERE rownum >= @curRecord and rownum < @curRecord + @batch
SET @curRecord = @curRecord + @batch
END
DROP TABLE #Temp
我添加了(NOLOCK)您的目标表-> dbo.Destination(NOLOCK)。现在，你不会锁定你的表。
WHILE 1 = 1
BEGIN
INSERT INTO [dbo].[Destination]
(proj_details_sid,
period_sid,
sales,
units)
SELECT TOP(@BatchSize) s.proj_details_sid,
s.period_sid,
s.sales,
s.units
FROM [dbo].[SOURCE] s
WHERE NOT EXISTS (SELECT 1
FROM dbo.Destination(NOLOCK) d
WHERE d.proj_details_sid = s.proj_details_sid
AND d.period_sid = s.period_sid)
IF @@ROWCOUNT < @BatchSize
BREAK
END
可以在select语句中使用WITH (NOLOCK)。