record locking problem

[RayMcManus]

Hi,

I'm having a problem with a piece of code that is supposed to allow more than one user to copy a record from the same screen at the same time. It should look at the last_ord_num variable and automatically increase the order number by one every time another user goes into the screen. The number increases, but keeps the record locked for some reason (even if I put a no-lock command right after the assign), so any other user would get an error message.


Here's the procedure:



define variable order-count as integer no-undo.
define variable assigned-order as logical no-undo.

find first order_ref exclusive-lock no-error.
if not(available(order_ref)) then create order_ref.

assign
stat = true
assigned-order = false.

if order_ref.use_inv_ordnum then
useinvblock:
do:

assign
assigned-order = false.

find inventory where
inventory.stock_num eq order.part_num:screen-value in frame {&active_page} and
inventory.revision eq order.part_rev:screen-value in frame {&active_page} and
inventory.type eq "F" no-lock no-error.

if not(available(inventory)) then
leave useinvblock.

if inventory.ord_num ne "" then
do:

findnextloop:
repeat order-count = 1 to 10000:
find alt-order where
alt-order.ord_num eq inventory.ord_num + "-" + string(order-count)
no-lock no-error.
if not(available(alt-order)) then
leave findnextloop.
end.

if order-count ne 10000 then
assign
order.ord_num = inventory.ord_num + "-" + string(order-count)
order.ord_num:screen-value in frame {&active_page} = string(order.ord_num)
assigned-order = true.
end.

end.

if not(assigned-order) then
do:

do while stat:
assign last_ord_num = last_ord_num + 1.
if not can-find(order where order.ord_num = string(last_ord_num))
then stat=false.
end.

assign
order.ord_num = string(last_ord_num).
order.ord_num:screen-value in frame {&active_page}=string(order.ord_num).
end.



Thanks,
Ray

 

[MurrayH]

Wow .. use the _sequence table .. thats what its for!!

Murray

 

[RayMcManus]

Well, I wanted to work with it the way that it is, instead of making it a sequence. I thought it would be just a matter of unlocking it at a certain place. What's weird is that if I tell it "find current order_ref exclusive-lock" right before the assign statement, then "find current order_ref no-lock" right after the assign statement it still keeps it locked. I have no triggers set up for order_ref.

 

[jamesmc]

Hi there Ray,

Try finding the record within a 'do transaction' block with exclusive-lock then assign the new value so it should read something like:

<HR>do transaction:
&nbsp;&nbsp;&nbsp;&nbsp;find order_ref EXCLUSIVE-LOCK.
&nbsp;&nbsp;&nbsp;&nbsp;assign order_ref.order_num = new_num.
end.
<HR>I know it doesn't use the exact field names etc but you get the idea!

If that doesn't work try adding a release statement in there after the update and see if that helps.

HTH,

James.

 

[RayMcManus]

Hi James,

Thanks for your help - unfortunately, I tried them both, and they don't seem to work. I'm thinking maybe it's something in another piece of code keeping it locked. I'll play around with it some more, and maybe I'll suddenly figure it out.

Ray

 

[MurrayH]

You have to remember that the lock will be help for the WHOLE transaction .. not just until you do a release. I'd create a buffer and try this:

def bufer b for order-ref.
def var i as int no-undo.

repeat i = 1 to 1 transaction:
find first b exclusive-lock no-error.
assign b.order_num = new_new.
end.

Its a bit hard to see without the whole program. Try a "compile listing" and look at the transaction scopes.

Murray

 

[mra]

Hello MurrayH!

:scan: I'm curious, why do you suggest creating a new buffer for the table? Does this have an impact on the way Progress locks tables?

Thanks

Mike

 

[MurrayH]

A buffer simply gives you a separate pointer into the database table. I tend to use buffers when I want to lock something. It makes it easier to track through the code .. when you see a bLockCustomer .. you know that the table will be locked. Of course its only as good as the programmers so its not 100% fail safe.

Murray

 

[RayMcManus]

Thanks a lot - I'll give it a try.

 

[greuceanu]

Hi Ray,

The record is locked as long the block containing your code is executed, supposing you're not inside another transaction.

The do transaction solution didn't work because the buffer was used outside the transaction scope, which made Progress to downgrade the lock to share-lock. This is called record-bleeding.

Re-organize your code so that the record is locked / created for the shortest time possible. Why did you lock the record in the beginning anyway since you never updated, you just test the use_inv_ordnum field?

Then try the following:
-define a buffer for the order_ref.
-write someting like:

do for b transaction:
... exclusive-lock
... create
end.

- if you really need to refer the buffer outside the transaction scope you have two solutions:
1. def some vars and assign the necessary values to them.
2. do something like:

do transaction:
... exclusive-lock
... create
end.
find first order_ref no-lock.

This should remove the share-lock.

TIP: Always use first with find statement when you are locating a unique record. If you don't use it Progress makes two operations:
- one locates the first record that matches your criteria.
- tries to find another one to ensure the record is unique.


Take care,
Greuceanu.

 

[RayMcManus]

Hi,

Thanks - I gave up though and just made it a sequence...everything works okay now.

Ray