Record Lock by another user

[wilderland]

In our application we will delete a Record in a TOracleDataSet.
In the AfterDelete event we start a skript correct other records, so are all record always in the right order.
Our appliction should also delete delete a couple of record, when we do that one after the other, we got the error "Record changed by another user".

What can we do ?

The table:

CREATE TABLE lis_formular_merkmale
(position NUMBER(38) NOT NULL,
lis_merkmal_id NUMBER(38) NOT NULL,
lis_form_id NUMBER(38) NOT NULL)

ALTER TABLE lis_formular_merkmale
ADD CONSTRAINT pk_lis_formular_merkmale PRIMARY KEY (lis_merkmal_id, lis_form_id)

The output of the OracleMonitor

04:49.967 00:00.010 Query.Execute savepoint DOA__LOCKPOINT
04:49.977 00:00.060 Query.Execute (1 record processed) select * from LIS_FORMULAR_MERKMALE where rowid = :doa__rowid for update nowait
04:50.037 00:00.160 Query.Execute (1 record processed) delete from LIS_FORMULAR_MERKMALE where rowid = :doa__rowid
04:50.207 00:00.091 Query.Execute (1 record processed) update LIS_FORMULAR_MERKMALE set Position = Position -1 where POSITION > os and LIS_FORM_ID = :Form
04:50.448 00:00.001 Query.Execute savepoint DOA__LOCKPOINT
04:50.448 00:00.010 Query.Execute (1 record processed) select * from LIS_FORMULAR_MERKMALE where rowid = :doa__rowid for update nowait
04:50.458 00:00.020 Query.Execute rollback to savepoint DOA__LOCKPOINT
04:50.488 00:00.001 Query.Execute rollback to savepoint DOA__LOCKPOINT

P.S. it seems that the RefreshOptions doesn't work.

 

[Marco Kalter]

If the records of a TOracleDataset are modified elsewhere (e.g. on the server through a trigger, through a TOracleQuery, or through another TOracleDataSet), you should subsequently refresh the dataset.

Alternatively, you can include the roBeforeEdit option in the RefreshOptions of the TOracleDataSet. If you have determined that the RefreshOptions do not work, then perhaps you can explain why?

------------------
Marco Kalter
Allround Automations

 

[wilderland]

Yes, the RefreshOptions don't work because then user selects a couple of record in a list.

The programm try's to locate the record in the TOracleQuery and will delete the founded record. This will work fine, but we have also a AfterDelete-Event on the TOracleDataset, which will change a fieldvalue with update-statment, because the positions in must be always correct in the database. If we do this we get the error "Record has been changed by another user", when we will delete more than 1 record.

If we always refresh the TOracleDataSet after deleting one record, we get a performace-problem, when a user selects 100's of record for delete. The user will not accept this wait time.

CachedUpdates is not a option, because of the application design. I think its a bug in TOrcaleDataset, because there is no RefreshOption roBeforeDelete.

Do you have a idea how we can solve the problem.

 

[Marco Kalter]

Hmm, maybe you should call TOracleDataSet.RefreshRecord before you delete it.

We'll consider the roBeforeDelete option.

------------------
Marco Kalter
Allround Automations