Showing posts with label nolock. Show all posts
Showing posts with label nolock. Show all posts

Wednesday, March 21, 2012

hi need help

hi all......,

in mssql 2000 there's this code
Declare LockObj_Cur CURSOR Fast_FORWARD FOR
Select id, dbId, type
FROM master..SysLocks (NOLOCK)
WHERE spId = @.spId
AND type in (5, 8, 10, 11)

how do i proceed in mssql 2005 so that i can get the same info as given by above piece of code,
i have found out that sys.dm_tran_locks has to be used but i wasnot able to find equivalent of "type" column of syslocks table of mssql 2000 in sys.dm_tran_locks
plz help

According the 2000 BOL, syslocks was renamed after 6.5 to syslockinfo. And the rsc_type column types only went up to 10. Can you state what you are actually looking for?

On my blog, I have the query that I use all of the time (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!906.entry#postcns!80677FB08B3162E4!906):

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

But I haven't documented what every column of the dmv's have yet (I am actually working on a project to do so.)

Louis

sql