Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

Deadlock graph

share|improve this question
    
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 Nov 8 '12 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 Nov 8 '12 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 Nov 9 '12 at 14:42
    
Too true! It is very frustrating. –  sh54 Nov 12 '12 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. –  ypercube Jan 8 '13 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.

share|improve this answer
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 Dec 11 '12 at 22:28
    
It appears to me that the entire database design needs revision. –  HLGEM Jan 8 '13 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
share|improve this answer
    
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 Nov 12 '12 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 Nov 12 '12 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 Nov 12 '12 at 13:57
    
that is interesting... –  Jimbo Nov 12 '12 at 15:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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