delayed locks after commit?

Question

This is a simplified scenario, but it illustrates a real world problem. The original problem resides in a Java EE application, but I have mimicked it in 3 small python scripts.

There are two tables involved:

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 @

There are also a number of xml indexes like:

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 @

but I don't think that's relevant so I'll leave them out of it.

There are 3 daemons working against these tables. Daemon 1 (D1) creates 3 ATOM_ENTRIES where feed_is is null commits and then sleeps for a while:

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)

Daemon 2 (D2) reads entries where feed_id is null and assigns them a 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)

I'm well aware that it would be much smarter to do the update directly, but this is the way the application is designed and I can not do anything about that.

It appears as if D2 always updates a multiple of 3 rows in each transaction.

Daemon 3 (D3) is a reader that reads all entries for the last feed that is not read:

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)

and then sleeps for a while.

Now for the peculiar part, most of the time D3 sees a multiple of 3 rows as I would expect, but every now and then there is a remainder of 1 or 2 rows. I.e. D3 does not see all rows from the last commit, my interpretation is that there are locks remaining for rows that are committed and that D3 cant read those.

This is not a problem per se when sort_order is consecutive for the read events (i.e. 1,2). But on occasion there is a gap (i.e. 1,3). This means that 2 is lost since last_sort_order now points to 3.

If I put a lock table in exclusive mode in D2 the problem seems to disappear (at least it does not occur within hours). If I change the select in D2 so that it read rows for update the problem remains.

All transactions are CS. Does anyone have a reasonable explanation why the reader on occasion does not see all committed rows from D2?

CUR_COMMIT, DB2_SKIPDELETED, DB2_SKIPINSERTED and DB2_EVALUNCOMMITTED are all OFF.