Question Life after Death (DELETE) - is it a bug or miracle?

[George Potemkin]

The session connects remotely to the database, deletes some records, commits the transaction after each record deletion, and finally re-reads some of the recently deleted records. The size of the records matters!

Explanation of “miracle”: if we don’t use NO-PREFETCH option then Progress fetches several records at a time and sends them to the client in one network message. Client stores the received messages in its memory while processing the records one by one. Documentation does not explain how many network messages can be cached by client. In ancient times there was the -Nb parameters called “Network Buffers” but most likely it’s obsolete now. As we know the size of the messages is defined by the -Mm parameter.

The code below deletes each records followed the records with the even keys. In other words, it deletes the records that are already stored in a current network message and that is not yet processed by the client. Obviously the client can’t re-read the deleted records using any locks other than NO-LOCK.

It’s happened with every sixth record. Let’s create only 6 records and check tabanalys:

                     -Record Size (B)-          ---Fragments--- Scatter
Table          Records    Size   Min   Max  Mean   Count Factor  Factor
PUB.theTable         6   15.8K  2704  2704  2704       6    1.0     2.3

The total size of the records is 15.8K. The tests were run with the -Mm 16K. Some number of bytes in network message is taken up by the service information. I guess the client got two network messages. If I would decrease a record size just by one byte the session will not read the deleted records. NO-PREFETCH option also solves the issue.

What do you think? Is it a Progress bug?

Result:

Key Can lock exclusively
  1 yes
  3 yes
  5 yes
  6 no
  7 yes
  9 yes
 11 yes
 13 yes
 15 yes
 16 no
 17 yes
 19 yes
 21 yes
 23 yes
 25 yes
 26 no

Procedure to reproduce the issue (note: "MeatSize"=2683+ is for -Mm 16384):

RUN CreateRecords(26, 2683).
RUN DeleteRecords(2).

/* ------------------------------------------------------------------------- */
PROCEDURE CreateRecords:

  DEFINE INPUT PARAMETER ipRecords  AS INTEGER NO-UNDO.
  DEFINE INPUT PARAMETER ipMeatSize AS INTEGER NO-UNDO.
 
  DEFINE VARIABLE vMeat AS CHARACTER NO-UNDO.
  DEFINE VARIABLE vKey  AS INTEGER   NO-UNDO.
 
  FOR EACH theTable EXCLUSIVE-LOCK:
    DELETE theTable.
  END. /* theTable */

  ASSIGN vMeat = FILL("x", ipMeatSize).

  DO vKey = 1 TO ipRecords
  TRANSACTION:
    CREATE theTable.
    ASSIGN theTable.TableKey  = vKey
           theTable.TableMeat = vMeat
    . /* ASSIGN */
  END. /* TRANSACTION */
END PROCEDURE. /* CreateRecords */

/* ------------------------------------------------------------------------- */
PROCEDURE DeleteRecords:

  DEFINE INPUT PARAMETER ipBaseKey AS INTEGER NO-UNDO.
 
  DEFINE BUFFER bufTable FOR theTable.

  DEFINE VARIABLE vKey   AS INTEGER NO-UNDO.
  DEFINE VARIABLE vRowid AS ROWID   NO-UNDO.

  FOR EACH theTable NO-LOCK:
    DISPLAY theTable.TableKey LABEL "Key".
  
    ASSIGN vRowid = ROWID(theTable)
           vKey   = theTable.TableKey
    . /* ASSIGN */
  
    DO TRANSACTION:
      FIND FIRST bufTable EXCLUSIVE-LOCK WHERE ROWID(bufTable) EQ vRowid
      NO-WAIT NO-ERROR.
    
      DISPLAY AVAILABLE(bufTable).
  
      IF AVAILABLE(bufTable) AND theTable.TableKey MODULO ipBaseKey EQ 1 THEN
      FOR FIRST bufTable EXCLUSIVE-LOCK
          WHERE bufTable.TableKey EQ vKey + 1:
        DELETE  bufTable.
      END. /* bufTable */
    END. /* TRANSACTION */
  END. /* theTable */
END PROCEDURE. /* DeleteRecords */

DF file:

ADD TABLE "theTable"
  AREA "Data Area"
  DUMP-NAME "theTable"

ADD FIELD "TableKey" OF "theTable" AS integer
  FORMAT ">>9"
  INITIAL "0"
  LABEL "Key"
  MAX-WIDTH 4
  ORDER 10

ADD FIELD "TableMeat" OF "theTable" AS character
  FORMAT "x(40)"
  INITIAL ""
  LABEL "Meat"
  MAX-WIDTH 16384
  ORDER 20

ADD INDEX "key" ON "theTable"
  AREA "Idx Area"
  PRIMARY
  INDEX-FIELD "TableKey" ASCENDING

.
PSC
cpstream=undefined
.
0000000427