MSSQL中with(nolock)的用法

由于在有时候我们在查询数据的同时，有其他用户进行表的增删改操作，如果此时对该表的事物没有提交更新，则此时该表处于锁定状态，会影响我们查询的结果，造成我们无法查询出该结果（由于表处于锁定状态）。所以用nolock来进行排除锁定状态，不考虑是否是锁定状态，都进行查询结果。简单说：不锁表。 下面引入一篇老外的讲解说明： Here is a query that returns all of the data from the Person.Contact table. If I run this query I can see there is only one record that has a Suffix value for ContactID = 12.

SELECT * FROM Person.Contact WHERE ContactID < 20

Let's say another user runs the below query in a transaction. The query completes and updates the records, but it is not yet committed to the database so the records are locked.

-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

If I run the same query from above again you will notice that it never completes, because the UPDATE has not yet been committed.

-- run in query window 2 SELECT * FROM Person.Contact WHERE ContactID < 20

If I run sp_who2 I can see that the SELECT statement is being blocked. I will need to either cancel this query or COMMIT or ROLLBACK the query in window one for this to complete. For this example I am going to cancel the SELECT query. To get around the locked records, I can use the NOLOCK hint as shown below and the query will complete even though the query in window 1 is still running and has not been committed or rolled back.

-- run in query window 2 SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

If you notice below the Suffix column now has "B" for all records. This is because the UPDATE in window 1 updated these records. Even though that transaction has not been committed, since we are using the NOLOCK hint SQL Server ignores the locks and returns the data. If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1. If I rollback the UPDATE using the ROLLBACK command and rerun the SELECT query we can see the Suffix is back to what it looked like before.

-- run in query window 1 ROLLBACK

 

-- run in query window 2 SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 -- or SELECT * FROM Person.Contact WHERE ContactID < 20

So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database. If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.