October 18, 2015 at 3:30 am
Hi
2 or 3 days we have huge number of Blocking.
In most of them these sp's are Blocked By 2 specific SP.
In addition in the same time that 2 Specific sp's are shown as open transaction. (and every Transaction Contain in average 100 commands and about 2 or 3 minutes)
But
This is strange :
Between Blocked sps and this 2 specidfic sp, We dont have any share table.
why and what does happen? what is the resource that Blocked sp wait And Blocked for that ?
Thank you
October 20, 2015 at 1:43 am
October 20, 2015 at 8:02 am
a foreign key constraint between the tables could block other spids from updating or inserting data due to locks,
Lowell
October 20, 2015 at 8:17 am
The following gem will list what database/objects currently are locked by an open transaction, the type of lock used (ie: Exclusive vs. Shared) and also the SPID, login name, etc. for session holding the lock. It also includes the currently executing sql text of the request holding the lock.
SELECT DISTINCT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time,
es.transaction_isolation_level,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
AS statement_text
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es ON tl.request_session_id=es.session_id
JOIN sys.dm_exec_requests er on er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE resource_type='OBJECT';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 21, 2015 at 4:03 am
Thank you all
And Lowell
Yes , we have a main forign key between Open Transaction And the Blocked sp.
October 21, 2015 at 5:52 am
MotivateMan1394 (10/21/2015)
Thank you allAnd Lowell
Yes , we have a main foreign key between Open Transaction And the Blocked sp.
i have that problem in my shop, related to occasional deadlocks.
as an update goes through indexes and validates foreign keys, the update locks and blocks other processes trying to access a FK related table.
I know the root cause in my case: a Linq To SQL update makes a poorly designed update to check concurrency, which ends up being updated to a table level lock,
for example, Linq generates a query like this:
UPDATE [dbo].[ActivityLog] SET [UpdatedBy] = @p22, [UpdatedDate] = @p23, [CheckOutUser] = @p24, [CheckOutDate] = @p25, [CurStatusID] = @p26, [RefID] = @p27, [RefInsuranceID] = @p28, [ToCodeNETOn] = @p29, [ToCodeNETBy] = @p30
WHERE ([ActivityLogID] = @p0) AND ([EDLogID] = @p1)
AND ([EDLogRecoveryID] IS NULL)
AND ([FacilityID] = @p2) AND ([HospAcctno] = @p3) AND ([MRNumber] = @p4) AND ([Fname] = @p5)
AND ([LName] = @p6) AND ([DateOfService] = @p7)
AND ([ProviderID] IS NULL) AND ([VoidsID] IS NULL)
AND ([isDeficient] IS NULL) AND (NOT ([isInHou
logically, i can see it could just do an update on the PK of the table [ActivityLogID], but the designer behind the scenes is making sure that the values didn't change since it was last "grabbed", and makes that huge WHERE statement....which results in a table scan instead of an index seek.
the fix for me is to have the app call a stored procedure instead, but getting the team to modify the code takes time .
you might be getting something similar... an escalation due to the plan expanding locks beyond a row level..
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply