New post
Question
· Jul 15, 2022

Dynamic SQL query on record that is locked

#Caché

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()
ObjectScript
ObjectScript

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)
            }
        }
    }

ObjectScript
ObjectScript
Product version: IRIS 2021.1
Discussion (3)0
Log in or sign up to continue
David Hockenbroch · Jul 18, 2022

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.

1 0