What is a NOLOCK?

Answers were Sorted based on User's Feedback



What is a NOLOCK?..

Answer / michael

Advantage:
applying nolock in select statement will increase
concurreny and performance in fetching.

Disadvantage:
it does'nt consider the transaction scenario, even if the
transaction is under process not yet commited or rollback
it will fetch the current record
eg

begin tran
insert into tablename
values('1','asdf')

select * from tablename with (nolock)

-- it will display the table with inserted record.
but the transaction is not completed, it can be rollback
so it became dirty read.

Is This Answer Correct ?    28 Yes 2 No

What is a NOLOCK?..

Answer / naren

Using NOLOCK politely asks SQL Server to ignore locks and
read directly from the tables. This means you completely
circumvent the lock system, which is a major performance
and scalability improvement. However, you also completely
circumvent the lock system, which means your code is living
dangerously. You might read the not-necessarily-valid
uncommitted modifications of a running transaction. This is
a calculated risk.

Is This Answer Correct ?    19 Yes 1 No

What is a NOLOCK?..

Answer / guest

Do not issue shared locks and do not honor exclusive locks.
When this option is in effect, it is possible to read an
uncommitted transaction or a set of pages that are rolled
back in the middle of a read. Dirty reads are possible. Only
applies to the SELECT statement.

Is This Answer Correct ?    16 Yes 3 No

Post New Answer

More SQL Server Interview Questions

How to include text values in sql statements?

0 Answers  


How to generate the Reports for the Database? I need an Example for it.Will we manually do this or else any script is there? Please let me know ASAP...

1 Answers   Cognizant,


How would you retrieve Unique rows from table without using UNIQUE and DISTINCT keyword?

2 Answers   Genpact,


what are different types of backups available in sql server? : Sql server database administration

0 Answers  


What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of sp nesting are possible?

0 Answers  






Diff. b/w Full Outer Join And Cross Join?

3 Answers  


Write a SQL queries on Self Join and Inner Join.

0 Answers   Aspiring Minds,


Does transparent data encryption provide encryption when transmitting data across the network?

0 Answers  


Why would you use sql agent?

0 Answers  


What is difference between commit and rollback when used in transactions?

0 Answers  


What is nonclustered index with included columns ?

0 Answers  


What xml support does the sql server extend?

0 Answers  


Categories