Feedback
Did this article resolve your question/issue?

   

Article

How to use PROMON to detect a deadly embrace?

Information

 
TitleHow to use PROMON to detect a deadly embrace?
URL NameP130709
Article Number000128818
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
How to use PROMON to detect a deadly embrace?
Using PROMON to find which users have a record locked in a deadly embrace.
How to detect a deadlock?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
A deadlock occurs when two separate process are each waiting for the other to release a resource. Two user sessions could potentially attempt to lock the same record at the same time as there is no deadlock detection algorithm in the database lock manager.  This is commonly referred to as a deadly embrace or deadlock and raises the following errors, which will not only be from record locks, but also on any attempt by one user to modify the schema while any NO-LOCK record access to the database is active.
             
<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop. (2624)

or
Database in use by <user> on <tty>. Wait or choose CANCEL to stop. (2623)

Querying the _Lock VST tables in this case, will not show users accessing records with NO-LOCK. Understand you're not ever going to get the same information from VST as you do from PROMON. The two methods are entirely different:
  • PROMON exploits the ability to 'snapshot' shared memory currently in use.
  • ABL code, does not snapshot and all PROMON data are not exposed through VST tables.
There are a number of methods to program around a deadlock, in order to have the application handle the situation more gracefully by programmatically detecting which user currently has the record locked.

As OpenEdge has no built in “deadlock detection”, it depends upon Lock Wait Timeouts to end deadlock conditions that would otherwise persist indefinitely.  Lock Wait timeouts have different defaults based on the client type being used:
  • An ABL client and Database utilities use the Lock Wait Timeout parameter (-lkwtmo) so that conditions raising deadlocks will not last forever. The default value when not specified is 30 minutes (1800 seconds). When the timeout value is reached, the lock request/queued lock will time out and disappear, the client that was queuing will then run on with no record available and all the application consequences which follow as a result if not handled by the application code.
  • A SQL client has a default value of 5 seconds, which can be changed by setting and exporting the PROSQL_LOCKWAIT_TIMEOUT environment variable to the new lock wait timeout value prior to starting the database server.
This Article provides one method of determining which user has the record locked and to see a list of users that are currently contending for the same record, using PROMON.

From the main menu in PROMON, select Option 4 Record Locking Table then select Option 1 Display all entries.  

The following example shows the simplest form of a deadlock or deadly embrace. More complex situations are for example between multiple users where user A, B, C  are in a circular deadlock. 

In the following ABL code example against the sports2000 sample database, start both client sessions with the following ABL code, when they're both pausing, break the pauses.

The PROMON recids may be different, but will show both sessions waiting on the other while the 2624 error triggers.
 
/* session 1: */
FIND FIRST customer EXCLUSIVE-LOCK. 
PAUSE. 
FIND LAST customer EXCLUSIVE-LOCK.
PAUSE.
 
/* session 2: */
FIND LAST customer EXCLUSIVE-LOCK.
PAUSE.
FIND FIRST customer EXCLUSIVE-LOCK. 
PAUSE.

(OpenEdge 11.4)                                  
Record Locking Table: by user number for all tenants                                      
Usr:Ten Name        Domain Chain #      Rowid Table:Part  Lock Flags   Tran State  Tran ID
  5     <username1>     -3 REC      99     97     2       EXCL  L      Begin   FWD   27954
  5     <username1>     -3 REC     128   1363     2       EXCL   Q H   Begin   FWD   27954
  6     <username2>     -3 REC      99     97     2       EXCL   Q H   Begin   FWD   27955
  6     <username2>     -3 REC     128   1363     2       EXCL  L      Begin   FWD   27955


Where:
  • User 5 has an exclusive lock on recid 97, and waits for recid 1363
  • User 6 has an exclusive lock on recid 1363, and waits for recid 97
  • The flags "Q" and "H" on the second lock indicate that the lock is queued (Q) and the lock is in hold (H) status.

Another common scenario can occur when two users take share-locks on a record then both attempt to modify the records.

  Usr:Ten   Name        Domain     Trans id Type   Table:Part                  ROWID Flags        Trans State

    6       <username1>      -3       28010  REC        2:0                       97 X    U Q        Begin   FWD
    7       <username2>      -3       28011  REC        2:0                       97 X    U Q        Begin   FWD

In the above example the Lock Flags are XUQ.  Which means both users are QUEUED attempting to UPGRADE to EXCLUSIVE locks.
 
Workaround
Notes
Last Modified Date11/20/2020 7:30 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.