Another user has modified the record

ravnen

Hi,

We have a changelog system where we write in a table when a record is changed.
When the user changes a record whey will, by code create an entry in a change log table.
At the same time a NAS i running and reading the changelog table.
This all works very nice but sometimes this creates a error from the NAS saying the record has been changed by another user.

We don't update a record after it has been inserted. When the NAS has read it, it will delete it.

We don't use any kind of manual locking.

We experience the problem mostly in Native dbs but also in SQL bases though the errormessage is a bit different.
The Native is running in a 5.1 client.

Hope you got some good ideas!

garak

Do you use the standard changenlog function (Table 402 .. 405 / Form 592 .. 595 / Report 508 .. 510 / CU 423) :?:

regards

ravnen

No, we use one which we build ourselfs. But the basic structure is somewhat the same.
We have _one_ codeunit for inserting in the changelog. And that is the only place where we insert in the table. No modify in that codeunit

Best regards

Slawek_Guzek

We don't update a record after it has been inserted. When the NAS has read it, it will delete it.

What kind of primary key are U using in logging table ? Integer (like "Entry No.") or some composite key ? Are U re-using the same PK values ?

ravnen

We use a auto increment integer, just like the navision change log. We have tried using a number series for generating the number, but that doesn't make a change.

Slawek_Guzek

Are U incrementing it's value using CAL code in OnInsert or using AutoIncrement property ?

ravnen

using the property

David_Singleton

Why aren't you using the standard?

ravnen

Expensive and we had problems that i didn't always created an entry. Maybe that has changes in newer versions.
And we needed to be more customized.

I looked a the code for the standard changelog but i couldn't see the difference in the way changes are inserted.

Slawek_Guzek

What exactly error message says ?

'Another user has modified the record' or something like 'another user has modified the Field table' ?

Slawek

ravnen

No The excact errormessage in the log is :

Another user has modified the record for ChangeLog
"Entry No." '294383' after you retrieved it from the database.

garak

did you start the debugger to see which process modified the rec?
Did you also runs the NAS process from a NAV client and start there the debugger?

DenSter

ravnen wrote:

No The excact errormessage in the log is :

Another user has modified the record for ChangeLog
"Entry No." '294383' after you retrieved it from the database.

Process 1 calls process 2. They both read entry number 294383. Process 2 changes something (using MODIFY) and gives control back to process 1. Process 1 changes something else and does a MODIFY => error message. You need to either retrieve the record after process 2 is done, or pass the record by reference from process 1 to process 2.

<edit>added reference to MODIFY, good catch Slawek, obviously I meant both processes MODIFY the record, otherwise you wouldn't get the error</edit>

Slawek_Guzek

DenSter wrote:

Process 1 calls process 2. They both read entry number 294383. Process 2 changes something and gives control back to process 1. Process 1 changes something else and does a MODIFY => error message. You need to either retrieve the record after process 2 is done, or pass the record by reference from process 1 to process 2.

No DenSter, if process 2 do some changes but does not fire MODIFY then the change is made only to local copy of rec in process 2 allocated memory. MODIFY in process 1 after return from process 2 will not give error in your example.

MODIFY have to be fired twice to get this error, this can happen even in the same process - there is only one condition that must be met - there must be two different variables defined of the same type, both must GET/FIND the record, (or one must GET/FIND a record and the second variable must copy rec content using TRANSFERFIELD(rec,TRUE), and then both must fire MODIFY.

BTW - Trouble is that ravnen says:

We don't update a record after it has been inserted. When the NAS has read it, it will delete it.

. After re-thinking the problem it seems to me not possible to get 'Another user has modified record..' error message without any MODIFY.

Sometimes when NAS is serviced by event-driven procedures it happens that the same procedure is called second time (when another even comes) before it finished servicing the first event. Maybe NAS procedure is fired by some events, and it is called twice, and this second call is made before first call was completed. In such a scenario the same record will be read twice by two 'instances' of NAS procedure, but still without any MODIFY to log record the 'Another user has modified record' error message looks impossible to get to me.

DenSter

Slawek Guzek wrote:

No DenSter,
<snip>
MODIFY have to be fired twice to get this error, this can happen even in the same process

I said "two processes" because it can be a different function, a different codeunit, a different report, form, any type of object, a different variable. By using the word 'process' I meant to swipe them all into one word. And I know both processes have to MODIFY the record to get this error (actually I think you also get it when one process does an INSERT and another process a MODIFY), and I should have probably been clearer about that. I edited my reply.

My solution stands though, the only way to get this error is when two processes change the same record and the message pops up when the second record is written to the database. To solve this issue they will need to debug the process to find out where it happens, and either retrieve the record before changing it, or passing it by reference to the other process.

ravnen

I've used DevTools to locate where the table is used, And it seems that it doesn't use modify anywhere.

garak wrote:

did you start the debugger to see which process modified the rec?
Did you also runs the NAS process from a NAV client and start there the debugger?

Good point.
Well, The NAS starts by opening the changelog and gets a record, process the record and afterwards deletes it.
I tried to use the NAV client instead and the error was thrown when the NAS tried to delete the changelog record.

Is it possible to have "dirty reads" from the database? So the record that the user created a changelog record from, was not modified or inserted because it generated an error, and thereby the changelog which was inserted, was removed again due to roleback?