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.

Answer 1

This is the answer IBM provided for the PMR, it makes sense so I'll add it as an answer:

Using isolation level RS (Db2's terminology for what java indicates as TRANSACTION_REPEATABLE_READ), we are allowed to find more qualifying rows on repeating the same query in the same transaction. The T3 scan could be halfway through table and some or all of the updated rows may have already been read (and didn't qualify as X was 0 at the time), so in this case anywhere between 0 and 3 rows could be found on the first query, although since they all committed then a subsequent scan would find all of them. We are not running in Snapshot Isolation here - things can change as we run the query depending on the isolation level, and the effects may vary depending on data location and access method.

The general feature for all-or-none when a query is running at the same time as update transaction is called snapshot isolation, which Db2 does not currently provide.

Other customers have implemented their application to rely on a single record in a control table, as indicator of the completion of the update transaction. To be more specific, below is the modified version of their application:

T1 inserts 3 rows into table DETAILS with column X=null and commits

T2 updates the 3 rows, one by one and sets column X=1, then insert into table CONTROL a new row, and commits all changes with 1 commit

T3 reads table CONTROL for the new row, if found, reads all rows in table DETAILS with X=1. This should ensure all 3 rows are returned.

(optionally the insert of single row into CONTROL can be done by T1, with T2 updating this row with value indicating the data is ready to be seen by T3)

[...]