Record changed by another user error when editing record of indexed organized table

[Fredrik]

We are getting "Record changed by another user" errors when editing records on index organized tables.
It seems that this only happens on our non-unicode database and it only happens if the table is index-organized.

I have sent 3 emails to support with the same topic but no answers.
The emails has thorough explanations of how to reproduce the issue.

 

[Marco Kalter]

Could it be that the SQL statement for the dataset includes some operations that modify the column data? For example a field like this:

select trunc(order_date) as order_date, ...

In such a case the record-change-check will see a different value in the result set than in the database, and will raise the error.

This is of course unrelated to index-organized tables or unicode, but it would be the first thing to check.

 

[Fredrik]

No its a select x.rowid, x.* from table, so that is not the reason for the issue

Please take a look at the project I sent by email

 

[Fredrik]

Still no response to my emails. Did you not get them?

 

[giga]

Based on table type and some setting the row can be moved during update (due to better performance) and at that case the rowid is changed. And modified by another user is reported....

 

[Fredrik]

Thanks for trying to help but I don't think this has anything to do with the rowid. If anyone at allroundautomations would try the sample project I sent I guess they would see the problem pretty easy.

I have a table like this test-table:

CREATE TABLE TEST_PRICELISTCODES
(
PRICELISTID VARCHAR2(10 CHAR),
STDPRODUCTID VARCHAR2(20 CHAR),
ALLOWNONSTDSIZE NUMBER(1),

CONSTRAINT PK_TEST_PRICELISTCODES
PRIMARY KEY
(PRICELISTID, STDPRODUCTID)
ENABLE VALIDATE
)
ORGANIZATION INDEX
;

What happens is that in TOracleDataSet.RecordChanged FI.QueryValue(Q) returns a value for 'STDPRODUCTID' that has x charachters cut off at the end of the string. What is strange is that how many characters that is cut off is affected by the declared size of the field 'PRICELISTID' which is the field just "before" in the table.

All this is also explained even more thorough in the mentioned emails to allroundautomations.

 

[Fredrik]

Still no real response from allroundautomations either here or to my emails. I have been getting good support from you guys in the past.
I really dont like this silence...

 

[Carlo Sirna]

Friedric: giga is right: index organized tables are exactly one of the data structures that cause row movement when data is updated.

info you need to know to understand this:
1) the rowid string is not a "random" value: it is the exact phisical position of the record in the database: if the record is moved to another disk block, its rowid changes.
2) index organized tables store row values DIRECTLY in the same data structure used for the primary key index: that is the table data is phisically "sorted", depending on the primary key values: if you change the primary key values, the record must be moved to another position, so its rowid changes.

so: you are experiencing EXACTLY what you should expect to happen when you use index organized tables: you changhe the primary key and the record is moved, so it rowid changes.

this means that you must write your own code in the OnApplyRecord event in order to implement the sql update.

I have never worked with index organized tables but I think that setting the UniqueFields dataset property should solve your problem

as a side note (for DOA developers if they are still reading):
1) OracleDataset could avoid this problem if it was using the "returning rowid into :newrowid" clause also for Updates, not only for inserts

2) it would be nice, for the same reason, that the "NewRowid" parameter of the OnApplyRecord event was taken in consideration also for Action = 'U' and not only for Action = 'I'

for the point 2 I did already solve it by myself by adding this line in my copy of the sources:

inside "procedure TOracleDataSet.InternalInsertUpdate;"

if ApplyRecord(c, NewRowId) then
begin
if Inserting then SetRowId(b, PChar(NewRowId));
if Updating and (NewRowID'') then //

 

[Fredrik]

Thanks Carlo Sirna for trying to help and sorry for not responding back sooner. I never got any notifications of a new answer to my post.

Sadly your reply is based on a wrong assumption that I am changing the primary key, which I am not(!).
In the test table i mentioned, I get the error when changing the value of ALLOWNONSTDSIZE. This is clearly a bug.
If allroundautomations would bother reading my email with a test project they would probably see right away.

If anyone will try this then create the table as mentioned earlier on a non-unicode database (on unicode database it works).
Then insert this test data:
Insert into TEST_PRICELISTCODES
(PRICELISTID, STDPRODUCTID, ALLOWNONSTDSIZE)
Values
('NY15', 'TS', 1);
Insert into TEST_PRICELISTCODES
(PRICELISTID, STDPRODUCTID, ALLOWNONSTDSIZE)
Values
('NY15', 'DIDCHRISTOPHERSP5KT', 1);

Create a test application that loads this data and try to change the value of the column ALLOWNONSTDSIZE. On the row with the value 'TS' in the column STDPRODUCTID it works, but in the row with 'DIDCHRISTOPHERSP5KT' you get "Record changed by another user".

 

[istrebitel]

Hi. SYN_SKL2MOL is IOT.
I enable debug mode on OracleDataSet.
When I change record ODS execute query:

select * from SYN_SKL2MOL where rowid = :doa__rowid
for update nowait
OA__ROWID = *BAGGZ6MQODAwMTAwMDAwMDAxMUVDNAd4

but rowid this record = *BAGGZ6MQODAwMTAwMDAwMDAxMUVDNAd4cgEBAQEB/g
DOA cuts the rowid.
rowid wrong and I get exception record locked by another user.

DOA Info
Direct Oracle Access 4.1.2.0
OracleHomeKey: SOFTWARE\ORACLE\KEY_OraClient11g_home1
OracleHomeDir: C:\oracle\product\11.2.0\client_1
Found: oci.dll
Using: C:\oracle\product\11.2.0\client_1\bin\oci.dll
OCI: version 11.1
Oracle Database 11g Release 11.2.0.2.0

Oracle Version
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

 

[Fredrik]

Please check my email to support@allroundautomations.com sendt today.
Regarding this issue.
The topic of the email is "Record changed by another user error when editing record of indexed organized table"

This is a forwarded email that i first sendt 2 years ago, forwarded again a wee later, but never got a reply to.