How to create sequenced records

[rvandehoek]

I would like to create sequenced records in a table. I have a control table with a sequence number. Users can increase it and create an order record with this sequence number with this code:

promp-for order_nbr.
find order where order_nbr = input order_nbr no-error.
if not avail order then do:
find first control exclusive-lock.
control_nbr = control_nbr + 1.
release control.
create order.
order_nbr = control_nbr.
end.

The code continues with updating the order record. Now when a second user starts this code he get the message that the control table is in use. I don't understand this message because of the release in the code.
Is there a better way to do this?

Thanks, Rene

 

[WillieG]

promp-for order_nbr.
find order where order_nbr = input order_nbr no-error.
if not avail order then do:
find first control exclusive-lock.
control_nbr = control_nbr + 1.
release control.
create order.
order_nbr = control_nbr.
end.

Rene,
"release control" will only downgrade your exclusive-lock to a share-lock, so the next user cannot get an exclusive-lock on "control".
You can either omit the exclusive-lock, or start a transaction block by changing: "if not avail order then do:" to "if not avail order then do ON ERROR UNDO, RETURN:"

 

[rvandehoek]

Willie,
Both suggestions make no difference. Control table still in use.

 

[raghooveer]

Hi,

When you execute "Release" then lock will be downgraded to NO-LOCK and not to Share lock.


Regards
Raghuveer

 

[raghooveer]

Hi,

When "Release" is executed progress will release the record from buffer and if you try to access it again with out fetching, you should get the message

"No <tablename> record is available".

Release will not downgrade the lock.

Regards
Raghuveer

 

[WillieG]

Rene,
Keep your transaction block to update the control table as short as possible, and keep the updates to the database and the FIND EXCLUSIVE-LOCKS in a seperate transaction block, so that your procedure doesn't become a transaction block. I do something simular with automatic order numbers. The down side is if the user decide not to go on with the order, then the control table is already updated, with no roll-back option, so you might end up with missing orders in between:
DEF VAR v_control_nbr LIKE control.control_nbr NO-UNDO.
DO ON ERROR UNDO, RETRY:
FIND control EXCLUSIVE NO-ERROR.
REPEAT:
find orders where
orders.order_nbr = control.control_nbr.
if not available orders then leave.
assign control.control_nbr = control.control_nbr + 1.
end.
ASSIGN v_control_nbr = control.control_nbr.
END.
/* seperate transaction block to update the order */
DO TRANSACTION:
CREATE orders.
ASSIGN orders.order_nbr = v_control_nbr.
UPDATE orders.xxxxxxxxxx
WITH FRAME xxxxxxx etc.
END.

 

[rvandehoek]

Willy,
Thanks for your help. But the result is still the same.
I'm not familiar with transactions but the compiler returns this message:
** WARNING--TRANSACTION keyword given within actual transaction level. (214)
Could this be a problem?

 

[WillieG]

A transaction is one iteration of the outermost FOR, REPEAT or procedure block that contains direct updates to the database (INSERT, CREATE, UPDATE, SET, ASSIGN or DELETE of any database record).
Other transaction blocks that you get are:
1. Any block statement followed by the TRANSACTION keyword
2. a DO ON ENDKEY or DO ON ERROR block that directly updates the database or reads records with a specified EXCLUSIVE-LOCK.
3. a Procedure, FOR EACH or REPEAT block that directly reads records with a specified EXCLUSIVE-LOCK.
The error that you get show that your "DO TRANSACTION" block is already within a transaction. Somewhere in your procedure, you directly update the database or read a record with EXCLUSIVE-LOCK, which make your procedure block a transaction block. The control table will only be released when the transaction block ended, which in your case is at the end of the procedure. You need to put all your other direct updates to the database and FIND .... EXCLUSIVE-LOCK in a seperate transaction block, so that the update to the control table could stay in it's own transaction block and not become a transaction block within a transaction block (a sub-transaction).
A transaction block defines to PROGRESS what to roll-back in the event of a failure, so the records will stay locked until the transaction block end. A sub-transaction block will only be released ones the transaction block end.
I hope this will help
Greetings
Willie

 

[it@flude.co.uk]

promp-for order_nbr.
find order where order_nbr = input order_nbr no-error.
if not avail order then do:
find first control exclusive-lock.
control_nbr = control_nbr + 1.
release control.
create order.
order_nbr = control_nbr.
end.

Is there a better way to do this?

Rene
I'm not sure of a better way, but what I normally do is to find the record NO_LOCK to check it is there, if not create it and release it, then find it again EXCLUSIVE-LOCK.

So, try something like:

def var new_control_nbr like control.control_nbr no-undo.

prompt-for order_nbr.
find order where order_nbr = input order_nbr no-lock no-error.
if not avail order then do:
find first control exclusive-lock.
control.control_nbr = control.control_nbr + 1.
new_control_nbr = control.control_nbr.
release control.
create order.
order.order_nbr = new_control_nbr.
release order.
end.
find order where order_nbr = input order_nbr exclusive-lock no-error.
/*
.
.
.
*/
release order.

 

[bulklodd]

Is there a better way to do this?

I think the sequences is the best way to do that. What's the problem to use them?

 

[jdgibson]

You could use a sequence but the problem with sequences is that they don't undo if you back out the transaction so you can get gaps in your order number.

If you want no gaps the way to do it is a table which you have used and the control record must be locked exclusively until the order record is committed to the database. The way to avoid other users being unable to create orders is to get the next order number from the control record after the user has entered the order information just before the transaction is committeed. The control record is then only locked for a fraction of a second at the end of the order creation process.

The only disadvatage with this is the user doesn't know the order numnber until he has finished.

 

[bulklodd]

The way to avoid other users being unable to create orders is to get the next order number from the control record after the user has entered the order information just before the transaction is committeed. The control record is then only locked for a fraction of a second at the end of the order creation process.

In that case using the sequence is much better and simpler. if NEXT-VALUE is in the end of the transaction there won't be any gaps.

 

[it@flude.co.uk]

The main problem I have had with sequences is that they require a database change to set them up. When you have many counters that can be used in a system, it is sometimes difficult to justify making database changes for them.

I know that there are now dynamic sequences that get around that problem, which makes them more useful.

 

[bulklodd]

The main problem I have had with sequences is that they require a database change to set them up. When you have many counters that can be used in a system, it is sometimes difficult to justify making database changes for them.

I agree with you but the best way to solve this problem is to develop your own sequence server. You can develop it using P4GL and sockets handles.

I know that there are now dynamic sequences that get around that problem, which makes them more useful.

Unfortunately I don't know about that. Could you shed a light on this topic? As I know there's a possibility to change a sequence value only (OE10) but not to create a new sequence.

 

[jdgibson]

In that case using the sequence is much better and simpler. if NEXT-VALUE is in the end of the transaction there won't be any gaps.

Its only better if you are certain the transaction is going to complete after the NEXT-VALUE is returned. If the order record fails any server side validation such as a missing mandatory field or non unique value in a unique index then the transaction will not compete but the sequence value will have been used.

If you are running client server it is also possible the client will unexpectantly terminiate between the next-value being returned and the transaction completing.

There is also the possibility the database may crash in which case I do not think the sequence will roll back but I am not certain.

If you are generating numbers in which it is a legal requirement that there be no gaps such as invoice number then using a sequence risks a company's accounts department trying to explain to auditors or the revenue and customs where a missing invoice has gone.