Question
MARK PONGONIS · Jul 15

Dynamic SQL query on record that is locked

Is there a way to perform an %Prepare()/%Execute() that will wait until the record being queried is unlocked by another process? 

Ex: 

Process 1 - is opening record, updating, then saving. NOTE: this is using embedded SQL

&sql(SELECT %ID INTO :id FROM table WHERE Prop=:prop)
s obj=##class(APPTRACKER.CacheHB.CustomEventCacheHB).%OpenId(eventId)
//updating record...
s sc=obj.%Save()

Process 2 - is querying the same record while Process 1 has it open. Result is nothing is returned and %SQLCODE is 0

//simplified snippet with extra code removed
    ...
    s sql = ##class(%SQL.Statement).%New()
    s sc=sql.%Prepare("SELECT TOP 1 * FROM class WHERE Prop='"_prop_"'")
    i $$$ISOK(sc) {
        s rset = sql.%Execute()
        i 'rset.%SQLCODE {
            i rset.%Next() {
                s propVal=rset.%Get(prop)
            }
        }
    }

 

Product version: IRIS 2021.1
0
0 138
Discussion (3)1
Log in or sign up to continue

In your post, you say "Result is nothing is returned and %SQLCODE is 0". I just want to make sure this isn't just a misunderstanding. If the SQLCODE is 0, that would mean that there is a result returned. When the query executes successfully and nothing is returned, the SQLCODE is 100.

I think what you're looking for might be setting the isolation level to read committed. This will make the process wait for the in-progress changes have been committed, though you'll still want to make sure you handle SQLCODE -114 somehow, too. That's the code you get back if there's a timeout waiting for the lock. You should be able to set that using:

%SYSTEM.SQL.Util.SetOption("IsolationMode",1,.oldval)

If you do that before your query, the rest of the process will run at that isolation level.

You can use that same method to set the LockTimeout too, by the way. Default is 10 seconds.