What is a NOLOCK?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
How to include text values in sql statements?
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...
How would you retrieve Unique rows from table without using UNIQUE and DISTINCT keyword?
what are different types of backups available in sql server? : Sql server database administration
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?
Diff. b/w Full Outer Join And Cross Join?
Write a SQL queries on Self Join and Inner Join.
Does transparent data encryption provide encryption when transmitting data across the network?
Why would you use sql agent?
What is difference between commit and rollback when used in transactions?
What is nonclustered index with included columns ?
What xml support does the sql server extend?