Menu
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) 
705
NOLOCK Is equivalent to READUNCOMMITTEDSpecifies 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.
In a nutshell, nolock (read uncommitted) takes no shared locks to prevent other transactions from modifying data read by this transaction. It also effectively ignores exclusive locks taken by other transactions when they have added or changed data but not committed it yet.
Thus, we can say that Nolock reads “Dirty Data” when applied with only Select statement in SQL Server Database. While With (Nolock)do not issue any shared locks and exclusive locks. It is possible with With (Nolock) that, it can read an uncommitted transaction, which can be rolled back at the middle of a read.
The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.
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
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
