Sql-server – force a user to use WITH NOLOCK

Using NOLOCK blindly is idiotic. There is no best practice at all for this. This could be construed as utter arrogance: that someone knows better than MS who chose READ COMMITTED as the default

From SO: "Using NOLOCK Hint in EF4?". As well as me calling the DBA a muppet, a guy from the MS EF team answers too. And SQL - when should you use “with (nolock)”

In addition, NOLOCK is ignored for updates, inserts, deletes.

Using 1:1 views is idiotic too. It adds no value. From programmers.se "What popular “best practices” are not always best, and why?".

Using a view to hide table changes ad interim is OK, but having the extra layer based on dogma is pointless. And I bet you don't have WITH SCHEMABINDING so the view can differ from the table anyway (SO)

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks).

Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent dirty reads.

In the example in the linked answer the SELECT query is not blocked when it encounters a modified row so reading partial updates is quite likely.

It can also happen at default read committed isolation level too though that a SELECT reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation where

1. Select query reads value of row R1 and releases its S lock
2. Update query updates R2 and takes an X lock
3. Select query tries to read R2 and is blocked.
4. Update query updates R1 and takes an X lock.
5. Update transaction commits thus releasing its locks and allowing the Select to read R2

This type of situation might arise for example if the SELECT and UPDATE are using different indexes to locate the rows of interest.

Example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y AS -X UNIQUE,
Name varchar(10),
Filler char(4000) DEFAULT 'X'
)


INSERT INTO T (Name)
SELECT TOP 2500 'A'
FROM master..spt_values

Now in one query window run

DECLARE @Sum int

SELECT 'SET @@ROWCOUNT' WHERE 1=0

WHILE (@@ROWCOUNT = 0)
SELECT @Sum = SUM(LEN(Name))
FROM T 
WHERE Y IN (-1, -2500)
HAVING SUM(LEN(Name)) = 3

This will run in an infinite loop. In another run

UPDATE T 
SET Name=CASE WHEN Name = 'A' THEN 'AA' ELSE 'A' END

This will likely stop the loop in the other query (try again if not) meaning that it must have read either A,AA or AA,A