Search

Concurrent Full Text CONTAINSTABLE queries deadlocking on each other by J. Granden

Active

1
0
Sign in
to vote
Type: Bug
ID: 774058
Opened: 12/11/2012 11:38:42 AM
Access Restriction: Public
0
Workaround(s)
0
User(s) can reproduce this bug
We're experiencing 10-20 deadlocks per hour bewteen multiple instances of this query:

"SELECT i.[key] FROM    CONTAINSTABLE(items, SearchTerms, @IncludeContainsCondition)"

The problematic resource is a Sch-M lock on a "COMPRESSED_FRAGMENT"

HW is 4 node numa with 80 procs.

(full deadlock graph will be attached shortly)
Resource list from deadlock graph:

<resource-list>
<metadatalock subresource="COMPRESSED_FRAGMENT" classid="object_id = 2079398527, fragment_id = 10858772" dbid="15" id="lock456efcd180" mode="Sch-S">
<owner-list>
    <owner id="process723196b0c8" mode="Sch-S" />
</owner-list>
<waiter-list>
    <waiter id="process6a91518cf8" mode="Sch-M" requestType="wait" />
</waiter-list>
</metadatalock>
<metadatalock subresource="COMPRESSED_FRAGMENT" classid="object_id = 2079398527, fragment_id = 9903010" dbid="15" id="lock539185f080" mode="Sch-M">
<owner-list>
    <owner id="process95a4cf8" mode="Sch-S" requestType="wait" />
</owner-list>
<waiter-list>
    <waiter id="process723196b0c8" mode="Sch-S" requestType="wait" />
</waiter-list>
</metadatalock>
<metadatalock subresource="COMPRESSED_FRAGMENT" classid="object_id = 2079398527, fragment_id = 9903010" dbid="15" id="lock539185f080" mode="Sch-M">
<owner-list>
    <owner id="process6a91518cf8" mode="Sch-M" />
</owner-list>
<waiter-list>
    <waiter id="process95a4cf8" mode="Sch-S" requestType="wait" />
</waiter-list>
</metadatalock>
</resource-list>
Details (expand)

Product Language

English

Version

SQL Server 2012 - Enterprise Core Edition

Category

SQL Engine

Operating System

Windows Server 2008 R2 Enterprise

Operating System Language

US English

Steps to Reproduce

Populate a fulltext indexed table with 600,000 rows. [SearchTerms] column is 25-30 words long.

create heavy churn on the table (approx 125,000 rows/day are deleted, 125, 000 new rows are inserted throughout the day).

Perform 40-60 CONTAINSTABLE queries per second.

Actual Results

CONTAINS queries are deadlocking on each other 10-20 times per hour.

Also, CONTAINS queries are periodically deadlocking against background threads as well.

Expected Results

No deadlocks because these are SELECT statements.

Platform

X64

Virtualization

 
File Attachments
File Name Submitted By Submitted On File Size  
fti_deadlock_graph.xml 12/11/2012 5 KB
Sign in to post a comment.
Posted by Microsoft on 4/23/2013 at 7:41 PM
Thanks for reporting. We are currently investigating.
Posted by J. Granden on 12/11/2012 at 11:51 AM
Version info:
Microsoft SQL Server 2012 - 11.0.2383.0 (X64)
    Oct 5 2012 19:35:54
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Sign in to post a workaround.