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,
CREATE TABLE [Location] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [fkCustomer]            UNIQUEIDENTIFIER NOT NULL,
CREATE TABLE [Customer] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,

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]
    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

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:

Deadlock graph

  • Run Profiler to grab the query plan for the trigger code. I suspect what's happening is that that code is doing scans to satisfy the query (which acquires share locks) while at the same time holding exclusive locks on inserted rows. Put two threads together that both do that, and you will likely have deadlocks.
    – Jon Seigel
    Commented Nov 8, 2012 at 13:58
  • @JonSeigel In the insert there is only a clustered index insert for the Resource table. In the trigger the query plan is picking up a clustered index scan for that table happening in the maxNumbers CTE.
    – sh54
    Commented Nov 8, 2012 at 14:12
  • 3
    This seems to be one of those multiple cases when frameworks cause more problems than they solve. Your requirement is trivial to accomplish via a stored procedure. I guess that you would rather avoid stored procedures.
    – A-K
    Commented Nov 9, 2012 at 14:42
  • Too true! It is very frustrating.
    – sh54
    Commented Nov 12, 2012 at 13:58
  • 2
    You have joins on columns that have no index, these joins nested inside more joins without indexes, these inside a trigger that updates the table that gets the inserts. And then you try a "simple" recursive insert that on top has an order by another unindexed column. Commented Jan 8, 2013 at 15:52

2 Answers 2


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.

  • 1
    I was not saying it is impossible. I said this: "cause more problems than they solve". I meant that the amount of time spent customizing may be several time more than the amount of time spent on rolling out our own solution that does exactly what we want. In other words, it is cheaper to walk in shoes that fit your size.
    – A-K
    Commented Dec 11, 2012 at 22:28
  • It appears to me that the entire database design needs revision.
    – HLGEM
    Commented Jan 8, 2013 at 16:18

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
  • I agree that it is a lot of code for just setting a single identifying column. The big issue is that linq2SQL will be one solution used to insert data into the table so I really need the database to handle everything after it is given the insert.
    – sh54
    Commented Nov 12, 2012 at 9:57
  • Then use a stored procedure, supply the stored procedure with all the records or one record and let it do all the work as I described above.
    – Jimbo
    Commented Nov 12, 2012 at 13:07
  • It is unfortunately out of the question to insist that client applications use a sproc to populate the table. That would violate the spec and all current conventions used by applications currently interacting with this particular database.
    – sh54
    Commented Nov 12, 2012 at 13:57
  • that is interesting...
    – Jimbo
    Commented Nov 12, 2012 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.