Sql Server - 在另一個事務上插入時選擇會產生意外結果

我偶然發現這種情況從根本上改變了我對事務和鎖定的了解（雖然我不太了解），我需要幫助來理解它。

假設我有一張這樣的桌子：

CREATE TABLE [dbo].[SomeTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SomeData] [varchar](200) NOT NULL,
[Moment] [datetime] NOT NULL,
[SomeInt] [bigint] NOT NULL
) ON [PRIMARY]

我執行這個“在事務中插入 1000 行”查詢：

BEGIN TRAN t1

DECLARE @i INT = 0

WHILE @i < 1000
BEGIN
   SET @i = @i + 1

   INSERT INTO [SomeTable] ([SomeData] ,Moment, SomeInt)
   VALUES (CONVERT(VARCHAR(255), NEWID()), getdate(), @i)

   WAITFOR DELAY '00:00:00:010'
END

COMMIT TRAN t1

在此事務執行時，我正在執行一個簡單的選擇：

SELECT Id, Moment, SomeData, SomeInt FROM [SomeTable]

並非總是可以重現它（顯然取決於時間），但有時選擇查詢會在插入事務完成後返回少於 1000 行。在我的無知中，我相信 select 總是會返回 1000 行（假設隔離級別是 Read Committed），但顯然我誤解了事務和鎖定的工作方式。

但是，如果我在 Id 列上放置一個主鍵（它會生成一個聚集索引），那麼只要我嘗試過，選擇查詢就會返回所有 1000 行。以其他方式放置索引，在復合鍵上使用聚集索引，在其他一些列上使用非聚集索引，可能會再次導致返回的行數比我預期的要少。

所以，我有這些問題：

1. 為什麼 select 並不總是返回事務送出的所有行？
2. 如果這是預期的行為，那麼真正讓它按我預期工作的最佳方法是什麼？基本上，我想選擇在事務之後（或之前）返回表的狀態，而不是一些半完成的數據。快照隔離目前不是一種選擇。放 TABLOCK 似乎在做這項工作，但有更好的解決方案嗎？在現實生活中，如果不是絕對必要的話，我不想鎖定在這個級別上的表格。
3. 為什麼放置索引會改變這種行為？

提前致謝。

執行您的程式碼幾次後，我還沒有設法重現這一點。

我認為它必須在後面的行被插入到文件中的較早頁面時發生。

所以操作的順序是（例如）

• 在第 200、207、223 頁上插入堆的行
• Select 語句啟動並執行分配有序掃描。發現第一頁是 200 並且被阻塞等待行鎖被釋放。
• 其他行由第一個事務插入。其中一些分配在 200 之前的頁面上。插入事務送出。
• 行鎖被釋放並繼續分配有序掃描。文件中較早的行被遺漏。

該表由 10 頁組成。預設情況下，前 8 個頁面將從混合範圍分配，然後將分配一個統一範圍。也許在您的情況下，文件中的空間在使用的混合範圍之前可用於免費的統一範圍。

重現問題後，您可以通過在不同的視窗中執行以下命令來測試該理論，並查看原始缺失的行是否SELECT都出現在此結果集的開頭。

SELECT [SomeData],
      Moment,
      SomeInt,
      file_id,
      page_id,
      slot_id
FROM   [SomeTable] 
/*Undocumented - Use at own risk*/
CROSS APPLY sys.fn_PhysLocCracker(%% physloc %%)
ORDER BY page_id, SomeInt

針對索引表的操作將按照索引鍵順序而不是分配順序，因此不會受到這種特定情況的影響。

可以針對索引執行分配排序掃描，但僅在表足夠大並且隔離級別未送出或持有表鎖時才考慮。

因為讀取送出通常會在讀取數據後立即釋放鎖，因此可以對索引進行掃描以讀取行兩次或根本不讀取（如果索引鍵由並發事務更新導致行向前或向後移動) 有關此類問題的更多討論，請參閱Read Committed Isolation Level 。

---

順便說一句，我最初設想索引的情況是索引位於相對於插入順序增加的列之一（Id、Moment、SomeInt 中的任何一個）。但是，即使聚集索引是隨機SomeData的，問題仍然不會出現。

我試過了

DBCC TRACEON(3604, 1200, -1) /*Caution. Global trace flag. Outputs lock info
                              on every connection*/

SELECT TOP 2 *,
            %%LOCKRES%%
FROM   [SomeTable] WITH(nolock)
ORDER BY [SomeData];

SELECT *,
      %%LOCKRES%%
FROM   [SomeTable]
ORDER BY [SomeData];

/*Turn off trace flags. Doesn't check whether or not they were on already 
 before we started, with TRACEOFF*/
DBCC TRACEOFF(3604, 1200, -1)

結果如下

第二個結果集包括所有 1,000 行。鎖定資訊表明，即使在24c910701749釋放鎖時它被阻止等待鎖資源，它也不會從該點繼續掃描。相反，它會立即釋放該鎖並在新的第一行上獲取行鎖。

引用自：https://dba.stackexchange.com/questions/75848