How to write SELECT ... FOR UPDATE

InterSystems FAQ rubric

Since SELECT ... FOR UPDATE is implemented in many RDBMS as a method of explicit row lock acquisition, I think there are many cases where this function is used.

This syntax is not an error for InterSystems products, but it does not acquire row locks as expected.

This article will show you how to achieve equivalent functionality.

DECLARE CURSOR C1 IS
SELECT Name FROM Person WHERE Name LIKE 'A%' FOR UPDATE
OPEN C1
LOOP FETCH C1 INTO name　
... show name
... EXIT the LOOP when finished
END LOOP
CLOSE C1

The above SQL statement can be substituted with the following SQL statement.

* Please note that the execution of the UPDATE statement on the second line below will cause an exclusive lock on the target row, which is different from the behavior of other DBs.

 &SQL(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
 &SQL(UPDATE Person SET ID=ID Where Name like 'A%')
 &SQL(DECLARE C1 CURSOR FOR SELECT ID, Name into :id, :name FROM Person Where Name like 'A%')
 &SQL(OPEN C1)
 &SQL(FETCH C1)
 While (SQLCODE = 0) {
   Write id, ": ", name, ! 
   &SQL(FETCH C1)
 }
 &SQL(CLOSE C1)
 &SQL(COMMIT) 

Note: &SQL() is called embedded SQL and is a description method that can be used when you want to embed SQL statements in server-side logic. Please refer to the documentation for details.