In traditional PowerBuilder client/server applications, a
SELECT ... WITH (UPDLOCK) statement executed within an
active transaction can hold a lock on the selected row until the
transaction is committed or rolled back.
In PowerServer installable cloud applications, database
operations are executed through REST-based requests. Each request runs
within its own server-side transaction which is committed or rolled
back when the request completes. As a result, a SELECT ... WITH
(UPDLOCK) statement may not maintain the lock across multiple
requests because the transaction may be closed after the request
finishes.
To ensure the lock is maintained across multiple requests, one
approach is to perform a preliminary dummy update (for example,
UPDATE table SET col1 = col1 WHERE ...) before executing
SELECT ... WITH (UPDLOCK). This forces PowerServer to
start a physical database transaction, allowing the lock to persist
until the transaction is explicitly committed or rolled back.
For example,
Original PowerScript:
//DOES NOT hold the lock in PowerServer Select max(1) Into :ll_test From table b with(updlock, ROWLOCK) Using sqlca; //in PowerServer no transaction remains open after the request completes //therefore the lock is not retained. (If you run in regular pb, there are locks.)
Must be modified like below to work in PowerServer:
The "trick" is to hold the transaction open.
The application should explicitly COMMIT or ROLLBACK the transaction after the operation to release the lock and properly close the transaction
//Start a transaction by performing a preliminary update Update table A Set col1 = col1 where col1 = 1 USING SQLCA; //Execute the SELECT lock Select max(1) Into :ll_test From table b with(updlock, ROWLOCK) Using sqlca; //now if you check the locks in sql server, you will see that they exist on table b (as well as table a)
