Tablock hint triggers deadlocks

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?

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?

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:

• One insert runs first and blocks the other insert. The second insert waits until the first insert is done.

• One insert finishes before the second insert starts. There is no explicit blocking but they aren't run concurrently.

• 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.

StackExchange Database Administrators Q#172850, answer score: 8