Tablock hint triggers deadlocks

Question

I was inserting two data sets, using minimal logging, into an empty heap table using via two Execute SQL Tasks running in parallel and with SQL of the following form.

INSERT INTO Table (TABLOCK) SELECT FROM ...

After the job hangs a bit, one of the SQL tasks became a deadlock victim. Below is XML output of the deadlock graph.

Can someone explain what was happening under the hood?

  <resource-list>
   <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
    <owner-list>
     <owner id="process9609dc8" mode="Sch-S"/>
     <owner id="process9609dc8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5e13048" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
    <owner-list>
     <owner id="process5e13048" mode="Sch-S"/>
     <owner id="process5e13048" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9609dc8" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>

Things get a lot trickier because I found that for most cases the two Execute SQL Tasks can run in parallel successfully. Try below:

Create table dbo.TablockInsert (c1 int, c2 int, c3 int)

--then issue the script in two Execute Sql Task in parallel you won't fail:
insert into dbo.TablockInsert(TABLOCK) SELECT 1, 1, 1

Since the only difference is the SELECT... FROM... statement, looks like the SELECT... FROM... statement can have an impact on the lock mode here?

Answer 1

The Data Loading Performance Guide was written for SQL Server 2008 but as far as I can tell Microsoft hasn't made any improvements in this area for heaps. Here's a quote for your loading scenario:

Bulk Loading an Empty, Nonpartitioned Table

Loading data into a nonpartitioned table, while a simple operation, can be optimized in several ways.

...

Multiple, concurrent insert operations for heaps are possible only when the chosen bulk method issues bulk update (BU) locks on the table. Two bulk update (BU) locks are compatible, and hence two bulk operations can run at the same time.

In this scenario, both INSERT … SELECT and SELECT INTO have a drawback. Both of these operations take an exclusive (X), table level lock on the destination. This means that only one bulk load operation can run at a given time, limiting scalability. However, BCP, BULK INSERT, and Integration Services are all capable of taking bulk update (BU) locks – if you specify the TABLOCK hint.

The important part is that you don't get a BU lock with INSERT ... SELECT. You'll always get an exclusive lock on the table, so only one INSERT can run at a time.

In the comments you said that you'll insert 100k rows or less and that other processes won't be running on the tables during the inserts. When sending two INSERT queries to the database I would expect one of three things to happen:

1. One insert runs first and blocks the other insert. The second insert waits until the first insert is done.
2. One insert finishes before the second insert starts. There is no explicit blocking but they aren't run concurrently.
3. You get a deadlock and only one insert completes successfully.

In all cases you either benefit or aren't hurt by adding a TABLOCKX hint to the query, so that is my recommendation of working around the deadlock. If you want to know why the deadlock sometimes happens you'll need to look to another answer for that.

For in a different scenario in which you really need parallel insert, two ways of working around the BU issue are to partition your heap and to have each session insert into a separate partition or to load your data through BCP, BULK INSERT, or Integration Services.

Answer 2

You are inserting into dbo.TargetTable from two sessions and both using TABLOCK hint.Both process9609dc8 and process5e13048 process holding Sch-S and IX locks which are compatible with each other so both process can hold at the same time. But both wants to convert IX lock to Exclusive X type. X locks are not compatible with each other. Therefore, SQL server chose one of the session as deadlock victim instead of waiting infinitely for each other.

Basic deadlocking information.

Lock Compatibility (Database Engine) chart.

Detecting and Ending Deadlocks.