I need to run a few short-running queries against a production system. I need to be absolutely certain that SS doesn't take out any locks on the table as a result.
Does "with (nolock)" absolutely guarantee this I've read BOL on the topic and I understand isolation level read-uncommitted. But I want to validate that there's not any undocumented behavior in SS that might violate the documentation (which clearly states that no shared locks are issued).
Thanks!
does "with (nolock)" absolutely guarantee no locks are taken?
mx666_!
BrianW
Jürgen Hefele
The answer I am about to give is not likely to be pertinent to your question since you state short running, but just in case since you were so adament about NO LOCKS :) There is one tiny exception to the nolock/read uncommitted isolation level, in that it does take a shared schema lock so other users cannot drop/alter the table while your query is running. in 2005, run this:
create table testLocks
(
testLockId int,
bigValue char(8000) default (replicate('*',8000))
)
insert into testLocks(testLockId)
select 1
go 1000
set transaction isolation level read uncommitted
select * from testLocks
cross join testLocks as t2
----
Then on another connection run this (that query will return 1000000 16KB rows, so it will take a while :)
select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @@spid
On my 3Ghz, 512 MB machine running Express Edition (on a Media Center PC) I got two rows, one of which was the shared schema lock, the other was a bulk operation lock, likely in Tempdb trying to build these rows. Unless you are dropping and creating rows, this is unlikely to be a concern.
Derek Theriot
prowl
thanks everyone for your very helpful information.
TylerH
You're talking two different things.
with (nolock) is a HINT. That means there is a possibility that SS will override it.
SET TRANSACTION ISOLATION LEVEL is a command. AFAIK, SS won't override this.
KirHil