送出後延遲鎖定？

這是一個簡化的場景，但它說明了一個現實世界的問題。最初的問題存在於一個 Java EE 應用程序中，但我在 3 個小型 Python 腳本中模仿了它。

涉及到兩個表：

CREATE TABLE SUPPLIER_STUDENT.ATOM_FEED  
(   FEED_ID BIGINT NOT NULL 
,   NEXT_FEED_ID BIGINT
,   PREV_FEED_ID BIGINT
,   FEED_XML XML 
,   FEED_XML_IS_NULL SMALLINT NOT NULL
) IN USERSPACE1 @

CREATE UNIQUE INDEX SUPPLIER_STUDENT.XPK_ATOM_FEED ON SUPPLIER_STUDENT.ATOM_FEED
   (FEED_ID) INCLUDE (NEXT_FEED_ID, PREV_FEED_ID)
CLUSTER
ALLOW REVERSE SCANS
COLLECT SAMPLED DETAILED STATISTICS @

CREATE INDEX SUPPLIER_STUDENT.X01_ATOM_FEED ON SUPPLIER_STUDENT.ATOM_FEED
  (FEED_XML_IS_NULL, NEXT_FEED_ID, PREV_FEED_ID, FEED_ID) 
ALLOW REVERSE SCANS 
COLLECT SAMPLED DETAILED STATISTICS @

CREATE TABLE SUPPLIER_STUDENT.ATOM_ENTRY  
(   ENTRY_ID CHAR(16) FOR BIT DATA NOT NULL
,   ENTRY_CONTENT_TYPE VARCHAR(100) NOT NULL
,   SUBMITTED TIMESTAMP NOT NULL
,   ENTRY_XML XML NOT NULL
,   FEED_ID BIGINT 
,   SORT_ORDER BIGINT NOT NULL
) IN USERSPACE1 @

CREATE UNIQUE INDEX SUPPLIER_STUDENT.XPK_ATOM_ENTRY ON SUPPLIER_STUDENT.ATOM_ENTRY
   (ENTRY_ID)
ALLOW REVERSE SCANS 
COLLECT SAMPLED DETAILED STATISTICS @ 

CREATE INDEX SUPPLIER_STUDENT.X01_ATOM_ENTRY ON SUPPLIER_STUDENT.ATOM_ENTRY
   (FEED_ID, ENTRY_ID)
CLUSTER
ALLOW REVERSE SCANS
COLLECT SAMPLED DETAILED STATISTICS @

ALTER TABLE SUPPLIER_STUDENT.ATOM_ENTRY ADD CONSTRAINT XPK_ATOM_ENTRY
   PRIMARY KEY (ENTRY_ID) @

ALTER TABLE SUPPLIER_STUDENT.ATOM_ENTRY ADD CONSTRAINT XAK1_ATOM_ENTRY
   UNIQUE (SORT_ORDER) @

ALTER TABLE SUPPLIER_STUDENT.ATOM_ENTRY ADD CONSTRAINT XFK_ATOM_FEED 
   FOREIGN KEY (FEED_ID)
   REFERENCES SUPPLIER_STUDENT.ATOM_FEED (FEED_ID)
       ON DELETE CASCADE
       ON UPDATE RESTRICT @

還有許多 xml 索引，例如：

CREATE INDEX SUPPLIER_STUDENT.XML01_JOB ON SUPPLIER_STUDENT.ATOM_ENTRY(ENTRY_XML)
GENERATE KEYS USING XMLPATTERN '//*:educationOrgId/text()' AS SQL VARCHAR(36) 
REJECT INVALID VALUES @

但我認為這無關緊要，所以我會把它們排除在外。

有 3 個守護程序針對這些表工作。守護程序 1 (D1) 創建 3ATOM_ENTRIES其中 feed_is 為空送出，然後休眠一段時間：

c1.execute("""insert into SUPPLIER_STUDENT.ATOM_ENTRY 
                (ENTRY_ID, ENTRY_CONTENT_TYPE, SUBMITTED ,ENTRY_XML,SORT_ORDER)
            VALUES (NYA.GET_NEW_UUID(), '', current_timestamp
                   , XMLPARSE( DOCUMENT CAST(? AS CLOB(1M)))                  
                   , next value for sort_order)""", '<dummy/>')

[ 2 more identical inserts ]

conn.commit()
time.sleep(1.0*random.randint(0, 10)/10)

守護程序 2 (D2) 讀取 feed_id 為空的條目並為其分配 feed_id：

c1.execute("""select sort_order, entry_xml 
             from SUPPLIER_STUDENT.ATOM_ENTRY 
             where feed_id is null
             order by sort_order
             fetch first %d rows only
          """ % (feed_sz))
for row in c1.fetchall():
   (sort_order, entry_xml) = row
   c2.execute("""update SUPPLIER_STUDENT.ATOM_ENTRY
                   set (feed_id, entry_xml) = (?, XMLPARSE( DOCUMENT CAST(? AS CLOB(1M))))
                 where feed_id is null
                  and sort_order = ?""", (next_feed_id, '<dummy2/>', sort_order))

conn.commit()
time.sleep(1.0*random.randint(0, 10)/10)

我很清楚直接進行更新會更聰明，但這是應用程序的設計方式，我對此無能為力。

似乎 D2 總是在每個事務中更新 3 行的倍數。

守護程序 3 (D3) 是一個讀取器，它讀取最後一個未讀取的提要的所有條目：

c1.execute("""select sort_order, XMLSERIALIZE(ENTRY_XML AS CLOB(1M)) 
             from SUPPLIER_STUDENT.ATOM_ENTRY
             where feed_id = ?
             and sort_order > ?
             order by sort_order""", feed_id, last_sort_order)
conn.commit()
time.sleep(0.01)

然後睡一會兒。

現在對於特殊的部分，大多數時候 D3 會看到 3 行的倍數，正如我所期望的那樣，但時不時地會有 1 或 2 行的剩餘部分。即 D3 沒有看到上次送出的所有行，我的解釋是對於已送出的行還有鎖，而 D3 無法讀取這些。

當 sort_order 對於讀取事件（即 1,2）是連續的時，這本身不是問題。但有時會出現間隙（即 1,3）。這意味著 2 失去了，因為 last_sort_order 現在指向 3。

如果我在 D2 中將鎖定表置於獨占模式，則問題似乎消失了（至少它不會在數小時內發生）。如果我更改 D2 中的選擇以便它讀取行以進行更新，問題仍然存在。

所有交易都是CS. 有沒有人有一個合理的解釋，為什麼讀者有時看不到 D2 中所有已送出的行？

CUR_COMMIT、DB2_SKIPDELETED、DB2_SKIPINSERTED 和 DB2_EVALUNCOMMITTED 都為 OFF。

這是 IBM 為 PMR 提供的答案，很有意義，所以我將其添加為答案：

使用隔離級別 RS（Db2 的 java 表示為 TRANSACTION_REPEATABLE_READ 的術語），我們可以在同一事務中重複同一查詢時找到更多符合條件的行。T3 掃描可能在表的中間進行，並且可能已經讀取了部分或全部更新的行（並且當時不符合 X 為 0 的條件），因此在這種情況下，可以找到 0 到 3 行之間的任何位置第一個查詢，儘管由於它們都已送出，因此後續掃描會找到所有這些。我們這裡沒有在快照隔離中執行 - 當我們執行查詢時，事情可能會根據隔離級別而改變，並且效果可能會因數據位置和訪問方法而異。

當查詢與更新事務同時執行時，all-or-none 的一般特性稱為快照隔離，而 Db2 目前不提供。

其他客戶已經將他們的應用程序實現為依賴控製表中的單個記錄，作為更新事務完成的指示器。更具體地說，以下是其應用程序的修改版本：

T1 將 3 行插入到表 DETAILS 中，列 X=null 並送出

T2 逐一更新 3 行並設置列 X=1，然後將新行插入到表 CONTROL 中，並通過 1 次送出送出所有更改

T3 為新行讀取表 CONTROL，如果找到，則讀取表 DETAILS 中 X=1 的所有行。這應該確保返回所有 3 行。

（可選地，將單行插入到 CONTROL 可以由 T1 完成，T2 使用指示數據已準備好被 T3 看到的值更新此行）

$$ … $$

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