需要读取数据的其他用户需要长时间运行的事务

删除 来自HoldingTable WHERE PrimaryKey IN（选择前20％主键 来自按键，按主键排序） ---暂停10秒 等待延迟'00：00：10'' ---前2000记录其余8000条记录 INSERT INTO DestinationTable SELECT TOP 25 PERCENT Cols 来自SourceTable JOIN HoldingTable ON a = b ORDER BY HoldingTable PrimaryKey DELETE FROM HoldingTable WHERE PrimaryKey IN（SELECT TOP 25 PERCENT PrimaryKey FROM HoldingTable ORDER BY Primary KEY） WAITFOR DELAY' '00：00：10'' ..... ---剩余记录 INSERT INTO DestinationTable SELECT TOP 100 PERCENT Cols 来自SourceTable JOIN HoldingTable ON a = b ORDER BY HoldingTable PrimaryKey drop HoldingTable - 假设它是临时表或表变量 COMMIT TRANSACTION 类似的概念应该适用于UPDATES。 可能还有其他解决方案;这对我有用。 I''ve cheated with large INSERT statements by inserting blocks of themin a batch inside a transaction, with a WAITFOR DELAY of a few seconds.This makes the individual transactions shorter, and allows the otherstatements to sneak in and get some work done while doing it. I''vefound that inserting 5 batches of of increasing percentage sizes (20,25, 30, 50, 100) usually ends up inserting near equivelent batch sizes. The psuedo-code for this method would be something like the following: BEGIN TRANSACTION INSERT INTO holdingTableSELECT PrimaryKeyFROM Table-- first 2000 records of 10000 recordsINSERT INTO DestinationTableSELECT TOP 20 PERCENT ColsFROM SourceTable JOIN HoldingTable ON a=bORDER BY HoldingTable PrimaryKey DELETEFROM HoldingTableWHERE PrimaryKey IN (SELECT TOP 20 PERCENT PrimaryKeyFROM HoldingTable ORDER BY Primary KEY) ---pause for 10 secondsWAITFOR DELAY ''00:00:10''---first 2000 records of remaining 8000 recordsINSERT INTO DestinationTableSELECT TOP 25 PERCENT ColsFROM SourceTable JOIN HoldingTable ON a=bORDER BY HoldingTable PrimaryKey DELETEFROM HoldingTableWHERE PrimaryKey IN (SELECT TOP 25 PERCENT PrimaryKeyFROM HoldingTable ORDER BY Primary KEY) WAITFOR DELAY ''00:00:10'' ..... ---remaining recordsINSERT INTO DestinationTableSELECT TOP 100 PERCENT ColsFROM SourceTable JOIN HoldingTable ON a=bORDER BY HoldingTable PrimaryKeydrop HoldingTable --assumes it''s a temp table or table variable COMMIT TRANSACTION A similar concept should work for UPDATES. There may be other solutions out there; this works for me. 这篇关于需要读取数据的其他用户需要长时间运行的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！