I have a program that I can not find what the problem is. This piece of code aborts on the second run with with the record changed after delayed lock error:


db.retry.point()
select tpbcr606.*
from tpbcr606 for update
where tpbcr606.gcid = :gcid.f
selectdo
tpbcr606.incl = tcyesno.no
db.update(ttpbcr606, db.retry)
commit.transaction()
endselect


This program is the only one that can change this table - plus only one user can run it at a time. Is this delayed lock error being caused by another query on this table? There are other places that do updates on the table, but they all look like the above. What am I missing? I have read all the other posts on this topic, but see nothing that helps me.

thanks

This program is the only one that can change this table thanks

Is there another place in the code that updates this table?

Yes - there are other places:


| Get current billing cycle data to test which records are included and
| which get skipped.
select tpppc980.*,tpbcr601.*
from tpppc980, tpbcr601
where tpppc980._index1 = {:date.f, :gcid.f}
and tpppc980.ninv = 0
and tpbcr601._index1 = {tpppc980.date, tpppc980.gcid, tpppc980.coty}
selectdo
| Find any bcr records
select tpbcr600.*
from tpbcr600
where tpbcr600._index1 = {:tpbcr601.gcid, :tpbcr601.cprj, :tpbcr601.clin}
selectdo
|Check for the key on table 606 using tpbcr600 records - for each BCR
|look for a 606 record. May need to add an index later.
db.retry.point()
select tpbcr606.*
from tpbcr606 for update
where tpbcr606.gcid = :tpbcr601.gcid
and tpbcr606.coty = :tpbcr601.coty
and tpbcr606.cprj = :tpbcr600.nprj
and tpbcr606.clin = :tpbcr600.cln1
and tpbcr606.year = :tpbcr601.year
and tpbcr606.cotp = :tpbcr601.cotp
and tpbcr606.cobj = :tpbcr601.cobj
and tpbcr606.obsp = :tpbcr601.obsp
selectdo
tpbcr606.incl = tcyesno.yes
db.update(ttpbcr606, db.retry)
commit.transaction()
endselect
selectempty | Means no BCR records
|Check for the key on table 606 using tpbcr601 records
db.retry.point()
select tpbcr606.*
from tpbcr606 for update
where tpbcr606.gcid = :tpbcr601.gcid
and tpbcr606.coty = :tpbcr601.coty
and tpbcr606.cprj = :tpbcr601.cprj
and tpbcr606.clin = :tpbcr601.clin
and tpbcr606.year = :tpbcr601.year
and tpbcr606.cotp = :tpbcr601.cotp
and tpbcr606.cobj = :tpbcr601.cobj
and tpbcr606.obsp = :tpbcr601.obsp
selectdo
tpbcr606.incl = tcyesno.yes
db.update(ttpbcr606, db.retry)
commit.transaction()
endselect
endselect
endselect


and


db.retry.point()
select tpbcr605.*
from tpbcr605
where tpbcr605._index1 = {:date.f, :gcid.f}
selectdo
rcd.ttpbcr606 = rcd.ttpbcr605
if check.for.bcr() then
| 20050728.st Allow BCR to change Project.
tpbcr606.cprj = tpbcr600.nprj | 20050728.end
tpbcr606.cln1 = tpbcr600.cln1
else
| 20050728.st Allow BCR to change Project.
tpbcr606.cprj = tpbcr605.cprj | 20050728.end
tpbcr606.cln1 = tpbcr605.clin
endif
| 20051025.st All new differential records go to yes.
tpbcr606.incl = tcyesno.yes | 20051025.end
db.insert(ttpbcr606,db.retry)
commit.transaction()
endselect


I have been playing with this puppy all day. I have tried with and without retry in several places. I know I am missing something, but I am not seeing it.

You get a problem if you don't run in the selectdo because there is no commit after the endselect. If you call this piece of code again, there is always an open transaction... . Move the commit behind the endselect.



Regards
csecgn

Hello mark,

i think, this will help:


.......................>>>>
tpbcr606.incl = tcyesno.yes | 20051025.end
db.insert(ttpbcr606,db.retry)
commit.transaction()
endselect
commit.transaction()

}

Regards

Every place I use this table I added a commit after the select. Still the same error.

thanks

I went through the complete program adding commits after the endselect for any update, insert or delete done on any table. I still get this abort on this table. This table does not have any references or anything. Any other suggestions are welcome?

What the heck. I'm glad it was not the commit after the endselect, because I do what you do all the time :)

Maybe some more detail will talk it out..

Is this an exact description - it runs ok the first time but then not again the second time? Repeatable? Does it work right after you exit the session and come back? And, how does this look in the debugger...? It should go back to the retry point on a 'dirty read', does it do that? Are there multiple records for that 'gcid.f'? If so, probably should put an 'order by' in there to resolve issues on a retry.

Is any call of any the selects inside the maintable.io section? If yes, remove the db.retry.points/commits.

Regards
csecgn

Yes - the first time I click process everything runs fine. Click process again and boom. The program hits the commit.transaction on the very first post and starts processing again at the db.retry.point.

I went through the program and added an order by clause for every query with a table selected for update. Still have the error. In this case for the contract there are multiple records. As long as I process and exit the session it works every dang time.

No main table sections tried as both print and maintain session. Did I miss any questions?

Funny - if I comment out all of this code it will run twice in a row.


db.retry.point()
select tpbcr606.*
from tpbcr606 for update
where tpbcr606.gcid = :gcid.f
order by tpbcr606._index1
selectdo
tpbcr606.incl = tcyesno.no
db.update(ttpbcr606, db.retry)
commit.transaction()
endselect
commit.transaction()


| Get current billing cycle data to test which records are included and
| which get skipped.
select tpppc980.*,tpbcr601.*
from tpppc980, tpbcr601
where tpppc980._index1 = {:date.f, :gcid.f}
and tpppc980.ninv = 0
and tpbcr601._index1 = {tpppc980.date, tpppc980.gcid, tpppc980.coty}
selectdo
| Find any bcr records
select tpbcr600.*
from tpbcr600
where tpbcr600._index1 = {:tpbcr601.gcid, :tpbcr601.cprj, :tpbcr601.clin}
selectdo
|Check for the key on table 606 using tpbcr600 records - for each BCR
|look for a 606 record. May need to add an index later.
db.retry.point()
select tpbcr606.*
from tpbcr606 for update
where tpbcr606.gcid = :tpbcr601.gcid
and tpbcr606.coty = :tpbcr601.coty
and tpbcr606.cprj = :tpbcr600.nprj
and tpbcr606.clin = :tpbcr600.cln1
and tpbcr606.year = :tpbcr601.year
and tpbcr606.cotp = :tpbcr601.cotp
and tpbcr606.cobj = :tpbcr601.cobj
and tpbcr606.obsp = :tpbcr601.obsp
order by tpbcr606._index1
selectdo
tpbcr606.incl = tcyesno.yes
db.update(ttpbcr606, db.retry)
commit.transaction()
endselect
commit.transaction()
selectempty | Means no BCR records
|Check for the key on table 606 using tpbcr601 records
db.retry.point()
select tpbcr606.*
from tpbcr606 for update
where tpbcr606.gcid = :tpbcr601.gcid
and tpbcr606.coty = :tpbcr601.coty
and tpbcr606.cprj = :tpbcr601.cprj
and tpbcr606.clin = :tpbcr601.clin
and tpbcr606.year = :tpbcr601.year
and tpbcr606.cotp = :tpbcr601.cotp
and tpbcr606.cobj = :tpbcr601.cobj
and tpbcr606.obsp = :tpbcr601.obsp
order by tpbcr606._index1
selectdo
tpbcr606.incl = tcyesno.yes
db.update(ttpbcr606, db.retry)
commit.transaction()
endselect
commit.transaction()
endselect
endselect

Ok, for me there are some questions:

What kind of Session you are using?

What ist the maintable if any?

Regards
csecgn

Originally I had it defined as a maintain session, changed to print just for grins and giggles. Script type is 4 as is the form. All the user does is enter billing cycle date and contract. The program pulls the tpppc981 records, stores them, does stuff to them, then puts them back in tpppc981. The maintable defined on the session record is tpbcr600. I have a whole series of tables to track what happens to keep things lined up for putting back into the tpppc981 table.

Thanks for all the help.

Solution found - it was the rcd.ttpbcr606 = rcd.ttpbcr605. Took this out and put in all 28 fields and it started working. The table tpbcr605 and 606 used to be the same number of fields. I added a couple to tpbcr606 and I guess those hidden fields like ._compnr were causing problems.

Mark,

You found it before I had a chance to reply. I had a similar problem about 1 year ago. When you use the record buffer there is a unique identifier for the lock id. When you transfer it to the other table it looks for that lock and give you the error. If you save the original lock id from the first record buffer and restore it after assigning the buffer it should also work.

Mark,

You found it before I had a chance to reply. I had a similar problem about 1 year ago. When you use the record buffer there is a unique identifier for the lock id. When you transfer it to the other table it looks for that lock and give you the error. If you save the original lock id from the first record buffer and restore it after assigning the buffer it should also work.


Thanks - I saw something about that mentioned. The last time I had errors using the record buffer it was caused by the "._compnr". In that case the two tables were identical.

Thanks to everyone for the assist.