What is (are) difference between NOLOCK and UNCOMMITTED
I use SQL Server 2012.
I write two queries but what is a different between
SELECT lastname, firstname FROM HR.Employees with (READUNCOMMITTED) SELECT lastname, firstname FROM HR.Employees with (NoLock)
5 Solutions collect form web for “What is (are) difference between NOLOCK and UNCOMMITTED”
NOLOCK : Is equivalent to
READUNCOMMITTED (source : MSDN)
READUNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions
NOLOCK hints apply only to data locks. All queries, including those
with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table
There is no difference at the statement level.
You can set READUNCOMMITED at the session level and here you have to write
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Under the hood they are the performing the same action.
read-uncommitted isolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking.
nolock table hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level.
The only difference between the two is that the
read-uncommitted isolation level determines the locking mechanism for the entire connection and the
nolock table hint determines the locking mechanism for the table that you give the hint to.
No difference in terms of their functions, like other have mentioned.
The single difference is that you can apply
WITH(NOLOCK) selectively, on some tables but not others.
READ UNCOMMITTED applies
NOLOCK to all tables in a session.
If you do this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T1.ID = T2.id
It is functionally equivalent to:
SELECT * FROM Table1 T1 WITH(NOLOCK) INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.ID = T2.ID
But you can also apply
SELECT * FROM Table1 T1 WITH(TABLOCK) INNER JOIN Table2 WITH(NOLOCK) ON T1.ID = T2.ID
For NOLOCK , we need to put this hint on table level, so it is require to put for every tables level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere tables refers in query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written at top of the query or stored procedure.
Let us look on small demo to elaborate it. First checking here database default isolation level
CREATE TABLE SAMPLETABLE ( Col1 INT , Col2 VARCHAR(100) ) INSERT INTO SAMPLETABLE(Col1,Col2) SELECT 1,'Col1' Union all SELECT 2,'Col1' BEGIN TRANSACTION Update SAMPLETABLE Set Col2 = 'Value changed' Where col1 =1 Select * from SAMPLETABLE with (nolock) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Select * from SAMPLETABLE
Output is 1, Col1 for both query