Refactoring SQL to avoid using TABLOCKX

I have two tables like this:

Table1             开发者_运维百科      Table2
----------------------------------
Table1Id IDENTITY        Table2Id
Table2Id NOT NULL        SomeStuff
                         SomeOtherStuff

With a foreign key constraint on Table2Id between them. It goes without saying (yet I'm saying it anyway) that a Table2 row needs to be inserted before its related Table1 row. The nature of the procedure that loads both tables does so in bulk set operations, meaning I have a whole bunch of Table1 and Table2 data in a @temp table that was created with an IDENTITY column to keep track of things. I am currently doing the inserts like this (transaction and error handling omitted for brevity):

DECLARE @currentTable2Id INT
SET @currentTable2Id = IDENT_CURRENT('dbo.Table2')
INSERT INTO dbo.Table2 WITH (TABLOCKX)
    ( SomeStuff, 
      SomeOtherStuff
    )
    SELECT WhateverStuff, 
           WhateverElse
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

INSERT INTO dbo.Table1 
    ( Table2Id )
    SELECT @currentTable2Id + SomeTempTableId
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

This works fine, all of the relationships are sound after the inserts. However, due to the TABLOCKX, we are running into constant situations where people are waiting for each other's queries to finish, whether it be this "load" query, or other UPDATES and INSERTS (I'm using NOLOCK on selects). The nature of the project calls for a lot of data to be loaded, so there are times when this procedure can run for 20-30 minutes. There's nothing I can do about this performance. Trust me, I've tried.

I cannot use SET IDENTITY_INSERT ON, as the DBAs do not allow users to issue this command in production, and I think using IDENTITY_INSERT would require a TABLOCKX anyways. Is there any way I can do this sort of insert without using a TABLOCKX?

---

Make sure you have a ID field in @SomeTempTable. Create a new column TempID in Table2. Insert the ID from @SomeTempTable to TempID when you add rows to Table2. Use column TempID in a join when you insert into Table1 to fetch the auto incremented Table2ID.

Something like this:

alter table Table2 add TempID int

go

declare @SomeTempTable table(ID int identity, WhateverStuff int, WhateverElse int)

insert into @SomeTempTable values(1, 1)
insert into @SomeTempTable values(2, 2)

insert into Table2(SomeStuff, SomeOtherStuff, TempID)
select WhateverStuff, WhateverElse, ID
from @SomeTempTable

insert into Table1(Table2Id)
select Table2ID
from @SomeTempTable as S
  inner join Table2 as T2
    on S.ID = T2.TempID

go

alter table Table2 drop column TempID    

Instead of add and drop of the TempID column you can have it in there but you need to clear it before every run so old values from previous runs don't mix up your joins.

---

I assume that you're using tablockx in an attempt to prevent anything else from inserting into Table2 (and thus incrementing the identity value) for the duration of your process. Try this instead

DECLARE @t TABLE (Table2Id int), @currentTable2Id int

INSERT INTO dbo.Table2
    ( SomeStuff, 
      SomeOtherStuff
    )
OUTPUT INSERTED.Table2Id into @t
    SELECT WhateverStuff, 
           WhateverElse
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

SELECT @currentTable2Id = Table2Id FROM @t

INSERT INTO dbo.Table1 
    ( Table2Id )
    SELECT @currentTable2Id + SomeTempTableId
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

DELETE @t