SQLServer2012 Locking

Question

Post reply

SQLServer2012 Locking

TSQLLearner2014

SSC Veteran

Points: 272
More actions
July 3, 2014 at 10:27 am

#290685

I have a insert stored procedure that inserts data with the values passed as parameters to this proc and then call another proc that selects the same data that is inserted. I execute the insert procedure within a transaction(READ COMMITTED). I see deadlocks when multiple processes call the insert stored proc. How can I get around this? Any help is appreciated
code:
*create proc [dbo].[xyz_Insert]
@FixingSource VarChar(60)
,@FixTime DateTime
,@InstrumentId Int
,@Lhs Decimal(18, 14)
,@Rhs Decimal(18, 14)
,@Version Int
,@ChangeTime DateTime
,@ChangeUser VarChar(60)
as
set nocount on
if @Version = 0
set @Version = 1
insert into [TableA] ([FixingSource], [FixTime], [InstrumentId], [Lhs], [Rhs], [Version], [ChangeTime], [ChangeUser])
values (@FixingSource, @FixTime, @InstrumentId, @Lhs, @Rhs, @Version, @ChangeTime, @ChangeUser)
exec [dbo].[xyz_Read]
@FixingSource = @FixingSource
,@FixTime = @FixTime
,@InstrumentId = @InstrumentId
----------
CREATE proc [dbo].[XYZ_Read]
@FixingSource VarChar(60) = null,@FixTime DateTime = null,@InstrumentId Int = null
as
set nocount on
select*
from[TableA]
where(@FixingSource is null or [FixingSource] = @FixingSource)
and(@FixTime is null or [FixTime] = @FixTime)
and(@InstrumentId is null or [InstrumentId] = @InstrumentId)
go*

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

To start...

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Can you post the deadlock graph? (you can get it out of the system health extended events session)

Also, https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TSQLLearner2014 SSC Veteran Points: 272 More actions · Answer 2

<victim-list>

</victim-list>

<process-list>

select*

from[LimitOrderFixingSnapshot]

where(@FixingSource is null or [FixingSource] = @FixingSource)

and(@FixTime is null or [FixTime] = @FixTime)

and(@InstrumentId is null or [InstrumentId] = @InstrumentId) </frame>

exec [dbo].[FixingSnapshotRepository_LimitOrderFixingSnapshot_Read]

@FixingSource = @FixingSource

,@FixTime = @FixTime

,@InstrumentId = @InstrumentId </frame>

</executionStack>

Proc [Database Id = 8 Object Id = 489219547] </inputbuf>

</process>

select*

from[LimitOrderFixingSnapshot]

where(@FixingSource is null or [FixingSource] = @FixingSource)

and(@FixTime is null or [FixTime] = @FixTime)

and(@InstrumentId is null or [InstrumentId] = @InstrumentId) </frame>

exec [dbo].[FixingSnapshotRepository_LimitOrderFixingSnapshot_Read]

@FixingSource = @FixingSource

,@FixTime = @FixTime

,@InstrumentId = @InstrumentId </frame>

</executionStack>

Proc [Database Id = 8 Object Id = 489219547] </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

</resource-list>

</deadlock>

TSQLLearner2014 SSC Veteran Points: 272 More actions · Answer 3

There is only one index - clustered index. The primary key is made of 3 colums FixingSource, FixTime, InstrumentID.

Phil Parkin SSC Guru Points: 246963 More actions · Answer 4

No doubt Gail will fix your problem more elegantly, but here is one idea (untested). In your script:

1) Define a temp table with the same structure as TableA.

2) Call the insert proc, with a modified INSERT clause to include an OUTPUT to the temp table you just created.

3) Modify your read proc to read from the temp table rather than TableA.

4) Drop the temp table - if you're feeling tidy 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) You simply MUST fix the IS NULL OR construct - and do so EVERYWHERE you have it in your code.

2) On the whole I would say that many tables, of more than a few rows anyway, can probably benefit from having at least one non-clustered index on them on something that is "searched/linked" that isn't the clustered index.

3) Non-clustered indexes are one of the main points of attack for resolving deadlock issues. Here's an oldy-but-goodie: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 additional parts to this blog series.

Also note that Extended Events can offer a VERY good window into deadlock information.

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

TSQLLearner2014 SSC Veteran Points: 272 More actions · Answer 6

Hi I have modified the query as directed. The filters in the query are on the columns that are part of the clustered index (primary key) so I am not sure if I can do anything further to fine tune the Indexes. I see the same behaviour even if I add a non-clusterd-covering index... Please help.

CREATE proc [dbo].[XYZ_Read]

@FixingSource VarChar(60) = null,@FixTime DateTime = null,@InstrumentId Int = null

as

set nocount on

select*

from[LimitOrderFixingSnapshot]

where([FixingSource] = CASE WHEN @FixingSource IS NULL THEN [FixingSource] ELSE @FixingSource END)

AND ([FixTime] = CASE WHEN @FixTime IS NULL THEN [FixTime] ELSE @FixTime END)

AND ([InstrumentId] = CASE WHEN @InstrumentId IS NULL THEN [InstrumentId] ELSE @InstrumentId END)

Phil Parkin SSC Guru Points: 246963 More actions · Answer 7

neltonk (7/7/2014)
Hi I have modified the query as directed. The filters in the query are on the columns that are part of the clustered index (primary key) so I am not sure if I can do anything further to fine tune the Indexes. I see the same behaviour even if I add a non-clusterd-covering index... Please help.
CREATE proc [dbo].[XYZ_Read]
@FixingSource VarChar(60) = null,@FixTime DateTime = null,@InstrumentId Int = null
as
set nocount on
select*
from[LimitOrderFixingSnapshot]
where([FixingSource] = CASE WHEN @FixingSource IS NULL THEN [FixingSource] ELSE @FixingSource END)
AND ([FixTime] = CASE WHEN @FixTime IS NULL THEN [FixTime] ELSE @FixTime END)
AND ([InstrumentId] = CASE WHEN @InstrumentId IS NULL THEN [InstrumentId] ELSE @InstrumentId END)

Did you take the time to read the link which Gail posted regarding catch-all queries?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

neltonk (7/7/2014)
Hi I have modified the query as directed. The filters in the query are on the columns that are part of the clustered index (primary key) so I am not sure if I can do anything further to fine tune the Indexes. I see the same behaviour even if I add a non-clusterd-covering index... Please help.
CREATE proc [dbo].[XYZ_Read]
@FixingSource VarChar(60) = null,@FixTime DateTime = null,@InstrumentId Int = null
as
set nocount on
select*
from[LimitOrderFixingSnapshot]
where([FixingSource] = CASE WHEN @FixingSource IS NULL THEN [FixingSource] ELSE @FixingSource END)
AND ([FixTime] = CASE WHEN @FixTime IS NULL THEN [FixTime] ELSE @FixTime END)
AND ([InstrumentId] = CASE WHEN @InstrumentId IS NULL THEN [InstrumentId] ELSE @InstrumentId END)

As Phil's post implies, your solution did absolutely nothing to remove the IS NULL or problem.

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

TSQLLearner2014 SSC Veteran Points: 272 More actions · Answer 9

Given the situation where we had to put a hot fix asap... This worked brilliantly. We have now time to fine tune the code.

I will now try using the recompile hint / dynamic sql within the code as suggested by Gail and test the impact.

Thanks for your help.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

TSQLLearner2014 (7/7/2014)
Given the situation where we had to put a hot fix asap... This worked brilliantly. We have now time to fine tune the code.
I will now try using the recompile hint / dynamic sql within the code as suggested by Gail and test the impact.
Thanks for your help.

I promise you your CODE did NOT make things "brilliantly faster"!! The simple fact that the sproc was compiled and the next call got you the most-often-fast-plan was what happened. Your IS NULL OR could do the same thing - be fast SOME (or even most) of the time.

Glad to hear you have time to put in a proper fix!!

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

TheSQLGuru (7/7/2014)
TSQLLearner2014 (7/7/2014)
Given the situation where we had to put a hot fix asap... This worked brilliantly. We have now time to fine tune the code.
I will now try using the recompile hint / dynamic sql within the code as suggested by Gail and test the impact.
Thanks for your help.
I promise you your CODE did NOT make things "brilliantly faster"!!

What that change would have done is make the performance consistent. Consistently not great, but consistent.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass