EXCLUSIVE-LOCK is not downgraded after transaction with Cile

Posted by chris_riddell on 10-Apr-2017 15:58

Hi there,

Long time reader, first time poster here. I've found what I think is a bug, but was hoping to get some input from the community.

What I'm seeing is a case where an EXCLUSIVE-LOCK is not being downgraded to a SHARE-LOCK, at the end of a transaction. The following is the code I'm experimenting with:

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

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

I'm using the message statements as pauses, so I can use promon to examine lock states. Including "available mytable" is necessary because it extends the scope of the mytable buffer; without it the EXCLUSIVE-LOCK is released, as expected, at the end of the FOR block.

The interesting bit is that the lock is downgraded properly if I'm connected to the database via shared memory. If I'm connected client/server, then I still see an EXCLUSIVE-LOCK when my "message 1" displays.

If I enclose the FOR block in a DO TRANSACTION block, the downgrade also behaves as expected (in all cases).

Edit: This has been re-produced on 10.2B and 11.6.3

Any insights?

Thanks,

Chris

Posted by Fernando Souza on 10-Apr-2017 16:34

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.

All Replies

Posted by Tim Kuehn on 10-Apr-2017 16:05

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

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

Posted by chris_riddell on 10-Apr-2017 16:19

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.
Posted by Fernando Souza on 10-Apr-2017 16:34

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.

Posted by jbijker on 11-Apr-2017 03:28

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.

Posted by marian.edu on 11-Apr-2017 06:49

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

Posted by chris_riddell on 11-Apr-2017 08:09

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.

Posted by Fernando Souza on 11-Apr-2017 08:20

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

Posted by chris_riddell on 11-Apr-2017 08:24

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

Posted by Fernando Souza on 11-Apr-2017 08:29

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

Posted by Thomas Mercer-Hursh on 11-Apr-2017 09:09

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.

Posted by ChUIMonster on 11-Apr-2017 09:26

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.

Posted by chris_riddell on 11-Apr-2017 10:06

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 :)

Posted by gus bjorklund on 11-Apr-2017 21:30

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.

Posted by marian.edu on 11-Apr-2017 21:30
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 
+40 740 036 212

Posted by chris_riddell on 12-Apr-2017 08:06

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

Posted by chris_riddell on 12-Apr-2017 08:09

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.
Posted by chris_riddell on 12-Apr-2017 15:36

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?

This thread is closed