May 6, 2009 at 12:15 pm
Query 1: (proper indexes are present on tables, execution plan is best)
select * from vWorkflowTransition where WkTr_TransitionId=9217
Query 2: (index is present on escl_escalationId but NOT utilized due to COALESCE function)
UPDATE Escalations
SET Escl_DateTime=NULL,escl_UpdatedBy=818,escl_TimeStamp='20090506 12:06:05',escl_UpdatedDate='20090506 12:06:05'
WHERE (COALESCE(Escl_EscalationID, 0) = 210693)
Query 3: (index is present on Lock_TableId and Lock_RecordId but NOT utilized due to COALESCE function)
DELETE [LOCKS]
WHERE coalesce([Lock_TableId],@1)=5 AND [Lock_RecordId]=10192345
Here is the scenario. Query 1 is blocking Query 2 and Query 3
So my question is How could query 1 block other 2 queries even when they are using different tables, although sql server is not utilizing the indexes for Query2 and Query3 but still all three queries are using different tables, so why are they blocking each other??
May 6, 2009 at 12:16 pm
Are there FKs that connect those tables? It seems like the delete might cause those to come into play, if they are there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2009 at 12:21 pm
No Foreign keys, completely independent tables.
May 6, 2009 at 12:21 pm
usman.tanveer (5/6/2009)Here is the scenario. Query 1 is blocking Query 2 and Query 3
Lets go for the basics... Please show us what makes you think query #1 is blocking queries #2 and #3?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2009 at 12:22 pm
I found it through Activity Monitor.
And i should add that "WITH (NOLOCK)" is also present in the view "vWorkflowTransition" which is the first query.
May 6, 2009 at 12:25 pm
Also, what table(s) does this view access: vWorkflowTransition?
I am basing my assumption that this is a view due to the naming convention used in the name.
May 6, 2009 at 12:31 pm
usman.tanveer (5/6/2009)I found it through Activity Monitor.
Good!... now show us the locks.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2009 at 12:34 pm
Based on the name, vWorkflowTransition is a view. That means it's probably hitting both tables and maybe more.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 6, 2009 at 12:37 pm
Grant Fritchey (5/6/2009)
Based on the name, vWorkflowTransition is a view. That means it's probably hitting both tables and maybe more.
Here is the command in the view.
SELECT WkTr_TransitionId, WkTr_WorkflowId, WkTr_StateId, WkTr_NextStateId, WkTr_RuleId, WkTr_CreatedBy, WkTr_CreatedDate, WkTr_UpdatedBy,
WkTr_UpdatedDate, WkTr_TimeStamp, WkTr_Deleted, WkTr_Condition, WkTr_Expanded
FROM dbo.WorkflowTransition WITH (NOLOCK)
WHERE (WkTr_Deleted IS NULL)
So completely different tables.
May 6, 2009 at 12:42 pm
What PaulB said: we need to see what you saw in the Activity Monitor wrt to the locks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 12:42 pm
PaulB (5/6/2009)
Good!... now show us the locks.
Query 2 has wait_type = "LCK_M_U" and wait_resource = "PAGE: 8:1:135798"
Query 3 has wait_type = "LCK_M_U" and wait_resource = "KEY: 8:72057596239085568 (4d03000fb951)"
May 6, 2009 at 12:43 pm
Triggers? Foreign Keys? Rules? Constraints?
There has to be something. You won't get a block... a block, which has a very specific meaning and refers to one process waiting on the other for a locked resource... when you're updating two completely unrelated tables unless there is something relating those tables. So there's something else involved here.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 6, 2009 at 12:47 pm
RBarryYoung (5/6/2009)
What PaulB said: we need to see what you saw in the Activity Monitor wrt to the locks.
I have provided the locks and resources in an earlier message.
May 6, 2009 at 12:50 pm
Can you run a query against sys.dm_tran_locks? That will give better information. Specifically we need to see the entity_id, the object getting locked.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 6, 2009 at 1:06 pm
Actually i am logging the locks into a custom table using a sql script that i wrote, i have attached the file with the details of the blocks, please see if this helps.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply