Some of my data using RECID pointers randomly went missing. Regret to say that I do not have the database log (.lg ) file for that moment when this incident occurred.
HDD does not have any bad sectors. Database was not touched either. Have done an extensive checking on our application code and highly confident that was not due to application code since the application have been running for number of years with no such issue before.
Kindly advise what could be the cause of this?
You know that storing RECID’s and ROWID’s as referened keys in the DB is a bad idea?
Did you dump and load your DB recently? That changes RECID’s and ROWID’d. Or delete and recreate a record.
Physical storage reorganization like a TBLMOVE from one are to another also changes RECID and ROWID.
Architect of the SmartComponent Library and WinKit
Mike understates the case -- "bad idea" doesn't even begin to cover it.
Never, ever store a RECID or a ROWID in a database table. Treat RECIDs as very volatile data -- they *might* be consistent from moment to moment during program execution. You will mostly, probably "get away" with using RECID *variables* (variables -- not db fields) within a narrow bit of code -- but even that will not *always* be safe.
Agree. In my previous job they had one table using RECID in db fields for referring to another table, and they forgot it when we performed a Dump & Load.
The boss sent me to repair the disaster, so I'll never forget how bad is the idea.
In addition to the fact that rowids can change as mentioned above by mike and tom, recid and rowid values are not nearly as unique as you might have been led to believe (and the documentation does not say much about it). long ago, in a galaxy far away, a 32-bit recid was always unique within a database. that has not been true since 1998. to be specific
rows in two different tables can have the same rowid values.
rows residing in different partitions of a single table can have the same rowid value.
rows belonging to different tenants in the same multitenant table can have the same rowid value.
Gus - is what you wrote another way of saying "rows in different areas can have the same ROWID"?
One big "OMG" though is "rows in different partitions of the same table can have the same ROWID".That's a big issue for any program which use ROWID to do things like find the same record as another buffer has only with a different locking status and the like.
Partitions of a table are storage objects. Two table partitions *can* be in different areas, but they don't have to be.
lock id's aren't the same as rowids. buffers have a lot of internal information in them along with the record itself (which you cannot get at but which we can use in the 4GL runtime).
most places in the 4GL where you can use a rowid, there is something else (e.g. rowid(customer)) involved as well as the context of the usage, so the expected thing happens. still, one can subvert the correct behaviour and get into trouble, such as when a rowid is saved away someplace without the rest of the information, then retrieved and (mis)used later.
This is the construct I'm thinking of -
WHERE ROWID(customer) = ROWID(CustomerUpdate)
Your comment about the same table having duplicate rowid's makes me concerned that this would not work anymore. If that's not the case - ie it's the same table for different tenants - then we're good.
Experience is what you get when you don't get what you want.
Even without considering the possibility of duplicate rowids, that code might not work the way you want because after the first find, but before the second, the following could happen:
a second transaction comes and deletes the customer you found and then commits.
a third transaction comes along and creates a new record with the same rowid as before and commits.
if the new record is for a different table, then the find customerupdate will not find it.
if the new record is for the same table, then the find will succeed but the record will be the new one, not the one you found before.
I've used this construct for years, and have never run into the situation you described. I have had cases where I persisted a ROWID for something, and then had another part of the program delete a record for that table and make a new one, which meant the ROWID pointed to an "illegal' record.
If the scenario you describe happened "very often", most of the NO-LOCK code out there wouldn't work.
And, in many applications, even if the record was updated in between, one would want the new version. Only if there were a meaningful time delay ... e.g., if it went out to the user in between, would one worry and then, of course, one would use standard optimistic locking behavior to detect the possible change. The delete in between should be catchable and thus gracefully handled. Note that doing the second find with a GUID or whatever doesn't actually change the scenario except for your third case, which seems a fairly thin chance.
Which said, I can't see any reason why a find by RECID would be better than a find by GUID unless one was dealing with legacy code where one had to use multiple fields to uniquely identify the record.
Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice http://www.cintegrity.com
“Which said, I can't see any reason why a find by RECID would be better
than a find by GUID unless one was dealing with legacy code where one had to use multiple fields to uniquely identify the record.”
I think the thread proves the opposite. As we cannot control if/when/how/why RECID’s are reused GUID’s have a great advantage: You are in controls of reusing them or
not and if you are a reasonable developer, you’d never reuse them for different records.
Thomas Mercer-HurshWhich said, I can't see any reason why a find by RECID would be better than a find by GUID unless one was dealing with legacy code where one had to use multiple fields to uniquely identify the record.
Because a lot of legacy code out there predates the availability of a GUID, and ROWID/RECID can serve as a reasonable proxy for certain restricted cases.
Hence my "unless". Been there, done that. Even then, one has to admit that the real motivation for doing the find using a recid instead of on the three fields or whatever that make up the primary key is laziness in typing ... and, a certain clarity that the intent is "the same one".