As I understand it, with Read Committed Snapshot, readers don't block writers and writers don't block readers. The only blocking that can occur is writers blocking writers. By default our database is set to Read Committed Snapshot. However, we have a lot of users issuing queries using WITH (NOLOCK) (out of bad habits). If I understand correctly, a query that uses WITH (NOLOCK) changes the isolation level. When this is used in a query, is it now possible for a reader to block a process attempting to update the data being read?
asked Nov 7, 2017 at 20:25
-
You may want to tag your question with the DBMS you're using.– mustaccioCommented Nov 7, 2017 at 20:29
Add a comment
|
1 Answer
If I understand correctly, a query that uses WITH (NOLOCK) changes the isolation level. When this is used in a query, is it now possible for a reader to block a process attempting to update the data being read?
No, you think wrong.
Both in RCSI and Read Uncommitted readers don't block writers.
The mechanism is different: RCSI uses last committed copy of modifying data for the reader, and a SELECT using with (nolock) will access "dirty" data, but there is no blocking in both the cases.
Here is an example. You make an update like this:
begin tran
update dbo.t
set col1 = 20 where col1 = 10;
-- transaction is still open, X lock is held on a row
In the second session you make SELECT * from dbo.t;
In the case of RCSI you'll get back col1 = 10, in case of Read Uncommitted you'll get col1 = 20, but session 2 will return data in both cases immediately, without waiting when transaction from 1st session commits/rolls back
