Why does a record still have a SDL lock when a new transaction starts?

Why does a record still have a SDL database record lock state after the transaction ends preventing it from being modified by another user?

Why does a SDL lock remain after the transaction scope has ended preventing the record from being updated or deleted?

Records updated in a TRANSACTION block retain a SHARE-LOCK until the transaction ends and record scope ends.

When there is no TRANSACTION, records cannot be modified by other users until the transaction that updated these records ends and the record scope ends

Why does a record still have a share-lock when 'it's' transaction has ended and the SDL persists when the next transaction is started which loads another record into the same record buffer.

What does SDL stand for as a database record lock state?

 

The flag "S DL" is for Share lock Downgrade Limbo. The lock is released when transaction ends and record scope ends, which may be procedure end or sooner.

The following example against the sports2000 database demonstrates:
 

DEFINE VARIABLE v-recid AS int NO-UNDO. 

DO TRANSACTION /* TRANSACTION_1*/: 
FIND customer WHERE customer.CustNum = 1 EXCLUSIVE-LOCK NO-ERROR. 
v-recid = Int(RECID(customer) ). 
customer.Address = customer.Address + "." . 
PAUSE.
END. 

FOR EACH sports2000._lock WHERE _lock._Lock-RecId = Int(v-recid) NO-LOCK , 
FIRST sports2000._file WHERE _file._File-Number = _lock._Lock-Table NO-LOCK : 
MESSAGE _lock._Lock-Flags _lock._Lock-Name _lock._Lock-Table _file-name SKIP
customer.CustNum TRANSACTION
VIEW-AS ALERT-BOX INFO BUTTONS OK. 
END. 


DO TRANSACTION /* TRANSACTION_2*/: 
FIND customer WHERE customer.CustNum = 2 EXCLUSIVE-LOCK NO-ERROR. 
FOR EACH sports2000._lock WHERE _lock._Lock-RecId = Int(v-recid) NO-LOCK , 
FIRST sports2000._file WHERE _file._File-Number = _lock._Lock-Table NO-LOCK : 
MESSAGE _lock._Lock-Flags _lock._Lock-Name _lock._Lock-Table _file-name SKIP
customer.CustNum TRANSACTION
VIEW-AS ALERT-BOX INFO BUTTONS OK. 
END. 
END.

PROMON: 10.2B - 11.6 

Record LockingTable:by usernumber
Usr:TenName Domain  Chain#     Rowid Table:Part LockFlags  TranState  TranID
5   user1      -3   REC  303   1538      2      EXCL       Begin FWD  28019

5   user1      -3   REC  303   1538      2      SHR D      None FWD   28019

5   user1      -3   REC  302   1537      2      EXCL       Begin FWD  28020
5   user1      -3   REC  303   1538      2      SHR DL     None FWD   28019

1. The exclusive lock is downgraded to a share-lock (S D) on the customer record (1538) after the transaction [1] ends because the record buffer scope is wider than the transaction scope.

When the next TRANSACTION [2] begins, the new customer record is in the record buffer (exclusive).
The share-lock persists as share-lock downgraded limbo (S DL) once the record in the buffer changes ("WHERE customer.CustNum = 2")

2. The share-lock prevents another user changing the record until the procedure ends:
 
3. Putting it all together: If you do not need to do another update on this record later in the procedure, RELEASE the record (or FIND CURRENT NO-LOCK) after the transaction.
        
For example when "RELEASE customer." is added, TRANSACTION 1 scope is wider than the record buffer scope.
Another user is then able to change the CustNum(1) record once TRANSACTION 1 ends and before the procedure ends.
 

Progress Articles:

 Explanation of the Lock Table flags in promon   
 Can there be a SHARE-LOCK on a Database Record without an active transaction?