What is (are) difference between NOLOCK and UNCOMMITTED

sql sql-server sql-server-2012 locking isolation-level
Ardalan Shahgholi picture Ardalan Shahgholi · Nov 29, 2014 · Viewed 51.9k times · Source

I use SQL Server 2012.

I write two queries but what is a different between NOLOCK and UnCommitted ? 

SELECT lastname, firstname
FROM HR.Employees with (READUNCOMMITTED)

SELECT lastname, firstname 
FROM HR.Employees with (NoLock)

Answer

Pரதீப் picture Pரதீப் · Nov 29, 2014

NOLOCK : Is equivalent to READ UNCOMMITTED (source : MSDN)

NOLOCK or READ UNCOMMITTED 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

READ UNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READ UNCOMMITTED 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

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More