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.

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 */

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.

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