George Potemkin
Member
I would like to programmatically bind _Connect-Wait to _Resrc-Name. Basically _Connect-Wait returns a short name of resource queue a process is waiting for. _Resrc-Name is a full name of the same resource. So the list of the names would be enough. Both match to the wait codes:
%GSYSTEM ERROR: unexpected wakeup from wait code %i, user %i (1007)
%GSYSTEM ERROR: invalid wait code %i (1023)
Documentation provides such list, but it is partially incorrect and partially outdated.
Table 2. Possible wait column values
docs.progress.com
Shared Memory Locks/Latches: User Control Codes in PROMON
knowledgebase.progress.com
What are the wait codes and what do they mean for error 1007?
community.progress.com
Progress executables, of course, should know these names. I found three lists:
Above is just my guess who is who. The lists differ in their numbers. Some names are different. The order of names is different as well.
We can get the lists of the full names using _Resrc and promon/R&D/6/8. Resource Queues. These names are also slightly different.
One more way to get the list is the tests but some resource waits is hard to reproduce. Especially if they are not implemented or not used.
Putting all together:
"Wait Info" column (_Connect-Wait1 + _Connect-Wait2) is based on my tests and on the Blocked Clients screens from production.
Documentation states, we may also see the latch names listed in the Wait column. In fact it's true only for the queued latch type (MT_LT_QUEUE) when database is started with nonzero -spin value - namely USR and SCC (a,k.a. SCH) latches.
Wait Info for USR latch is "0:0".
SCC/SCH latch is related but is not the same as SCH (Schema Lock) resource queue. SCH latch is locked a hundred times per login, the same number per logout and many times while compiling r-code. SCH resource queue is exclusively locked when a session is going to create or update a record of metaschema table. When a session run r-code it puts share-lock on database schema. Session will wait for the locked SCH resource queue (Wait Info = "9:0"). _Resrc-wait will be incremented but the shared schema lock does not increment _Resrc-lock.
In my tests _Connect-Wait returns "SCH" and it was the Schema Lock resource queue. Could _Connect-Wait return the latch name? If yes then would it be "SCC" or "SCH" (the same name as resource queue)? First one is the _Latch-Name, the second is the name of the same latch used in promon.
"Shared Memory" resource queue does not have a short name. Neither _Connect, nor promon can read shared memory when it's locked. The processes can wait for the "Shared Memory" lock but we just can't see these waits.
Dan Foreman's VST Guide states that "DB Buf Backup" resource queue a.k.a. "DBBK" was not implemented.
Description of "DB Buf Avail" (a.k.a. "NOBF" or "BUFF") links it to LRU chain but resource is not copied for LRU2. I never saw such waits in production. Even if it's implemented, IMHO, it's very unlikely to get this type of waits.
"DB Buf * Lock LRU2". They seem to be missed in the list of _Connect-Wait. DB Buf Locks do not seem to be related to the chains in buffer pool. Progress database has only one buffer pool divided in many chains. Two largest chains are named "Primary Buffer Pool" and "Alternate Buffer Pool" but it's just the names. My guess - BKS2, BKX2 and DBW2 are not implemented.
"AI Buf Read" a.k.a. "AIRD". Only database broker reads AI files and it reads them only at database startup. There are no other processes at this phase that could wait for the locked AI files. My guess: this resource queue may be implemented but it does not used.
"TXE Excl Lock" a.k.a. "TXE" or "TXEX" does not seem to be implemented (unlike other TXE locks).
Wait Info of "Statement Cache" (a.k.a. "STCA") retuns "number":0. Just curious - what the number means?
Can anyone confirm or deny my assumptions?
Does my list of names returned by _Connect-Wait (in the table above) correct?
%GSYSTEM ERROR: unexpected wakeup from wait code %i, user %i (1007)
%GSYSTEM ERROR: invalid wait code %i (1023)
Documentation provides such list, but it is partially incorrect and partially outdated.
Table 2. Possible wait column values
Progress Documentation
docs.progress.com
Shared Memory Locks/Latches: User Control Codes in PROMON
Progress Customer Community
What are the wait codes and what do they mean for error 1007?
Progress Customer Community
Progress executables, of course, should know these names. I found three lists:
Code:
Promon/1. User Control (21 names):
REC SCHE TRAN DBRD DBWR DBBK BIRD BIWR AIWR BUFF RGET BKSH BKEX
TXS TXB TXE TXX SRPL DBSQ STCA ENCB
Promon/R&D/1/4/2. Status: Blocked Clients (26 names):
REC SCH TRAN DBSI RGET DBRD DBWR DBBK BKSH BKEX NOBF BKS2 BKX2 DBW2
BIRD BIWR AIRD AIWR TXES TXEU TXEC TXEX SRPL DBSQ BTDE STCA
_Connect-Wait (21 names):
REC SCH TRAN DBSI RGET DBRD DBWR DBBK BKSH BKEX BIRD BIWR AIRD AIWR
TXS TXB TXX TXE SRPL DBSQ STCA ENCB UNKNWe can get the lists of the full names using _Resrc and promon/R&D/6/8. Resource Queues. These names are also slightly different.
One more way to get the list is the tests but some resource waits is hard to reproduce. Especially if they are not implemented or not used.
Putting all together:
Code:
c# wait _Resrc-Name promon/Blocked Clients Wait Info Description
-- ---- ------------------ ---- ------------------ ------------ ----------------------
0 Shared Memory Shared Memory Only if promon - 2. Enable latch activity data collection
1 REC Record Lock REC Record Lock recid:table#
2 SCH Schema Lock SCH Schema Lock lock#:0 Request to make a schema change. lock#: 9=share, 10=exclusive
3 TRAN Trans Commit TRAN Trans Commit TRID:0 Requests to delete a unique key
4 DBSI DB Buf I Lock DBSI DB Buf I Lock dbkey:area# Intent upgrade on index block
5 RGET Record Get RGET Record Get recid:table#
6 DBRD DB Buf Read DBRD DB Buf Read dbkey:area#
7 DBWR DB Buf Write DBWR DB Buf Write dbkey:area#
8 DBBK DB Buf Backup DBBK DB Buf Backup Not implemented
9 BKSH DB Buf S Lock BKSH DB Buf S Lock dbkey:area#
10 BKEX DB Buf X Lock BKEX DB Buf X Lock dbkey:area#
11 DB Buf Avail NOBF DB Buf Avail BUFF? Replacement buffer was needed for the LRU chain but all buffers in the cache were locked
12 DB Buf S Lock LRU2 BKS2 DB Buf S Lock LRU2
13 DB Buf X Lock LRU2 BKX2 DB Buf X Lock LRU2
14 DB Buf Write LRU2 DBW2 DB Buf Write LRU2
15 BIRD BI Buf Read BIRD BI Buf Read BI Buffer read queue during transaction undo
16 BIWR BI Buf Write BIWR BI Buf Write block#:0
17 AIRD AI Buf Read AIRD AI Buf Read Broker reads AI files at startup
18 AIWR AI Buf Write AIWR AI Buf Write
19 TXS TXE Share Lock TXES TXE Share Lock 1:0
20 TXB TXE Update Lock TXEU TXE Update Lock 2:0
21 TXX TXE Commit Lock TXEC TXE Commit Lock 3:0
22 TXE TXE Excl Lock TXEX TXE Excl Lock Not used?
23 SRPL Repl TEND Ack SRPL Repl TEND Ack Replication Transaction End Acknowledgment from a Sync Replication Agent
24 DBSQ DB Svc enqueue DBSQ DBSQ Send Lock Database service queue message wait
25 ENCB Encryption buffer BTDE TDE Buffer Spare buffer for encryption/decryption
26 STCA Statement cache STCA Statement Cache number:0 Statement caching wait
27 Priv Buf S Lock Lock 27"Wait Info" column (_Connect-Wait1 + _Connect-Wait2) is based on my tests and on the Blocked Clients screens from production.
Documentation states, we may also see the latch names listed in the Wait column. In fact it's true only for the queued latch type (MT_LT_QUEUE) when database is started with nonzero -spin value - namely USR and SCC (a,k.a. SCH) latches.
Wait Info for USR latch is "0:0".
SCC/SCH latch is related but is not the same as SCH (Schema Lock) resource queue. SCH latch is locked a hundred times per login, the same number per logout and many times while compiling r-code. SCH resource queue is exclusively locked when a session is going to create or update a record of metaschema table. When a session run r-code it puts share-lock on database schema. Session will wait for the locked SCH resource queue (Wait Info = "9:0"). _Resrc-wait will be incremented but the shared schema lock does not increment _Resrc-lock.
In my tests _Connect-Wait returns "SCH" and it was the Schema Lock resource queue. Could _Connect-Wait return the latch name? If yes then would it be "SCC" or "SCH" (the same name as resource queue)? First one is the _Latch-Name, the second is the name of the same latch used in promon.
"Shared Memory" resource queue does not have a short name. Neither _Connect, nor promon can read shared memory when it's locked. The processes can wait for the "Shared Memory" lock but we just can't see these waits.
Dan Foreman's VST Guide states that "DB Buf Backup" resource queue a.k.a. "DBBK" was not implemented.
Description of "DB Buf Avail" (a.k.a. "NOBF" or "BUFF") links it to LRU chain but resource is not copied for LRU2. I never saw such waits in production. Even if it's implemented, IMHO, it's very unlikely to get this type of waits.
"DB Buf * Lock LRU2". They seem to be missed in the list of _Connect-Wait. DB Buf Locks do not seem to be related to the chains in buffer pool. Progress database has only one buffer pool divided in many chains. Two largest chains are named "Primary Buffer Pool" and "Alternate Buffer Pool" but it's just the names. My guess - BKS2, BKX2 and DBW2 are not implemented.
"AI Buf Read" a.k.a. "AIRD". Only database broker reads AI files and it reads them only at database startup. There are no other processes at this phase that could wait for the locked AI files. My guess: this resource queue may be implemented but it does not used.
"TXE Excl Lock" a.k.a. "TXE" or "TXEX" does not seem to be implemented (unlike other TXE locks).
Wait Info of "Statement Cache" (a.k.a. "STCA") retuns "number":0. Just curious - what the number means?
Can anyone confirm or deny my assumptions?
Does my list of names returned by _Connect-Wait (in the table above) correct?
