Feedback
Did this article resolve your question/issue?

   

Article

Can there be a SHARE-LOCK on a Database Record without an active transaction?

Information

 
TitleCan there be a SHARE-LOCK on a Database Record without an active transaction?
URL NameP6970
Article Number000148225
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
Can there be a SHARE-LOCK on a Database Record when NO TRANSACTION is active?
Can there  be a SHARED-LOCK on a Database Record without any active TRANSACTION?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
Yes, a SHARE-LOCK can exist outside of transactions.

1. A SHARE-LOCK read does not implicitly start a transaction, unless the same code block is also anticipated to update the record.

Compare:
/* ASSIGN is intent to update in same block, this starts transaction */
FIND FIRST customer SHARE-LOCK.
PAUSE MESSAGE "step 1".
ASSIGN 
    customer.comments = customer.comments + ".".
PAUSE MESSAGE "step 2". 
RELEASE customer.
with:
/* ASSIGN is in it's own transaction block. 
Before that block starts, no transaction is active. */

FIND FIRST customer.
PAUSE MESSAGE "step 1".
DO TRANSACTION:
    ASSIGN 
        customer.comments = TRIM(comments,".").
    PAUSE MESSAGE "step 2". 
END.
PAUSE MESSAGE "step 3".
RELEASE customer.
* To confirm the behavior, at each PAUSE in the code check: PROMON > 4.  Record Locking Table and 1.  User Control

2. When the Buffer Scope is wider than the Transaction Scope, the record will be held with a share-lock after the transaction ends.
DEFINE BUFFER bCustomer FOR Customer.
FIND FIRST bCustomer NO-LOCK.
RUN LockIt.

PAUSE MESSAGE "Check 
PROMON > 4.  Record Locking Table and 
PROMON > 1.  User Control".

/* at this point, User Control will report transaction ID 0, 
Record Locking Table still shows previous transaction ID 
but in "None" state to indicate it is now inactive*/

PROCEDURE LockIt:
    FIND FIRST bCustomer EXCLUSIVE-LOCK.

    PAUSE MESSAGE "Check
    PROMON > 4.  Record Locking Table and
    PROMON > 1.  User Control."

/* at this point, User Control will report the same transaction ID as Lock table, 
Lock table also shows transaction is in "Begin" state */

END.
* To confirm the behavior, at each PAUSE in the code check: PROMON > 4.  Record Locking Table and 1.  User Control

The following scenario can cause record locking issues if the record actually gets updated. When the record lock is downgraded, it will retain the (downgraded) flag, which will prevent other clients from obtaining a lock on the record.
DEFINE BUFFER bCustomer FOR Customer.
FIND FIRST bCustomer NO-LOCK.
RUN LockIt.

PAUSE MESSAGE "Check 
PROMON > 4.  Record Locking Table and
PROMON > 1.  User Control". 

/* at this point, User Control will report transaction ID 0, 
Record Locking Table still shows previous transaction ID 
but in "None" state to indicate it is now inactive*/

PROCEDURE LockIt:
    FIND FIRST bCustomer EXCLUSIVE-LOCK.
    bCustomer.comments = bCustomer.comments + ".".
    
    PAUSE MESSAGE "Check
    PROMON > 4.  Record Locking Table and
    PROMON > 1.  User Control".

    /* at this point, User Control will report same transaction ID as Lock table, 
       Lock table also shows that the transaction is in "Begin" state */
END.
Workaround
Notes
Last Modified Date11/20/2020 7:21 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.