Hi,

I'm not sure, if I understand the description of the "delayed lock" right.

When exactly is the record locked?
I think it ist done after the comit.transaction() at a time, when the database (oracle) realy writes the data to the table. But then the above error couldn't happen that easy.

Or

happens the above error if:

- I programm a: db.update()
- change the actual record somehow
- do a commit.transaction()

We have the above error, and I think without an exact understanding of delayed locks, it'll be hard to find the problem.


Thanks

delayed lock means that the lock is delayed until the next query, commit or abort. If one of these is reached, first the insert, update, or delete is done, and then the query is executed.

This is easy to check in a small program:

table ttcmcs001
table ttcmcs002

function main()
{
db.retry.point()

db.first(ttcmcs001, db.lock)

tcmcs001.dsca = "DJW"

db.update(ttcmcs001, db.retry)

db.first(ttcmcs002)

abort.transaction()
}

Compile it like:
$ bic6.2 p0 -lo ./p0.o

Start it like:
$ ORAPROF=0.0 ba6.2 ./p0.o

trace on one screen the oraprof file (tail -f oraprof) and step through the code on another.

Hope this helps,
Dick

Hi Dikkie,

did it, but "tail -f oraprof" doesn't do something during I stepped trough the program. I started the second window from Baan, like the first on with "ksh". Do I have to start the second window as a child process from the first one? If yes, can you tell me how?

Thanx a lot

The tail -f could be started from a any other window. But it probably only gives information on a UNIX or Linux box. Are you using any of these?

Hope this helps,
Dick

Found it :p The oraprof is located in my CD. I tailed the wrong one.

Thanx

i am really confused with the delayed lock concept, i have some queries please answer them.
there a table pcmmm001 with 3 records lets say A, B , C .

1) select pcmmm001 .*
from pcmmm001 update
where record = A
selectdo
i changed some info record A
db.update()
select pcmmm001 .*
from pcmmm001 update
where record = B
selectdo
i changed some info record B
db.update()
select pcmmm001 .*
from pcmmm001 update
Where record = A
selectdo
i changed some info record B
db.update()
endselect
endselect
endselect
will I get a error cannot lock the record ? why or whynot ?

will I get a error cannot lock the record ? why or whynot ?
No, you won't get the error.

why is the record A not locked? .....

if i do the same thing with main table(kpoes721 ; maintain session while updating a existing record) i.e


field.kpoes721.mipr:
after.input:
on.main.table(testsing_ommaintable)
function testsing_ommaintable()

{
long a

long temp
temp=kpoes721.scno -2
select kpoes721.*
from kpoes721 for update
where kpoes721.scno =:temp
selectdo
temp = temp-1
kpoes721.desc = "vamsi testing"
end select
db.update(kpoes721,db.retry)
}

i get an error on transaction is on cannot continue


although i can do that with in after.rewrite in main.table.io:

Where is you db.retry.point()? It looks like you are already in the middle of an update of the current record. I am not even sure you need the db.update. Not sure what you are trying to accomplish, but if you can explain that maybe someone can assist.

hello mark ,i ignored db.rety(), i was just trying to update a record from main session simultaneously i wanted to update another record ie.

by U.I(session) if i am updating record no. 5 simultaneously i want to update record no.3 through script.

there is no requirement as such i am just trying to understand why aint it working..

Got it - just trying to test. Here is the help from on.main.table:

This copies the contents current record of the main table to the record buffer of that table, saves the record, and executes the specified function. After that the saved record buffer is restored. This enables you to perform actions on the record contents without affecting the values in the table.

Notice the last statement - this enables you to perform actions on the record contects without effecting the table. I do not know if this will work but have you tried an actual commit:

field.kpoes721.mipr:
after.input:
on.main.table(testsing_ommaintable)
function testsing_ommaintable()

{
long a

long temp
temp=kpoes721.scno -2
select kpoes721.*
from kpoes721 for update
where kpoes721.scno =:temp
selectdo
temp = temp-1
kpoes721.desc = "vamsi testing"
end select
db.update(kpoes721)
}


The only time I have done something like this was inside a query in a prorate process. So basically I found a record, did reversal, then insert the new prorate records on the same table.

Forgot to mention every place I have used on.main.table I am only checking a condition on the table like the example or maybe getting the last used position or serial number. Nothing were updates were involved.

Mark , i have tried commit.transaction() the actually 2 observations
1) while inserting , everything is working fine i am able to insert as well as modify the existing record too.

2) but if i try to update any existing record (say 5) then simultaneously it should update record 3 but an error occurs stating cannot lock the record(but record 3 is updated). i feel as i commited the transaction due to which all the lock have been removed

and about on.main.table : i assume that it save the current record , (pls let me know if i am wrong)

Sorry I meant to say have you tried just an update - without the db.retry. As far as I know it does not update the current record - but you could try execute(update.db), then go and do a an update on another record. I am not sure the execute(update.db) will work in that even - you might have to move it around to get it in the correct spot.

function testsing_ommaintable()

{
long a
execute(update.db)
long temp
db.retry.point()
temp=kpoes721.scno -2
select kpoes721.*
from kpoes721 for update
where kpoes721.scno =:temp
selectdo
temp = temp-1
kpoes721.desc = "vamsi testing"
end select
db.update(kpoes721,db.retry)
}

Thank You Mark. Your post helped me. Your pointer on update.db before doing further actions on the main table resolved the 'db.lock' problem.

hmmm... i never tried it at all..(actually i forgot i guess ) but thanks mark ...
punitha .. thanks for confirming that it works..;)