I was reading the answer from here (from stackoverflow, I think should ask in here)
NOLOCK means placing no locks at all.
Your query may returns portions of data as of before UPDATE and
portions as of after UPDATE in a single query.
I get that nolock will not place lock to the table, so other people can query the same time.
From the answer and example it show, it fetch data while the data is being updating.
Why does that happen?
I am assuming for normal select it will try place lock on table, so when update statement is executed, it place a lock on the row or page. Then when I try to run select statement, it cannot put the lock until the update statement lock is released.
But in this case because the select statement doesn't try to put lock on the table, so it can run without waiting for the update statement release the lock?
Best Answer
It is not quite true that
NOLOCK
means placing no locks at all. Queries under this hint will still takeSch-S
locks and (possiblyHOBT
locks).Under
read committed
isolation level SQL Server will (usually) take row levelS
locks and release them as soon as the data is read. These are incompatible with theX
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 aSELECT
reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation whereR1
and releases itsS
lockR2
and takes anX
lockR2
and is blocked.R1
and takes anX
lock.R2
This type of situation might arise for example if the
SELECT
andUPDATE
are using different indexes to locate the rows of interest.Example
Now in one query window run
This will run in an infinite loop. In another run
This will likely stop the loop in the other query (try again if not) meaning that it must have read either
A,AA
orAA,A