Full-Text indexed column -> deadlocks in multithreaded application

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi group!

I'm facing the the following problem: I want a full-text indexed column
where records can get inserted constantly when under heavy load. The c#
application (can be distributed) and works multithreaded on the same SQL
server instance.

Under heavy load from multiple threads, I get the following SqlException:

SqlException: Transaction (Process ID 73) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the
transaction.

There are no foreign keys and no SELECTs on the table at all. Change
Tracking is set to manual, no schedule is defined.

If a simply disable the full-text index creation, everything works fine, no
deadlocks.

SQL Profiler shows me the deadlock graph attached below. The source code for
a very simple c# class producing the exception above is also attached after
the deadlock graph.


<deadlock-list>
<deadlock victim="processd15c18">
<process-list>
<process id="processd15c18" taskpriority="0" logused="388"
waitresource="KEY: 7:72057594038517760 (6f00ef79b8bf)" waittime="3744"
ownerId="551937" transactionname="user_transaction"
lasttranstarted="2007-11-22T15:42:59.437" XDES="0x837dd330"
lockMode="RangeI-N" schedulerid="2" kpid="5932" status="suspended" spid="56"
sbid="0" ecid="0" priority="0" transcount="2"
lastbatchstarted="2007-11-22T15:42:59.437"
lastbatchcompleted="2007-11-22T15:42:59.437" clientapp=".Net SqlClient Data
Provider" hostname="host" hostpid="6564" loginname="user"
isolationlevel="read uncommitted (1)" xactid="551937" currentdb="7"
lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="90"
sqlhandle="0x02000000d773b935dad97dd61f78e99d7e78dbe9e9368951">
INSERT INTO DATA (Guid, SearchableString) VALUES (@guid, @string) </frame>
<frame procname="unknown" line="1"
sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@guid uniqueidentifier,@string nvarchar(11))INSERT INTO DATA (Guid,
SearchableString) VALUES (@guid, @string) </inputbuf>
</process>
<process id="process83148088" taskpriority="0" logused="388"
waitresource="KEY: 7:72057594038517760 (ffffffffffff)" waittime="3744"
ownerId="549853" transactionname="user_transaction"
lasttranstarted="2007-11-22T15:42:57.367" XDES="0x81e4b330"
lockMode="RangeS-U" schedulerid="1" kpid="3948" status="suspended" spid="58"
sbid="0" ecid="0" priority="0" transcount="2"
lastbatchstarted="2007-11-22T15:42:57.367"
lastbatchcompleted="2007-11-22T15:42:57.367" clientapp=".Net SqlClient Data
Provider" hostname="host" hostpid="6564" loginname="user"
isolationlevel="read uncommitted (1)" xactid="549853" currentdb="7"
lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="90"
sqlhandle="0x02000000d773b935dad97dd61f78e99d7e78dbe9e9368951">
INSERT INTO DATA (Guid, SearchableString) VALUES (@guid, @string) </frame>
<frame procname="unknown" line="1"
sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@guid uniqueidentifier,@string nvarchar(11))INSERT INTO DATA (Guid,
SearchableString) VALUES (@guid, @string) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594038517760" dbid="7"
objectname="FulltextTest.sys.fulltext_index_map_2089058478" indexname="i1"
id="lock8012bf80" mode="RangeS-U" associatedObjectId="72057594038517760">
<owner-list>
<owner id="process83148088" mode="RangeS-U"/>
</owner-list>
<waiter-list>
<waiter id="processd15c18" mode="RangeI-N" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594038517760" dbid="7"
objectname="FulltextTest.sys.fulltext_index_map_2089058478" indexname="i1"
id="lock838af200" mode="RangeI-N" associatedObjectId="72057594038517760">
<owner-list>
<owner id="processd15c18" mode="RangeI-N"/>
</owner-list>
<waiter-list>
<waiter id="process83148088" mode="RangeS-U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>


// ***********************
// Source Code:
// ***********************

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Data.Common;

namespace SqlFullTextDeadlocks
{
public class SqlServerFullTextDeadlocksDemo
{
public SqlServerFullTextDeadlocksDemo(int nrOfInserts, int
nrOfThreads)
{
// create database and table, setup fulltext
SetupDB();

// setup threads & objects to insert
IList<MyObject>[] eventObjects = new IList<MyObject>[nrOfThreads];
Thread[] threads = new Thread[nrOfThreads];

for (int i = 0; i < nrOfThreads; i++)
{
ParameterizedThreadStart ts = InsertMyObjects;
threads[i] = new Thread(ts);
eventObjects[i] = CreateMyObjects(nrOfInserts / nrOfThreads);
}

// start threads and wait for them to finish
Console.Out.WriteLine("Start");
DateTime start = DateTime.Now;

for (int i = 0; i < nrOfThreads; i++)
{
threads[i].Start(eventObjects[i]);
}

for (int i = 0; i < nrOfThreads; i++)
{
threads[i].Join();
}

TimeSpan duration = DateTime.Now - start;
Double eps = nrOfInserts / duration.TotalSeconds;
Console.Out.WriteLine("Finished processing {0} objects in {1} ms
({2} per second)",
nrOfInserts, duration.TotalMilliseconds,
eps);

Console.Out.WriteLine("Press any key to close ...");
Console.ReadKey();
}

#region private helpers

private static void SetupDB()
{
DatabaseTestHelper.DropDatabase();
DatabaseTestHelper.CreateDatabase();

DatabaseTestHelper.ExecuteNonQuery("dbo.[sp_fulltext_database]
@action = 'enable'");
DatabaseTestHelper.ExecuteNonQuery("Create fulltext catalog
FTCatalog");

DatabaseTestHelper.ExecuteNonQuery(
@"CREATE TABLE [dbo].[Data](
[Guid] [uniqueidentifier] NOT NULL,
[SearchableString] [nvarchar](max) NOT NULL,
CONSTRAINT [ui_myIndex] UNIQUE NONCLUSTERED ([Guid] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]) ON [PRIMARY]");

DatabaseTestHelper.ExecuteNonQuery(
@"CREATE FULLTEXT INDEX ON
[dbo].[Data] (SearchableString) KEY INDEX ui_myIndex ON
FTCatalog WITH CHANGE_TRACKING MANUAL");
}

private static void InsertMyObjects(object eventObjectsObj)
{
IList<MyObject> eventObjects = (IList<MyObject>)eventObjectsObj;

foreach (MyObject myObj in eventObjects)
{
try
{
using (DbConnection connection =
DatabaseTestHelper.CreateDbConnection())
{
connection.Open();
using (DbTransaction tx =
connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
using (IDbCommand cmd =
connection.CreateCommand())
{
IDataParameter guidParam =
cmd.CreateParameter();
guidParam.ParameterName = "guid";
guidParam.DbType = DbType.Guid;
guidParam.Value = myObj.Guid;

IDataParameter stringParam =
cmd.CreateParameter();
stringParam.ParameterName = "string";
stringParam.DbType = DbType.String;
stringParam.Value = myObj.SearchableString;

cmd.Transaction = tx;
cmd.CommandText = "INSERT INTO DATA (Guid,
SearchableString) VALUES (@guid, @string)";
cmd.Parameters.Add(guidParam);
cmd.Parameters.Add(stringParam);
cmd.ExecuteNonQuery();
}
tx.Commit();
}
}
}
catch (Exception e)
{
Console.Out.WriteLine(String.Format("Exception:
{0}\n{1}", e.Message, e.StackTrace));
}
}
}

private static IList<MyObject> CreateMyObjects(int count)
{
IList<MyObject> retVal = new List<MyObject>();
for (int i = 0; i < count; i++)
{
retVal.Add(new MyObject("some string"));
}
return retVal;
}

class MyObject
{
private readonly Guid _guid;
private readonly String _searchableString;

public MyObject(String searchableString)
{
_searchableString = searchableString;
_guid = Guid.NewGuid();
}

public Guid Guid
{
get { return _guid; }
}

public String SearchableString
{
get { return _searchableString; }
}
}

#endregion private helpers
}
}
.

Relevant Pages

  • Re: Getting a GUID
    ... Private Type GUID ... Private Declare Function CoCreateGuid Lib "ole32" _ ... (pGuid As GUID) ... Private Function CreateGUIDAs String ...
    (microsoft.public.vb.winapi)
  • Re: Single Instance web service??
    ... Basically what I did is generate a Guid every time the static memebr is ... private Guid _guid; ... I will make my web service stateless, but I still would like to know how ... Basically, you don't have to worry much about it if you don't use statics, ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: Error with VB/Access StringFromGUID
    ... If you add a class module to the project called clsGUID, ... Private Sub Command1_Click ... Lib "ole32.dll" (tGUIDStructure As GUID) As Long ... Private Declare Function StringFromGUID2 _ ...
    (microsoft.public.vb.general.discussion)
  • Re: I Think I Found The Problem
    ... private void InitializeComponent() ... Guid jobID = cbOpenJobs.SelectedValue; ... Environment.NewLine, foundRowIndex, jobNumber, jobID); ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: problem with catching event of powerpoint in C#
    ... Powerpoint 10.0 Object Library". ... private PowerPoint.Application m_app; ... // Get the GUID of the EApplication interface. ... public void SlideShowNextBuild ...
    (microsoft.public.powerpoint)