Question DB Locks - _Lock VST vs promon

[ron]

Yes Rob, I am aware of that. I actually take a bit more just because of that. I am only interested in troublesome locks, in particular those that have been around "a bit too long" so I can identify programs that are holding locks too long - most likely due to the omission of a RELEASE command (or two).

 

[TomBascom]

I seriously doubt that RELEASE has much to do with it.

 

[ron]

There are many reason for locks hanging around a long time - and imho they are almost always due to poor programming in one way or another. I fairly often find that it's because of a missing release. Some programmers think that at the end of a transaction all locks "go away".

 

[TomBascom]

It is a popular misconception that RELEASE releases locks.

RELEASE statement

Verifies that a record complies with mandatory field and unique index definitions. It clears the record from the buffer and unites it to the database if it has been changed.

The problem that you describe is more a matter of a mismatch between record scope and transaction scope. Updates to records should always be strong scoped (use the FOR statement...) and you will never need to bother with RELEASE. You will need to pay a lot more attention to record scope. But IMHO that's a good thing.

 

[ron]

It was my understanding that when a transaction completes - any exclusive locks are downgraded to shared locks - and those locks will persist until the records go out of scope.

If a program does nothing further with records for that (or those) record(s) but instead waits for an event of some kind - I thought that only a release would clear those shared locks. Is that not so?

 

[TomBascom]

It was my understanding that when a transaction completes - any exclusive locks are downgraded to shared locks - and those locks will persist until the records go out of scope.

Correct.

And the "root cause" is a mismatch between record scope and transaction scope.

If a program does nothing further with records for that (or those) record(s) but instead waits for an event of some kind - I thought that only a release would clear those shared locks. Is that not so?

This snippet: "... and then executes a RELEASE statement. This ensures that the lock is released when the transaction ends." from the example in the documentation is a bit confusing and gives people the wrong idea.

Fiddle with the code below to see that when you RELEASE it the lock is still there -- until the transaction ends. If you RELEASE outside the TRX it is much less clear but you are not really doing anything special with the lock per se -- you are letting go of the buffer.

/* release.p
 */

define variable r as integer no-undo.

find first customer no-lock.  /* record scope is now the procedure block */
r = recid( customer ).

find _lock no-lock where _lock-recid = r no-error.
display
  available customer
  _lock-flags when available _lock
  "NL" when not available _lock @ _lock-flags
 with
  frame pre-trx
  title "Pre TRX"
  no-labels
  row 1
  column 1
.

do transaction:

  find current customer exclusive-lock.

  update name.

  release customer.

  find _lock no-lock where _lock-recid = r no-error.
  display
  available customer
  _lock-flags when available _lock
  "NL" when not available _lock @ _lock-flags
  with
  frame in-trx
  title "In TRX"
  no-labels
  row 1
  column 25
  .

end.

/* release customer. */

find _lock no-lock where _lock-recid = r no-error.
display
  available customer
  _lock-flags when available _lock
  "NL" when not available _lock @ _lock-flags
 with
  frame post-trx
  title "Post TRX"
  no-labels
  row 1
  column 50
.

pause.

 

[TomBascom]

Contrast that with:

/* release2.p
 */

define variable r as integer no-undo.

define buffer upd_cust for customer.

find first customer no-lock.  /* record scope is now the procedure block */
r = recid( customer ).

find _lock no-lock where _lock-recid = r no-error.
display
  available customer
  _lock-flags when available _lock
  "NL" when not available _lock @ _lock-flags
 with
  frame pre-trx
  title "Pre TRX"
  no-labels
  row 1
  column 1
.

do for upd_cust transaction:

  find upd_cust exclusive-lock where recid( upd_cust ) = r no-error.

  update upd_cust.name.

  find _lock no-lock where _lock-recid = r no-error.
  display
  available customer
  _lock-flags when available _lock
  "NL" when not available _lock @ _lock-flags
  available upd_cust
  with
  frame in-trx
  title "In TRX"
  no-labels
  row 1
  column 25
  .

end.

find _lock no-lock where _lock-recid = r no-error.
display
  available customer
  _lock-flags when available _lock
  "NL" when not available _lock @ _lock-flags
 with
  frame post-trx
  title "Post TRX"
  no-labels
  row 1
  column 50
.

pause.

In this second example the lack of a RELEASE statement (or adding one) has no impact. The update, the locked record and the associated transaction are all tightly confined to a FOR block. No leakage is possible and if you mistakenly reference the wrong buffer in the wrong place the compiler will make it very clear to you without any need to go traipsing through _LOCK or PROMON looking for mysterious excess locks.

 

[TomBascom]

BTW -- the "upd" is the original intent of "Hungarian Notation". Before it got corrupted into the useless and unreadable gibberish that so many people have inflicted upon otherwise (presumably) innocent code. "Systems Hungarian" -- possibly the worst of a nearly infinite set of bad ideas to come out of Redmond. One so bad that even Microsoft tells people to stop doing it.