CODEWITHSUNDEEP
sql-servert-sqltransactionsdeadlock
David Work
David Work

Reputation: 279

Concurrent create or select statement failing with deadlock error

Where running the following SQL statement currently (maybe 30 or 40 times at the same time) and is giving us deadlock errors but we're not sure which statements are interfering with each other. There are no indexes on the Things table besides a PK and a Fk to ThingTypes table. We're also wondering if adding an index to ThingTypeId and HourId will help resolve the issue. Lastly it's also safe for us to assume that {@thingTypeID and @hourID} are unique for all the concurrent queries and the if is only there for if this was re-run at a later period in time.

Transaction (Process ID 237) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Code:

IF NOT EXISTS(select top(1) id from [eddsdbo].[Things] (UPDLOCK) 
              where [ThingTypeID] = @thingTypeID and [HourID] = @hourID)
BEGIN
    INSERT INTO [eddsdbo].[Things]
            ([ThingTypeID]
            ,[HourID])
        VALUES
            (@thingTypeID
            ,@hourID)

    SELECT m.*, mt.SampleType 
    FROM [eddsdbo].[Things] as m
    inner join [eddsdbo].[ThingTypes] as mt on mt.Id = m.ThingTypeID
    WHERE m.ID = @@IDENTITY
END
ELSE
BEGIN
    SELECT m.*, mt.SampleType 
    FROM [eddsdbo].[Things] as m
    inner join [eddsdbo].[ThingTypes] as mt on mt.Id = m.ThingTypeID
    where [ThingTypeID] = @thingTypeID and [HourID] = @hourID
END

We've been chasing down this issue for a while so any help is appreciated.

Upvotes: 1

Views: 99

Answers (1)

paparazzo
paparazzo

Reputation: 45096

Not sure this will fix it
But do you you really need the WHERE m.ID = @@IDENTITY

IF NOT EXISTS(select top(1) id from [eddsdbo].[Things] (UPDLOCK) 
              where [ThingTypeID] = @thingTypeID and [HourID] = @hourID)
BEGIN
    INSERT INTO [eddsdbo].[Things]
               ([ThingTypeID], [HourID])
        VALUES (@thingTypeID,  @hourID)
END

SELECT m.*, mt.SampleType 
FROM [eddsdbo].[Things] as m
inner join [eddsdbo].[ThingTypes] as mt 
  on mt.Id = m.ThingTypeID
 and [ThingTypeID] = @thingTypeID 
 and [HourID] = @hourID

A single statement is a transaction
I think this will have less overhead 

DECLARE @t AS TABLE (id int identity primary key, thingTypeID  int, hourID  int);
declare @thingTypeID int = 1, @hourID int = 2;

insert into @t (thingTypeID, hourID)  
values (@thingTypeID, @hourID);
select * 
from @T 
where thingTypeID = @thingTypeID and hourID = @hourID;

insert into @t (thingTypeID, hourID)
       select @thingTypeID, @hourID 
       where not exists (select 1 from @t where thingTypeID = @thingTypeID and hourID = @hourID);
select * 
from @T 
where thingTypeID = @thingTypeID and hourID = @hourID;

set @thingTypeID = 1;
set @hourID = 3;
insert into @t (thingTypeID, hourID)
       select @thingTypeID, @hourID  
       where not exists (select 1 from @t where thingTypeID = @thingTypeID and hourID = @hourID);
select * 
from @T 
where thingTypeID = @thingTypeID and hourID = @hourID;

select * 
from @T 
order by id;

Upvotes: 1

Related Questions