Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Lviv, Ukraine
    Posts
    2

    Unanswered: Progress DB - locking problem

    Hi!

    I need to exclusively lock multiple records at the same time (pure 4GL):
    I have some table "table" with the unique index containing fields "f1", "f2", "f3".
    I need to lock all records where "f1 = value1 and f2 = value2".
    In two words, I need something like:
    for each table where f1 = value1 and f2 = value2 exclusive-lock:
    .....
    end.
    but all records should be locked.
    This code suppose to get executed in a persistent procedure and the goal is to not allow other users update/delete locked recordset.

    I'll be very thankful for any help.

  2. #2
    Join Date
    Nov 2003
    Location
    Lomza, POLAND
    Posts
    81
    Hi,
    Try this:

    l_tr:
    do transaction
    on error undo, leave
    on stop undo, leave:

    for each table
    where f1 = value1 and
    f2 = value2 and
    f3 = value3
    exclusive
    on error undo l_tr, leave l_tr
    on stop undo l_tr, leave l_tr:

    end.

    message "The records are being locked !"
    view-as alert-box message.
    /* now all the records are eXclusively locked */

    end. /* l_tr */
    /* here the locks are released */

  3. #3
    Join Date
    Jun 2004
    Location
    Lviv, Ukraine
    Posts
    2
    Thank you for the answer, but I've just found more effective way tha allows you to keep lock as long as you want (in a persistent procedure):

    /* the table containing locked buffers */
    define temp-table t-lock
    field buf-handle as handle.

    /* lock multiple records*/
    for each table where f1 = value1 and f2 = value 2 no-lock:
    create t-lock.
    create buffer buf-handle for table "table".
    buf-handle:find-by-rowid(rowid(table), exclusive-lock).
    end.

    /* unlock multiple records*/
    for each t-lock:
    delete object buf-handle.
    delete t-lock.
    end.

  4. #4
    Join Date
    Nov 2003
    Location
    Lomza, POLAND
    Posts
    81
    Hi again
    Your interesting case encouraged me to perform some experiments. You can take all advantages of the dynamic extensions (introduced with Progress version 9) and think about the universal persistent procedure, which can apply locks to any table (from database)using any condition (too much 'any' ? :-). Here is the example:

    /***********************************************/

    /* Locker.p */

    define temp-table ttLock
    field hRec as HANDLE.

    PROCEDURE lockRecords:
    DEF INPUT PARAM tableName AS CHAR NO-UNDO.
    DEF INPUT PARAM condition AS CHAR NO-UNDO.
    DEF INPUT PARAM lockType AS CHAR NO-UNDO. /* S or X */

    DEF VAR ret AS CHAR NO-UNDO INIT "NOT-OK".

    DEF VAR qh AS HANDLE.
    DEF VAR bh AS HANDLE.
    DEF VAR expr AS CHAR NO-UNDO.

    proc:
    DO TRANSACT
    ON ERROR UNDO, LEAVE
    ON STOP UNDO, LEAVE:
    CREATE BUFFER bh FOR TABLE tableName.
    CREATE QUERY qh .
    qhET-BUFFERS(bh).
    expr = "for each " + tableName +
    " " + condition.
    qh:QUERY-PREPARE(expr).
    qh:QUERY-OPEN.
    qh:GET-FIRST(NO-LOCK) .
    DO WHILE bh:AVAILABLE:
    CREATE ttLock.
    CREATE BUFFER ttLock.hRec FOR TABLE tableName.
    IF lockType = "X" THEN
    ttLock.hRec:find-by-rowid(bh:ROWID, EXCLUSIVE).
    ELSE
    ttLock.hRec:find-by-rowid(bh:ROWID, SHARE).
    qh:GET-NEXT(NO-LOCK).

    END. /* bh:avail */

    END.

    ret = "ok".
    RETURN ret.
    END PROCE. /* lockRecords */


    PROCEDURE unlockRecords:
    DEF VAR ret AS CHAR INIT "not-ok".

    proc:
    DO ON ERROR UNDO, LEAVE
    ON STOP UNDO, LEAVE:
    FOR EACH ttLock
    ON ERROR UNDO, LEAVE proc
    ON STOP UNDO, LEAVE proc:
    DELETE OBJECT ttLock.hRec.
    DELETE ttLock.
    END.
    ret = "ok".
    END.
    RETURN ret.
    END PROCE. /* unlockRecords */

    /********************************************/

    However you must keep in mind that eXclusive lock occurs during active transaction only. It is downgraded to Share lock when the transaction is completed. There is a simple example below, which can be executed against sporst2000 database. It uses Locker.p procedure presented above.

    /*****************************************/
    DEF VAR hLocker AS HANDLE.

    DO TRANSACTION
    ON ERROR UNDO, LEAVE
    ON STOP UNDO, LEAVE:
    RUN Locker.p PERSISTENT SET hLocker.
    RUN lockRecords IN hLocker
    ("customer",
    "where customer.custnum < 10",
    "X").
    BELL.
    MESSAGE "eXclusive"
    VIEW-AS ALERT-BOX.
    /* now the eXclusive lock is applied to selected records */
    END.

    BELL.
    MESSAGE "Share"
    VIEW-AS ALERT-BOX.
    /* the locks are downgraded now to the Share lock */
    /***********************************************/

    On the other hand when the transaction is active you cannot release the locks apllied by this transaction:

    /************************************************** */
    DEF VAR hLocker AS HANDLE.

    DO TRANSACTION
    ON ERROR UNDO, LEAVE
    ON STOP UNDO, LEAVE:
    RUN Locker.p PERSISTENT SET hLocker.
    RUN lockRecords IN hLocker
    ("customer",
    "where customer.custnum < 10",
    "X").
    BELL.
    MESSAGE "eXclusive"
    VIEW-AS ALERT-BOX.

    RUN unlockRecords IN hLocker.
    BELL.
    MESSAGE "Still eXclusive"
    VIEW-AS ALERT-BOX.
    /* the locks are not released by the unlockRecords procedure
    as the transaction is still active */
    END.

    BELL.
    MESSAGE "Locks are released"
    VIEW-AS ALERT-BOX.
    /* now when the transaction is completed the locks are released */
    /************************************************/

    Hope it helps
    Regards
    Bogdan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •