Explain the use of NOLOCK query optimizer hint

Next>>         SQL Server tutorial  

SQL Server - use of NOLOCK query optimizer hint - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

Explain the use of NOLOCK query optimizer hint.

NOLOCK is same as READUNCOMMITTED

SQL Server - use of NOLOCK query optimizer hint - May 05, 2009 at 22:00 PM by Rajmeet Ghai

Explain the use of NOLOCK query optimizer hint.

NOLOCK table hint used using WITH clause, is similar to read uncommitted. This option allows dirty reads are allowed. Using this option, shared locks cannot be issues to other transactions. This prevents existing transactions to not read incorrect data.

SQL Server - What is NOLOCK? - May 19, 2009 at 10:00 AM by Rajmeet Ghai

What is NOLOCK?

NOLOCK is used to improve concurrency in a system. Using NOLOCK hint, no locks are acquired when data is being read. It is used in select statement. This results in dirty read - another process could be updating the data at the exact time data is being read. This may result in users seeing the records twice.

SQL Server - What is NOLOCK? - June 21, 2009 at 09:00 AM by Amit Satpute

Explain the use of NOLOCK query optimizer hint.

NOLOCK

NOLOCK would be deprecated in the future releases. It has been replaced by READUNCOMMITTED.
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations.

They are ignored by the SQL query optimizer in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

They specify that dirty reads are allowed. Transactions can modify data while it is being read by a transaction. Shared locks are not issued to prevent it.

Exclusive locks set by other transactions do not block the current transaction from reading the locked data.

Allowing dirty reads provide higher concurrency but it may generate errors for a transaction and present users with data that was never committed or may have the records displayed twice.

Next>>

Also read

What are the lock types?

Answer - Shared Lock allows simultaneous access of record by multiple Select statements. Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading......

Sql Server DBA interview

What are the steps to take to improve performance of a poor performing query?
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
What is blocking and how would you troubleshoot it?.............

Define Identity and uniqueidentifier property of Column.

Answer - Column with identity property contains unique system generated value in the table. Column with identity property is similar to AutoNumber field in MS Access....