March 31, 2020 at 4:26 am
Hi all,
Hope all are staying safe!,
we have a 3rd partly application that is running on SQL Server
2014 standard edition, the application is getting hanged every ones in a while because of blocking issues.
I did some investigation and found there are 2 stored procedures that are conflicting
1. a process that excutes a delete stored procedure which removes a record in a table(TableA) is being blocked
by another process that is trying to insert into the same table(TableA),there is no complexity in both stored procedures,
the table in question is a very small table with about 300 records, i am puzzled on seeing a blocking issue with such a small table.
The table doesn't have a CLUSTERED INDEX it has a non-clustered index on an primary key(which is identity column),
also the delete storedprocedure where clause have columns are not indexed, and hence the execution plan shows table scan(i think this fine given the table size)
i used the sp_whoisactive to capture the blocking information and locks xml info
for the insert procedure
<Database name="XXXXX">
<Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="TableA" schema_name="dbo">
<Locks>
<Lock resource_type="KEY" index_name="PK_TableA" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_TableA" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="1" />
</Locks>
</Object>
for the delete sp
<Database name="XXXXX">
<Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="TableA" schema_name="dbo">
<Locks>
<Lock resource_type="KEY" index_name="PK_TableA" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" request_mode="IU" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_TableA" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="RID" page_type="*" request_mode="U" request_status="WAIT" request_count="1" />
<Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="1" />
</Locks>
</Object>
</Objects>
</Database>
i see that the blocking is happening because the insert statement is having a"IX" lock on the table,and is blocking the delete, i see that the insert statement some time takes more than 25 secs(not sure why it does), which i think is blocking the delete process and then it clears after a while, but by this time the application hangs and the only way to clear this to restart the application processes
from the above insert stored procedure xml there is a "IX" lock on the table which is causing the blocking on the delete , but am not sure why it takes 10 to 25 secs to just insert a record, any help greatly appreciated, the DB server has other applications running also and don't see any resource contention(as per my understanding)
Thanks
Vijay
April 1, 2020 at 5:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply