quote:

---

Originally posted by CorkMan
In all of our SELECT queries and Stored Procedures we specify (NOLOCK)

---

quote:

---

Nolock will have no effect on the two concurrent writes scenario. It only affects reads.

---

quote:

---

If you are doing SELECT and UPDATE in one "unit of work" then there is the possibility of blocking between processes. In particular if Process-1 updates Table-A and then tries to update Table-B and "at the same time" Process-2 updates Table-B and then tries to update Table-A. One of the processes should be treated as the deadlock-victim and be aborted (and rolled back) in such circumstances.

In such circumstances NOLOCK will probably make things worse because it may have successfully read the data that Process-2 had written to Table-B (which has now been rolled back) and may therefore be trying to perform some action dependant on that data (apart from a user acting upon their viewing of that data too).

---

IF EXISTS (SELECT * FROM MYTABLE WITH (NOLOCK)) WHERE MyID = 1234)
BEGIN
    UPDATE MyTable
    SET Col1 = 'Foo'
    WHERE MyID = 1234
END

quote:

---

You misunderstand me.

The nolock hint only affects read queries. Queries that change data (update, delete) will ignore the nolock hint if it's applied. It is only for read queries.

---

quote:

---

"NOLOCK may cause serious problems as Kristen described but it is not the cause of deadlock"

You sure? I have no evidence of this but:


IF EXISTS (SELECT * FROM MYTABLE WITH (NOLOCK)) WHERE MyID = 1234)
BEGIN
UPDATE MyTable
SET Col1 = 'Foo'
WHERE MyID = 1234
END


Another session may have a lock on MyID=1234 and, depending on isolation and Transaction state in both processes, that could presumably block the SELECT if it did not have the NOLOCK hint.

So this statement could be blocked BEFORE the UPDATE rather than WHEN the Update was reached.

Just assume considerably more complex logic, but where the SELECT's have NOLOCK hints and thus don't block.

I think this could lead to Deadlock on the Updates whereas, without the NOLOCKs, the process might block earlier such that Deadlock did not occur.

Pure conjecture though!

---

quote:

---

Originally posted by namman

In your sql, if you take WITH(NOLOCK) out (with repeatable isolation level), deadlock will occur in concurring enviroment.

---

quote:

---

IF EXISTS (SELECT * FROM MYTABLE WITH (NOLOCK)) WHERE MyID = 1234)
BEGIN
UPDATE MyTable
SET Col1 = 'Foo'
WHERE MyID = 1234
END


In your sql, if you take WITH(NOLOCK) out (with repeatable isolation level), deadlock will occur in concurring enviroment. I should explain more about my idea .... but a bit busy. I come back latter.

---

quote:

---

Why not use the normal read committed isolation level?

---