Blocking tables issue

Question

Blocking tables issue

mario17

SSCertifiable

Points: 6079
More actions
December 5, 2016 at 5:10 pm

#316403

Hi,
I have big process which log all details into TRACKER table for further review and I noticed that I have <Blocking Tree> report once I try to do
select * from dbo.TRACKER
while my sp is still running, even it has set IL like :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
What is the logic behind this, is it b'z of sp ? I expected my query from TRACKER be out of any locks.
THanks for info
Mario

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

mario17 (12/5/2016)
Hi,
I have big process which log all details into TRACKER table for further review and I noticed that I have <Blocking Tree> report once I try to do
select * from dbo.TRACKER
while my sp is still running, even it has set IL like :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
What is the logic behind this, is it b'z of sp ? I expected my query from TRACKER be out of any locks.
THanks for info
Mario

Can you please try to explain what is going on and what you are noticing differently? I have absolutely no idea what you are talking about here or even a hint as to what the actual problem is.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

BLOB EATER SSChampion Points: 14609 More actions · Answer 2

I think he is trying to say why is he still seeing blocking with read uncommitted being set in his query....that's my guess anyway.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

If you have blocking with read uncommitted, this could be due to DDL operations.

When you have the blocking, check the request_mode of sys.dm_tran_locks and check for schema stability locks.

Sue

mario17 SSCertifiable Points: 6079 More actions · Answer 4

Tx, I got notice from our alert system re lock, and I found that my select * From tracking was with IL=COmmited, so setting it uncommit solved this problem.

Tx all

M