|
Locking is a normal process in sql server to claim some resource. For
instance if we put "select * from emp" table, will set a shared lock
on the emp table. Locks are there in place to maintain data integrity
& consistency. Blocking is the process of blocking a resource by another resource,
which is already having an incompatibale lock on the same resource.
For instance "select * from emp" table will set a shared lock on emp
table. At this time if any other process try to have an exclusive lock
on the emp table, the second process will be in block state, & this
will remain till the first shard lock is removed.
You can get the lock info using sp_lock & sp_who will give you the
block process details.
Below query is very handy to find the blocking queries, rather than
checking thru Activity Monitor. SQL 2000 & 2005
------------------------- SELECT spid, status, loginame=SUBSTRING(loginame,
1,12),hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),dbname=SUBSTRING(DB_NAME(dbid),
1, 10), cmd, waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) SQL 2005
-------------- SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
|
