EXCLUSIVE-LOCK is not downgraded after transaction with Cile

I expect the network connection is "optimizing" the lock downgrade away.

Try a "VALIDATE tablename" before the message statement and see what it does.

Thanks for the quick response Tim! I tried your suggestion, with the same result. Here's an updated version of the code, in case I misunderstood what you were asking. I still have the EXCLUSIVE-LOCK after the VALIDATE statement. Oh, and forgot to mention that the transaction keyword returns TRUE in the first message and FALSE in the second, as expected.

for first mytable exclusive-lock:
    message "0 " transaction view-as alert-box.
end.

validate mytable.

message "1 " available mytable transaction view-as alert-box.

That is not expected.

But I am assuming you are going to update that record, since you are getting it with exclusive-lock. Once you update the record, the record should be downgraded after the transaction ends.

Interesting. I think it has something to do with the FOR FIRST.

I can reproduce when using the FOR FIRST, but when I change it to FOR EACH with specific criteria to hit only one record it seems to behave properly.

That’s just because of usage of ‘first’ (same with last) on a ‘for’ loop, looks like a bug to everyone probably at first encounter but then everyone at PSC will say it’s expected behaviour and that will probably never change mostly due backward compatibility thingy.

The scope of the buffer just go one level higher than the enclosing for loop, that’s why if you put a do ‘nothing’ loop around the ‘leaking’ is fixed.

Marian Edu

Acorn IT

www.acorn-it.com

www.akera.io

+40 740 036 212

So I just tried updating the record in the FOR block and Fernando was correct, the lock is downgraded. So, this example works as I would expect it to:

for first mytable exclusive-lock:
    mytable.myfield = mytable.myfield + "123".
    message "0 " transaction view-as alert-box.
end.

message "1 " available mytable transaction view-as alert-box.

Just another example, I can also reproduce this bug with a FOR EACH that includes a LEAVE. The following example will cause the EXCLUSIVE-LOCK to be retained after the FOR block (although adding an actual update in this case also resolves the issue):

for each mytable exclusive-lock:
    message "0 " transaction view-as alert-box.
    leave.
end.

message "1 " available mytable transaction view-as alert-box.

I will repeat it - it is not expected for that code to behave differently based on the connection type.

Thanks, I'll look at opening a case with support. I was just pointing out that I tried it and you were right ;).

I was just responding to a previous comment stating that PSC would say this is expected. :)

I am surprised Tom hasn't jumped in here yet to lecture you about FOR FIRST, so I guess I will have to do it.  FIRST is a red flag because it implies that there is a set of records and you are treating the first one differently, which is a violation of normal form.  If, in fact, there is not actually a set and you are really finding a unique record, then FIRST sends a confusing message to anyone trying to maintain the code.  Moreover, it provides no benefit.  So, don't.

FOR FIRST is even worse -- the implied order is very misleading.  Since only one record is ever returned the sort phase does nothing.

If I could pick one feature to remove from the 4GL "for first" would be a very strong candidate.

Being a long time reader, I am aware of Tom's distate for FOR FIRST :). I'll take a look at the actual context in which this problem came up, and perhaps we can improve the code. Despite the validity of your lecture, this still looks like a bug :)

the downgrade is buffered on the client side.

mary and i used to argue about this behaviour but she wouldn’t change it for good reasons that i no longer remember.

That’s just because of usage of ‘first’ (same with last) on a ‘for’ loop, looks like a bug to everyone probably at first encounter but then everyone at PSC will say it’s expected behaviour and that will probably never change mostly due backward compatibility thingy. The scope of the buffer just go one level higher than the enclosing for loop, that’s why if you put a do ‘nothing’ loop around the ‘leaking’ is fixed.

Thanks for the replies everyone, defect PSC00356589  has been opened for this.

I tested this scenario with just a FOR statement (i.e. no FIRST) and the same behaviour exists. You probably expected that, since you weren't suggesting this as a workaround/fix for the issue, but just thought it was worth following up and sharing what I found :).

So this code demonstrates the same behaviour:

for mytable exclusive-lock where mytable.uniquekey = "value":
    message "0" view-as alert-box.
end.

message "1" available mytable view-as alert-box.

Actually, I did a bit more reading and playing around, and I'm hoping Thomas and/or Tom (at least) can chime in here. What I'm finding is that "FOR buffer", when multiple records exist that meet the search criteria,  does not behave as "FIND buffer" does. "FOR buffer" actually does return a record, and seems to behave exactly like "FOR FIRST buffer" does; though I don't have enough evidence to really verify that "FOR buffer" always retrieves the FIRST record. But here's the example I tried:

define variable r1 as rowid no-undo.
define variable r2 as rowid no-undo.

for Individual where FirstName = "Chris" no-lock:
    r1 = rowid(Individual).
end.

for first Individual where FirstName = "Chris" no-lock:
    r2 = rowid(Individual).
end.

display (r1 = r2).

There are 3 Individual records, in my database, with FirstName = "Chris". The output of that display statement is:

yes

I expected this to behave more like "FIND FIRST" and return an error, or maybe nothing, if the result was ambiguous like this. Reading through the documentation of the FOR statement, I'm not uncovering an explicit answer. Is this how you thought it would work? I'm guessing not...If I'm wrong and you did expect this, then why suggest I not use the FIRST keyword.

Thoughts?