Locking scope

Posted by Jens Dahlin on 03-Jul-2019 12:04

I have a locking issue where it seems that one query is locking a record used in another (concurrent) query. The locking issue definitely occurs and is also nicely handled with NO-WAIT etc. However looking at the queries it seems that the locked record should not be in scope in the first query. The where clauses used should see to that. 

Can the locking "scope" be bigger than the actually returned records? Is this dependant on the index usage in the query or something else? XREF compile show at least decent, if not perfect, index usage in the respective queries.

Concurrency is handled by the appserver and async calls. OE11.7.4 on Ubuntu Linux.

All Replies

Posted by 2087 on 03-Jul-2019 12:39

I believe so - I sure I experienced this many years ago in Progress 7.

If the indexes don't allow for the server to read only the records you want it will have to look at the records themselves to establish whether they are wanted in the query results and it locks them whilst doing this.

The way to avoid it is change the indexes and/or query so that only the records that you want are read (which could be difficult) - or read them NO-LOCK and then have to read them again (by ROWID or similar) to lock them (which could be slow).

Hope that helps.

Derek

Posted by gus bjorklund on 03-Jul-2019 13:16

this is a large topic, discusssed in the 4GL programming handbook and in multiple kbase articles and user conference talks.

lock scope is related to buffer scope and transaction scope. either can be larger than the other. while compiler chooses the boundaries automatically, both can be controlled explicitly in the code.

often, buffer scope and transaction scope boundaries are the same, but not always. a common mistake is to have both be scoped to the procedure boundaries.

This thread is closed