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
- Select query reads value of row
R1
and releases its S
lock
- Update query updates
R2
and takes an X
lock
- Select query tries to read
R2
and is blocked.
- Update query updates
R1
and takes an X
lock.
- 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
The table hint in the query takes precedence, as I would have expected.
Here's my test script:
CREATE TABLE t1 (a int);
INSERT INTO t1(a) SELECT TOP 1000 message_id FROM sys.messages;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM t1 WITH(NOLOCK); /* Try it with and without the hint */
EXEC sp_lock;
ROLLBACK;
With the NOLOCK
hint included, no locks are taken by the SELECT
statement.
In other words, setting the isolation level on the transaction makes that isolation level the default within the transaction, but that default can still be overridden by table hints, as I demonstrated.
Best Answer
Locking is mainly used to help writes and reads write and retrieve data that is ACID compliant. Thus a 'read only' query can have several layers of locking. By default, MS SQL Server uses pessimistic locking where as Oracle uses optimistic locking. That means in SQL Server reads by default will always block writes and vice versa. In Oracle's default isolation level, reads do not block writes. Thus unless you are familiar with your isolation level and if you're OK with dirty reads, then technically could. However that doesn't mean you should.
Read up on some of the issues you'll face with NoLock and alternatives.
Copy/paste: