0

In a stored procedure,

can an insert/update followed by a select (nolock) on the same record yield unexpected results?

Do I always get the (complete) updated/inserted record?

Thanks in advance for your help!

Raghu Ariga
  • 1,059
  • 1
  • 19
  • 28
Gerard
  • 2,649
  • 1
  • 28
  • 46
  • 1
    Why would you use a `NOLOCK` hint if you care? – Dan Guzman Nov 06 '16 at 17:22
  • Prevent (b)locks, but we notice some weird results once awhile. There are a lot of concurrent threads, all operating on a unique record (new or updated) – Gerard Nov 06 '16 at 19:08
  • Concurrent insert/update activity during a NOLOCK scan can cause rows to be missed or duplicated. It would be better to turn on the READ_COMMITTED_SNAPSHOT database option unless your application relies on pessimistic locking. – Dan Guzman Nov 06 '16 at 19:50

1 Answers1

0

If everything is done by single process, then data will be correct. But if the stored procedure is called from a different process id and user is doing select (nolock) in other process then discrepancy in data can happen for select.

As stored procedure may be still in use.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Singh_A22
  • 16
  • 3
  • Thx Signh! There are a lot of concurrent threads, all operating on a unique record (new or updated). Still no problem? – Gerard Nov 06 '16 at 19:04
  • Ideally nolock and read uncommitted should not be used but if you plotting your result set to a graph or to dashboard and you can afford some discrepancy as they are showing treads then you may use nolock. – Singh_A22 Nov 07 '16 at 13:55