Refactoring SQL to avoid using TABLOCKX

Question

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?

If you find your way to SQL Server 2008 you can use `merge` and `output` to get the job done. The solution I would use in SQL Server 2005 is either a loop (One row in `Table2` at a time) or add an extra column to `Table2` that holds the ID from the staging table and then use that value in a join when inserting to `Table1`. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id — Mikael Eriksson, Oct 07 '11 at 14:47
@Mikael - the original version of this procedure use cursors and loops. I was told to refactor the cursors out. — AJ., Oct 07 '11 at 15:46
@MikaelEriksson - Also, it looks like `merge` and `output` would be the perfect solution. Alas, its SQL Server 2005. — AJ., Oct 07 '11 at 17:19
Are you able to add an extra column to table2 or is that prohibited by the DBA's as well? — Mikael Eriksson, Oct 07 '11 at 17:22
Are you thinking of adding Table1Id to Table2? Hmmm...that might work. You should post as an answer so I can upvote :-) — AJ., Oct 07 '11 at 17:39
Could you break it into manageable sized bulk loads? Replacing one 20-minute batch with 250 batches that only lock your table for 5 seconds each (maybe with a 2 second sleep in between?) might give you acceptable service interruption while not hurting your bulk load performance too badly, either. — Kevin, Oct 07 '11 at 18:42
@kcrumley I'm not certain how I could do this, as right now it's all one giant body of SQL. — AJ., Oct 07 '11 at 19:53

score 2 · Answer 1 · answered Oct 07 '11 at 15:51

2

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

answered Oct 07 '11 at 15:51

Ben Thul

31,080
4
45
68

Ah ha. I will be trying this shortly and will let you know the results. Thanks! – AJ. Oct 07 '11 at 16:33
I'm not sure this will work. Without the lock, the ID values in @t won't necessarily be in strict order. I.e., they could be 1, 5, 6, 9...etc. SomeTempTableId is always directly sequential. Also, how would I know that @currentTable2Id would be the first in the sequence from @t without using an order by? – AJ. Oct 07 '11 at 17:02
@AJ - You are right. I ran some tests and in high concurrency there is no guarantee that id's in @T is without gaps. – Mikael Eriksson Oct 07 '11 at 17:12
I misread your initial code as doing an insert of one row at a time. If you change the @t temp table to have SomeTempTableId in addition to Table2Id, then you have a mapping that you can use to do your table1 insert. – Ben Thul Oct 07 '11 at 19:10

score 2 · Accepted Answer · answered Oct 07 '11 at 18:27

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 think this is going to work. If I have two instances of the same query running, each in its own transaction, is there a chance that the TempIDs will overlap? — AJ., Oct 10 '11 at 17:23
@AJ - it depends on what values you have for ID in the temp table. They have to be unique across all instaces that runs at the same time. — Mikael Eriksson, Oct 10 '11 at 17:37

Refactoring SQL to avoid using TABLOCKX

2 Answers2