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 t
here 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, w
hich 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 27955Where:
- 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.
