Advantage of setting isolation level to READ UNCOMMITED

Hi,
I have two questions.
1) What is the advantage of setting isolation level to READ UNCOMMITED if
there are noupdates for the records that we are selecting?
Does it make the select faster when we don't issue any shared lock?
2) I found this statement in books online:
'if an update acquires a large number of row locks and has locked a
significant percentage of a table, the row locks are escalated to a table
lock'
Is this statement true when we have a select instead of update and isolation
level is READ COMMITED?
Thanks,
RosiePerformance is slightly better when using READ UNCOMMITTED for selects, as
SQL Server does not have to do any of the work of issuing shared locks.
And yes, SELECTs can escalate lock granularities.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:2E0888C6-59F0-4E52-B4C8-2D91645D1FDB@.microsoft.com...
> Hi,
> I have two questions.
> 1) What is the advantage of setting isolation level to READ UNCOMMITED if
> there are noupdates for the records that we are selecting?
> Does it make the select faster when we don't issue any shared lock?
> 2) I found this statement in books online:
> 'if an update acquires a large number of row locks and has locked a
> significant percentage of a table, the row locks are escalated to a table
> lock'
> Is this statement true when we have a select instead of update and
isolation
> level is READ COMMITED?
> Thanks,
> Rosie|||> 1) What is the advantage of setting isolation level to READ UNCOMMITED if
> there are noupdates for the records that we are selecting?
> Does it make the select faster when we don't issue any shared lock?
Sure, since there is less work to do. Will it make it that much faster, not
really unless the statement would have been blocked.

> 2) I found this statement in books online:
> 'if an update acquires a large number of row locks and has locked a
> significant percentage of a table, the row locks are escalated to a table
> lock'
> Is this statement true when we have a select instead of update and
> isolation
> level is READ COMMITED?
If you are in read committed isolation level, then you would not escalate.
Basically, as rows are fetched a lock is taken on that row. Then the row is
placed on the output buffer, the lock is released, a new row is locked and
fetched, etc. So only one row should be locked during the select. The
holdup comes when the single row that need to be locked is already locked.
Then wait city. If you are doing lots of updates, or in READ COMMITTED or
SERIALIZABLE transaction isolation level, then the table lock might come in
to play, but you would likely have to lock a lot of rows.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:2E0888C6-59F0-4E52-B4C8-2D91645D1FDB@.microsoft.com...
> Hi,
> I have two questions.
> 1) What is the advantage of setting isolation level to READ UNCOMMITED if
> there are noupdates for the records that we are selecting?
> Does it make the select faster when we don't issue any shared lock?
> 2) I found this statement in books online:
> 'if an update acquires a large number of row locks and has locked a
> significant percentage of a table, the row locks are escalated to a table
> lock'
> Is this statement true when we have a select instead of update and
> isolation
> level is READ COMMITED?
> Thanks,
> Rosie