April 30, 2013 at 8:21 am
Hi,
We are running one job which runs SELECT query from a view from another linked server. We are getting blocking in the linked server where SELECT query from View is causing the same. The view is written with NOLOCK. So I am confused, how SELECT query can be culprit blocker when using NOLOCK option?
Please help.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 30, 2013 at 8:54 am
select so.name, resource_database_id, Resource_associated_entity_id, request_mode, request_type, request_status,request_session_id, GETDATE() from sys.dm_tran_locks tl
inner join sys.sysobjects so on so.id= tl.resource_associated_entity_id
where [resource_type] <> 'database'
--and so.name <> 'Table_1'
and so.name not like 'sys%'
Try this it will tell you who is locking what and what locks it has acquired.
April 30, 2013 at 9:33 am
Hi,
One DTS job is ruuning which is causing the blocking.
Lock type- sch-s
DBCC Inputbuffer gives the select query
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 1, 2013 at 8:51 am
Ryan007 (4/30/2013)
Hi,One DTS job is ruuning which is causing the blocking.
Lock type- sch-s
DBCC Inputbuffer gives the select query
Even when NOLOCK or READUNCOMMITTED hints are used, the query acquires a SCH-S (schema stability) lock to protect the object schema from modification while the query is using it. This will cause blocking of any transactions that are attempting to obtain a SCH-M (schema modification) lock to change the object schema, and vice versa. Do you have some process running that is attempt to ALTER or DROP the view or an object referenced by the view?
Jason Wolfkill
May 2, 2013 at 10:48 am
Ryan007 (4/30/2013)
Hi,We are running one job which runs SELECT query from a view from another linked server. We are getting blocking in the linked server where SELECT query from View is causing the same. The view is written with NOLOCK. So I am confused, how SELECT query can be culprit blocker when using NOLOCK option?
Please help.
The NOLOCK hint doesn't work when selecting from or joining remote tables. It will throw syntax error if you attempt to add nolock to a four part named table. If you add NOLOCK hint to a view that itself select from remote tables, then it will comile and run but the hint will be ignored.
There is an assumption that blocking is result of locking, and that can typically be the case, but there are over 100 different wait states that can block a query or put it in a wait state. Use the following query to see what's currently blocking and also the specific wait_type.
Since it's a remote query, it may even be an OLEDB wait type, meaning that SQL Server is waiting for resultset to be pulled from remote server. If you're joining between multiple remote tables, it may even be doing table scans and pulling entire resultset across the wire to your local server to be joined.
USE [master]
GO
SELECT session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO
SELECT s.session_id, wt.blocking_session_id, wt.wait_type, max(wt.wait_duration_ms)wait_duration_ms
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY s.session_id, wt.blocking_session_id, wt.wait_type
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 2, 2013 at 11:06 am
Eric M Russell (5/2/2013)
Ryan007 (4/30/2013)
Hi,We are running one job which runs SELECT query from a view from another linked server. We are getting blocking in the linked server where SELECT query from View is causing the same. The view is written with NOLOCK. So I am confused, how SELECT query can be culprit blocker when using NOLOCK option?
Please help.
The NOLOCK hint doesn't work when selecting from or joining remote tables. It will throw syntax error if you attempt to add nolock to a four part named table. If you add NOLOCK hint to a view that itself select from remote tables, then it will comile and run but the hint will be ignored.
There is an assumption that blocking is result of locking, and that can typically be the case, but there are over 100 different wait states that can block a query or put it in a wait state. Use the following query to see what's currently blocking and also the specific wait_type.
Since it's a remote query, it may even be an OLEDB wait type, meaning that SQL Server is waiting for resultset to be pulled from remote server. If you're joining between multiple remote tables, it may even be doing table scans and pulling entire resultset across the wire to your local server to be joined.
USE [master]
GO
SELECT session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO
I'm pretty sure the OP meant that the view exists on the linked server, that the view definition includes the NOLOCK hint, and that the blocking occurs on the linked server when it executes the 'SELECT * FROM VIEW_X' query.
OP, you indicated that a DTS job is causing the blocking and mentioned a Sch-S lock. The SELECT * FROM VIEW_X query on the linked server will try to acquire a Sch-S lock on the view (I'm not sure off the top of my head how that will affect the tables underlying the view - my gut sense is that it won't unless the view was created with schemabinding). What other process is taking locks on that view, and what kind of locks?
Jason Wolfkill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply