When reading a table record that has been created by another process, but that has perhaps not yet been committed by that process, I want to be able to determine whether the record is available for update / deletion at the point of reading it. Does anyone know a method of doing this?

Scenario:
- Process A creates a record in a Baan table, then moves on to create other records, but has not yet committed the entries.
- Process B is a continuous process that is running concurrently and reads the record that has been created by process A (but not yet committed). Currently, process B creates an output file from the record it has read, then attempts to delete the record from the table (generating a retry and, ultimately, a fatal error).
I want to be able to identify, at the point of reading the record in process B, that it is not yet available to delete and skip it until the next cycle.

Any thoughts, anyone?

OK, I have worked around this by making the following change to process B:
After reading the record without update,
1) Set a start time
2) Set a retry point
3) Set a finish time
4) Compare start and finish time and if > 2 seconds have elapsed, abort the transaction and skip the read of this record (indicates a retry)
5) Read the record for update
6) Back up the contents of a field value
7) Change the field value
8) Commit the change (if this fails, it will return to 2, above)
9) If the commit was successful, restore the field value from backup and proceed to process the record

This works, but if there is a better method that anyone can think of, then I would be interested to hear from you!

Andy

Hi,

I guess setting the time will not help and could cause issues. DB operations are rather fast and you dont have a time stamp for separating the interval.

I will go with a flag setting way. You can hold old values in temp variables after commit you can check if the commit is done or not.

Btw, I am still not sure about what you are looking for. If you can post your code snippet, that will help.

There a couple ways you can handle this. The easiest would be to modify your db.update (if using 'db' instead of DAL) to trap errors instead of going to the retry point. This way, you can gracefully handle the ELOCKED (record locked) or EFLOCKED (file locked) situations by disregarding the line until the next run that it becomes available.


reterr = db.update(ttableid, db.retry, db.return.error)
if (reterr = ELOCKED or reterr = EFLOCKED) then
| do nothing, wait until next update
else
| handle other type of error yourself
endif




You might be able to check for the lock ahead of time using db.check.row.dlocked:


| tableid is a table with two fields in the primary index.
| fill all index fields
tableid.field1 = ...
tableid.field2 = ...
if (db.check.row.dlocked(ttableid) = 0) then
| record is not locked, fill fields and update
else
| record is locked, wait until next run - dlock > 0 is locked, < 0 is error.
endif

Okay - did I miss something or am I not understanding something. I see this statement - "When reading a table record that has been created by another process, but that has perhaps not yet been committed by that process" - if the record has not been committed then another process would not be able to read it. So in your exampe - until process A commits the record process B would not be able to read it. As far as I know that is how Oracle works - is informix different?

PS - I use application locks for records on tables that I do not want to be messed with. Those are easy to set and release.

Re: Setting a flag at db.delete / db.update:
Thanks bhushanchanda & BaanInOhio, but by the time the program reaches this point, it has already created O/S file entries and I want to prevent it from doing so in the first place, if it is not available for update.

Re: db.check.row.dlocked:
Thanks BaanInOhio - I wasn't aware of this function and it's not in the manuals I have seen. It seems like the perfect solution, but doesn't seem to work for me. I'm handling a bespoke table tried this:
ecedi904.pmno = i.pmno (Assigns single-field primary key)
tableid_ecedi904 = db.bind("tecedi904") (Creates pointer successfully)
if (db.check.row.dlocked(tableid_ecedi904) = 0) then (Zero is returned here, even though this record is locked by the other process)
Any ideas? (In this scenario, the Baan version is Vb - do you know whether this function is compatible?)

Mark_h:
Thanks for your thoughts. Process A in my scenario is creating multiple records in a bespoke table and commits at the end. As it is processing and before the commit point, the records are available to read - in fact these can be viewed via ttaad4500 before commit. (If the process were to be deliberately terminated without committing, for example, then the records are not written and are no longer visible - this is the same for all Baan tables during update operations on AS400 / DB2.)

Process A in my scenario is creating multiple records in a bespoke table and commits at the end. As it is processing and before the commit point, the records are available to read - in fact these can be viewed via ttaad4500 before commit. (If the process were to be deliberately terminated without committing, for example, then the records are not written and are no longer visible - this is the same for all Baan tables during update operations on AS400 / DB2.)

I never knew that about AS400/DB2.

How many records are being written before being committed? If it is not a lot of records then process A could set an application lock when it writes the record. Then it could remove the application when it does the commit. Process b could read the record, check for application lock, if it exists skip the record.

Now if it is writing 1000's of records this would probably not be a good solution. And there is probably something better. I know what bhushan an baaninohio recommended should work. I only used something like it once, but I think I just skipped any error - the next run would pick up the record. Can't seem to find that code.

I found some old code a contractor wrote on our system - not sure if it helps or would even work in your case. Basically it looks like he tried to lock the record - if it was already locked it could not be updated.

select tdpur041.orno, tdpur041.pono
from tdpur041
where tdpur041.orno = :tdpur040.orno
selectdo
save.error.bypass = error.bypass
error.bypass = 2
retry.count = 0
db.eq(ttdpur041,db.delayed.lock)
while db.error(ttdpur041) = ELOCKED
if retry.count < 10 then
mess("tibom11106",0, tdpur041.orno,
tdpur041.pono, retry.count)
| Record of positon %d/%d is locked; Retried
| for %d sec.
retry.count = retry.count + 1
suspend (1000) | 1 seconds
db.eq(ttdpur041,db.delayed.lock)
else
clean.mess()
lines.locked = true
return
endif
endwhile
error.bypass = save.error.bypass
if retry.count <> 0 then
clean.mess()
endif
lines.locked = false
endselect

Thanks for the snippet, Mark - looks useful!

Hi Mark,

The code you have posted is not working in BaaN V.
g.tcibd100.id = db.bind("ttcibd100","",011)
select tcibd100.*
from tcibd100
where tcibd100._index1 = {:1}
order by tcibd100._index1
as set with 1 rows
wherebind(1, item.f)
selectdo
save.error.bypass = error.bypass
error.bypass = 2
retry.count = 0
db.eq(g.tcibd100.id,db.delayed.lock)
g.lc.c = db.error(g.tcibd100.id)
while db.error(g.tcibd100.id) = ELOCKED
if retry.count < 10 then

retry.count = retry.count + 1
suspend (1000) | 1 seconds
db.eq(ttcibd100,db.delayed.lock)
else
clean.mess()
lines.locked = true
return
endif
endwhile
error.bypass = save.error.bypass
if retry.count <> 0 then
clean.mess()
endif
lines.locked = false
endselect

if ( lines.locked = false ) then
|no lockin g
select tcibd100.*
from tcibd100 for update
where tcibd100._index1 = {:1}
order by tcibd100._index1
as set with 1 rows
wherebind(1, item.f)
selectdo
tcibd100.ncst = tcncst.no
tcibd100.ncdt = 0
db.update( ttcibd100, db.retry )
endselect
else
g.fp.c = seq.open("\\kuuerp03\userdata\asha\011_Monitor.txt","at+")
seq.puts(trim$(item.f) & " ; " & str$(g.lc.c),g.fp.c)
seq.close(g.fp.c)
endif
commit.transaction()