%NOLOCK in insert query

Question

Question

Suman Samanta · Oct 2, 2018

I would like to know , if its safe to use %NOLOCK in insert query while doing some parallel processing in Ensemble. We will have pool size of more than 1 for a business process. The business process does an entry in internal meta data table , which will be used for some internal reporting. The same row might get updated by some other process later point of time. i could not produce any error or any issues . Looking forward to hear some opinion on this.

Discussion (1)1

Log in or sign up to continue

Alexander Koblov · Oct 3, 2018

No, it's not safe to use %NOLOCK on INSERT query.

Doc says following:

%NOLOCK ... should only be used when a single user/process is updating the database.

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

If you use INSERT with %NOLOCK, then some other process might UPDATE partially inserted row.

Rows inserted as follows:
a) Save inserted row (fill Data global)
b) File index values for inserted row (fill Index global)

Imagine there are two parallel processes A and B. A inserts row with %NOLOCK. B updates the same row without %NOLOCK.

t1) A saves row, filling Data global
t2) B updates the same row, overwriting the value in Data global
t3) B fills Index global with values corresponding to new data of row
t4) A fills Index global with values corresponding to old data of row (as of Insert time)

Now you have row in the table with wrong indices.

Also, please notice that if there is inheritance in classes -- E.g. Sample.Employee inherits from Sample.Person. Then storing row in Data global is at least two SETs, so you might end up with corrupted row, not only wrong index. Also filing index records is not atomic. Each index record is separate SET.

So, I would not recommend to use %NOLOCK. There is one possible use case for %NOLOCK -- bulk-loading data, but there should be only one process accessing the data while loading.

1 0