代码之家  ›  专栏  ›  技术社区  ›  David Work

并发create或select语句失败,出现死锁错误

  •  1
  • David Work  · 技术社区  · 7 年前

    其中,当前正在运行以下SQL语句(可能同时运行30或40次),这会给我们带来死锁错误,但我们不确定哪些语句相互干扰。除了PK和Fk to ThingTypes表之外,Things表上没有索引。我们还想知道向ThingTypeId和HourId添加索引是否有助于解决此问题。最后,我们还可以安全地假设{@thingTypeID和@hourID}对于所有并发查询都是唯一的,if只在稍后重新运行时才存在。

    事务(进程ID 237)在另一个进程的锁资源上处于死锁状态,已被选为死锁受害者。重新运行事务

    代码:

    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
    

    我们已经关注这个问题一段时间了,所以非常感谢您的帮助。

    1 回复  |  直到 7 年前
        1
  •  1
  •   paparazzo    7 年前

    不确定这能解决问题
    但是你真的需要 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
    

    单个语句是一个事务
    我想这会减少开销

    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;