Using -rereadnolock with ROWIDs

Posted by Jeff Ledbetter on 24-May-2018 14:39

Hi.

We are trying to understand this kbase:

https://knowledgebase.progress.com/articles/Article/000027672-rereadnolock-does-not-always-cause-a-NO-LOCK-records-to-be-re-read-from-the-database

Specifically:

"There are several contexts where the -rereadnolock startup parameter has no effect on NO-LOCK record reads.  In the following cases, the -rereadnolock startup parameter will not cause the record to be re-read from the database:

1.  The -rereadnolock parameter has no affect on records that are being retrieved via RECID or ROWID.

In this case, Progress will not re-read the record. It will use the copy of the record already stored in the buffer. If the most current version of the record is needed instead, then use the RELEASE statement on all buffers that contain a copy of the record before reading the record, or use FIND CURRENT or GET CURRENT statements to re-read the record."

Does that mean that if I have a session using -rereadnolock and call the code below giving it customer ROWID, there is a chance that it may get the record from the ICB instead of the database?

PROCEDURE doIt:

  DEFINE INPUT  PARAMETER prCust AS ROWID NO-UNDO.

  DEFINE BUFFER bCust FOR customer.

  FIND bCust NO-LOCK
    WHERE ROWID(bCust) = prCust.

  /* do something */

END PROCEDURE.

All Replies

Posted by Laura Stern on 24-May-2018 16:47

-rereadnolock does not affect records read by ROWID/RECID, but you WILL get the new copy of the record anyway in that case.  So you don't need -rereadnolock for those because that is what you get by default.

Posted by Tim Kuehn on 24-May-2018 17:13

Are you saying this is a doc bug?

Posted by Laura Stern on 24-May-2018 17:54

Sounds that way to me.  

On the other hand it could be a bug in the product.  I am simply relating how the product behaves.  But from looking at the code it doesn’t look as if we are doing anything explicitly different for ROWID/RECID reads based on the -rereadnolock parameter.  It just never goes through the code that checks that parameter.  It reads the record via a different routine that will always do the re-read.  

Also I was the one who implemented the -rereadnolock parameter and I don’t remember any such rule as stated by the doc.  But that was about 20+ years ago, so that’s not very reliable!  

Posted by George Potemkin on 25-May-2018 00:51

FIND bCust NO-LOCK
    WHERE ROWID(bCust) = prCust.

When the record is already located in the ICB structure (in the client record pool - the -l parameter) then FIND BY ROWID statement does not even create a db request and _UserIO._UserIO-DbAccess is not incremented. No matter if the -rereadnolock is used or not.

Posted by Jeff Ledbetter on 25-May-2018 06:28

Thanks.

[quote user="George Potemkin"]

1
2
FIND bCust NO-LOCK
    WHERE ROWID(bCust) = prCust.

When the record is already located in the ICB structure (in the client record pool - the -l parameter) then FIND BY ROWID statement does not even create a db request and _UserIO._UserIO-DbAccess is not incremented. No matter if the -rereadnolock is used or not.[/quote]

What is the correct technique to ensure that the latest record is always gotten from the DB? Immediately follow it with a FIND CURRENT?

We are trying to debug a situation (that we cannot readily duplicate) where it appears that a record that has been updated in one procedure is not re-fetched when the ROWID is passed to another procedure and a FIND is executed.

Posted by Laura Stern on 25-May-2018 07:47

Yes, use FIND CURRENT.

Posted by Jeff Ledbetter on 25-May-2018 07:58

[quote user="Laura Stern"]

Yes, use FIND CURRENT.

[/quote]
Ok, just to be clear as possible.
The "proper" way to ensure that you always have the latest record when doing a FIND NO-LOCK is to:
FIND bCust WHERE ROWID(bCust) = prCust NO-LOCK.
FIND CURRENT bCust NO-LOCK.
Is that correct?
The "issue" we are seeing only seems to happen rarely, so the code without the FIND CURRENT works most of the time (and I am not even sure if that is the resolution yet). I would love to be able to explain why though...
Thanks.

Posted by George Potemkin on 25-May-2018 08:00

Try:

DEFINE VARIABLE vDbAccess LIKE _UserIO._UserIO-DbAccess NO-UNDO EXTENT 2.

FUNCTION MyDbAccess RETURNS INT64:
  FOR FIRST DICTDB._MyConnection NO-LOCK,
      FIRST DICTDB._UserIO NO-LOCK
      WHERE DICTDB._UserIO._UserIO-Id EQ DICTDB._MyConnection._MyConn-UserId + 1:
   RETURN DICTDB._UserIO._UserIO-DbAccess.
  END.
END FUNCTION.

PROCEDURE doIt:
 
  DEFINE INPUT  PARAMETER prCust AS ROWID NO-UNDO.
 
  DEFINE BUFFER bCust FOR customer.
 
  ASSIGN vDbAccess[1] = MyDbAccess().
  FIND bCust NO-LOCK
    WHERE ROWID(bCust) = prCust.
  ASSIGN vDbAccess[2] = MyDbAccess().
  MESSAGE "FIND BY ROWID:" vDbAccess[1] NE vDbAccess[2] VIEW-AS ALERT-BOX.
 
  ASSIGN vDbAccess[1] = MyDbAccess().
  FIND CURRENT bCust NO-LOCK.
  ASSIGN vDbAccess[2] = MyDbAccess().
  MESSAGE "FIND CURRENT bCust:" vDbAccess[1] NE vDbAccess[2] VIEW-AS ALERT-BOX.
 
END PROCEDURE.

ASSIGN vDbAccess[1] = MyDbAccess().
FIND FIRST customer NO-LOCK.
ASSIGN vDbAccess[2] = MyDbAccess().
MESSAGE "FIND FIRST customer:" vDbAccess[1] NE vDbAccess[2] VIEW-AS ALERT-BOX.
RUN doIt(ROWID(customer)).

Posted by Jeff Ledbetter on 25-May-2018 08:31

Well that is certainly interesting. Thank you.

Posted by James Palmer on 03-Jun-2018 23:51

In my opinion this is quite a dangerous inconsistency. I've raised it before, and in fact I think I've raised an enhancement request. In my previous job we had quite a few cases where we used find by rowid to refind a record. The person who wrote the logic in the first place had read about -rereadnolock, but not about this limitation. As a result there were a number of reported instances where the logic resulted in old data being written back to the database when 2 processes updated the same record in succession.

I've found the enhancement: community.progress.com/.../-rereadnolock_to_be_honoured_by_rowid_finds

Posted by George Potemkin on 04-Jun-2018 01:21

I saw the legacy code that passes ROWID as a parameter instead of PARAMETER BUFFER. So the procedures set their own local buffers pointed out to an existent record in client's record pool - without db access. Fix can break the logic of such legacy code and it will, of course, increase db accesses.

Posted by gus bjorklund on 16-Jun-2018 11:16

there are two different mechanisms at work here.

when you do a query such as a for each or a find and a record is retrieved, then we look to see if there is a copy of the same record in the buffer’s ICB structure. “same” is defined as matching row id and table. if there is a match then we have to decide which copy of the record should be kept.

the original logic was that if the lock strengths are different, then the higher one is kept. if the same, then the older copy is kept. this was done because it increased read consistency a bit.

because times changed, people wanted the new logic which was to take the new record instead of the older one.

the other mechanism is used for find by rowid. in that case, before fetching the record, we check the buffer to see if it contains a matching rowid with the same or higher lock strength. if so, then the record is not fetched again from the database.

This thread is closed