Deadlock on insert for a table with an insert trigger that updates column in same triggered table

Question

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the customer that owns the resource (related via Resource -> Location -> Customer).

I have tried to set this 6 number using an after insert trigger. The logic works fine but causes deadlocks when I simulate many inserts coming in. I can eliminate this deadlock by adding a tablockx hint to the insert. However since Linq2SQL will be used to insert data I would like to avoid having to add a locking hint to the insert. I was hoping some combination of locking hints or index hints in the trigger might work instead.

The schema:

CREATE TABLE [Resource] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [nIdentifier]           INT NOT NULL DEFAULT 0,
    [fkLocation]            UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED ([pkGUID] ASC)
)
CREATE TABLE [Location] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [fkCustomer]            UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([pkGUID] 
)
CREATE TABLE [Customer] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([pkGUID] 
)

Currently there are no indexes on the Resource table except the clustered primary key.

The trigger:

CREATE TRIGGER [dbo].[Trigger_Resource_Insert]
ON [dbo].[Resource]
FOR INSERT
AS
BEGIN
    SET NoCount ON

    ;with relevantCustomers as (
        select c.pkGUID
        from inserted i
        join Location l on i.fkLocation = l.pkGUID
        join Customer c on l.fkCustomer = c.pkGUID
        group by c.pkGUID
    ), maxNumbers as (
        select rc.pkGUID, max = case when max(x.nIdentifier) < 100000 then 100000 else max(x.nIdentifier) end
        from relevantCustomers rc
        join Location l on l.fkCustomer = rc.pkGUID
        join Resource x on x.fkLocation = l.pkGUID
        group by rc.pkGUID
    ), numbers as (
        select i.pkGUID, number = row_number() over (partition by n.pkGUID order by i.pkGUID) + n.max
        from inserted i
        join Location l on i.fkLocation = l.pkGUID
        join maxNumbers n on n.pkGUID = l.fkCustomer
    )
    update x
    set nIdentifier = n.number
    from Resource x
    join numbers n on x.pkGUID = n.pkGUID
END

An insert generating a bunch of data:

;with x as (
    select num = 1
    union all
    select x.num + 1
    from x
    where x.num < {0}
), l as (
    select top {1} fkLocation = pkGUID, sLocationName from Location order by sLocationName
)
insert into Resource with (tablockx) (pkGUID, fkLocation, ...)
select newid(), l.fkLocation, ...
from x
cross join l

Deadlock graph from SQL profiler when running inserts without tablockx hint:

Answer 1

I have to say that I don't quite agree with some of the comments that state that what you're seeing is necessarily a framework problem. The Linq to SQL framework most certainly does allow you to specify a stored procedure as the means of input for rows.

That being said, I'd highly recommend you use that mechanism instead of a trigger if at all possible. You'll be able to add your locking hints inside the stored procedure and your inserts will work fine.

Your spec, however seems highly suspicious to me, and IMO the problems you seem to be running into right now are good counterexamples as to why one might not want to pursue a design such as this. So my (purely rhetorical) question is for the designer. What is that problematic sequential number supposed to represent? If it's a sequence in time, then why not just use a time and use the window functions to order the rows in something like a view instead (you can point Linq to SQL to views as well)? Is the intent to have some sort of gapless sequential number? Are you signed on to all of the complexity and locking you're going to have to deal with just to maintain that number?

Unfortunately, I think there is no real answer -- no magic combination of hints which will work across your frameworks and design -- which will solve your problem. IMO my answer is take a step back and look critically at your design.

Answer 2

seems like too much code for a trigger.

let's step back and look at what you are trying to achieve.

you want to set nIdentifier for any record you have inserted.

my solution would be to NOT use a trigger at all, i.e.

1. insert the records, use OUTPUT to capture what you have inserted into a temp table
2. use the temp table to join back to the Resource table and do your update