I need to generate the next bptmm130.seqn value for a given employee-year-period so I can insert a record. Using the DAL would be the optimum choice, however I cannot do this because of a recursion error. To explain breifly, the code automates the creation of a second hours accounting record when certain types of labor entries are made in hours accounting (bptmm1130m000).

I have code to get the next seqn value but I'm concerned it is not safe for concurrent transactions --- that is two different people keying in records at the same time:


| have to get next seqn value
| is this safe for concurrent operations?
select max(bptmm130.seqn):max.seqn
from bptmm130
where bptmm130._index1 = {:i.emno, :bptmm130.year, :bptmm130.peri}
as set with 1 rows
selectdo
bptmm130.seqn = max.seqn + 1
selectempty
bptmm130.seqn = 1
endselect


then I go on w/ db.insert() later


All of this is happening in the ue.after.after.save.object() method of a user-exit DAL for bptmm130. So I'm assuming (and it appears) that db.retry/commit is being handled outside of my code (by 4GL engine?).

My questions are: is the code the way its written safe for concurrent transactions? If not, how can I change it so it is?

Thanks in advance,
-Tom

Hi Tom,

this is the standard way to find sequence numbers and is perfectly concurrency safe. As your code is running inside a transaction (which is started and commited by standard program) this is what happens when two processes execute the code at exactly the same time:

Both processes determine the same next sequence number.
One of the processes is successful and write its record in database.
The other process fails with error 100 "duplicate record" and has to go back to the retry point. By second attempt it "sees" the seuqnce number of the first process and generates a higher sequence number and now can save its record without problem.

vahdani,
Thanks for you reply and clear explanation. I am inserting the record w/ db.insert(tbl.id, DB.RETRY).

Also, after I create the bptmm130 record I need to restore the bptmm130 record buffer to the original values. I'm using db.store.record(), it seems to be working OK but I'm not entirely sure its necessary or that I'm using it correctly.


|* shown here without checking return values or error handling for brevity
tbl.id = db.bind("tbptmm130")
db.store.record(tbptmm130)
|* create new bptmm130 by changing some values in bptmm130 buffer:
bptmm130.emno = i.emno | etc.
db.insert(tbl.id, DB.RETRY)
db.restore.record(tbptmm130)
db.unbind(tbl.id)

Hi Tom,

there is no need for the variable "table.id" and explicit db.bind call. The table is already declared so all you need is the following:

table tbptmm130
...
...
db.store.record(tbptmm130)
db.retry.point()
|fill fields of bptmm130
...
db.insert(tbptmm130, db.retry)
commit.transaction()
db.restore.record(tbptmm130)
...