LLblGen 3.0 Rel Dec 3 Oracle 9i/10g

Is there way to execute the syntax "Select For Update" in queries ?

Let me explain my scenario so that you may even suggest other way other than "Select for Update"

I have only table BILLCOUNTER Columns BILLCOUTER_ID [PK] BILLNUMBER

Now, When a person is making invoice, what I am doing is SELECT * FROM BILLCOUNTER UPDATE BILLNUMBER = BILLNUMBER+1 output of BILLNUMBER+1, I am storing in variable since I have to pass the same to other tables.

Now the problem is when two users execute at same time they get same bill number.** I cant apply expression in predicates in this case because if I do so, I get the BILLNUMBER only at commit.**

Now, When a person is making invoice, what I am doing is SELECT * FROM BILLCOUNTER UPDATE BILLNUMBER = BILLNUMBER+1 output of BILLNUMBER+1, I am storing in variable since I have to pass the same to other tables.

Now the problem is when two users execute at same time they get same bill number. I cant apply expression in predicates in this case because if I do so, I get the BILLNUMBER only at commit

Sorry I'm not following, could you please elaborate with more details.

Why do you select from the table, while you can directly issue update statments? What's wrong with getting the BillNumber at commit? Where do you need to store the new BillNumber?

Does the BillCounter table end up having only one row?

Walaa wrote:

Now, When a person is making invoice, what I am doing is SELECT * FROM BILLCOUNTER UPDATE BILLNUMBER = BILLNUMBER+1 output of BILLNUMBER+1, I am storing in variable since I have to pass the same to other tables.

Now the problem is when two users execute at same time they get same bill number. I cant apply expression in predicates in this case because if I do so, I get the BILLNUMBER only at commit

Sorry I'm not following, could you please elaborate with more details.

Why do you select from the table, while you can directly issue update statments? What's wrong with getting the BillNumber at commit? Where do you need to store the new BillNumber?

Does the BillCounter table end up having only one row?

Hi Walaa

Yes I can directly issue update statement. But I need to get the value before commit since I need to insert this bill numer in another table. Yes bill counter will have only one row. Simple explanation is as follows

1. Get the bill number from table billcouner and add 1 to it
2. Insert to table called receiptsmartcard
3. Commit / rollback.

When these steps are in progress, prevent another user from doing these steps in order to ensure continuity in bill number.

This should be handled in some service code using a critical section.

Another way is to have a timestamp field in that table and use a Concurrency Predicate to prevent a user from updating the entity (which he has fetched first), if it has been altered by someone else. And then if the entity is not updated, the code should attempt to refetch it and update it again.