|
|
|
date: Thu, 22 Nov 2007 08:35:01 -0800,
group: microsoft.public.sqlserver.fulltext
back
Full-Text indexed column -> deadlocks in multithreaded application
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
}
}
date: Thu, 22 Nov 2007 08:35:01 -0800
author: heinzroth
RE: Full-Text indexed column -> deadlocks in multithreaded application
sorry, i forgot to attache the rest of the solution, unfortunately i there
doesn't seem to be a possibility to attach a zip file :/
// ******************
// Program.c
// ******************
using System;
using System.Collections.Generic;
using System.Text;
namespace SqlFullTextDeadlocks
{
public class Program
{
static void Main(string[] args)
{
int nrOfInserts = 5000;
int nrOfThreads = 50;
new SqlServerFullTextDeadlocksDemo(nrOfInserts, nrOfThreads);
}
}
}
// *********************
// DatabaseTestHelper
// *********************
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace SqlFullTextDeadlocks
{
public class DatabaseTestHelper
{
public static readonly string DB_NAME = "FulltextTest";
public static string CONNECTION_STRING =
String.Format("server=localhost;database={0};Integrated
Security=SSPI;Pooling='false'", DB_NAME);
public static void CreateDatabase()
{
CreateDatabase(DB_NAME);
}
public static void CreateDatabase(String dbName)
{
ExecuteNonQuery(
new SqlConnection("server=localhost;Integrated
Security=SSPI"),
String.Format("IF NOT EXISTS (SELECT * FROM sys.databases
WHERE name = '{0}') CREATE DATABASE {0}", dbName));
}
public static bool DropDatabaseSilent()
{
return DropDatabaseSilent(DB_NAME);
}
public static void DropDatabase()
{
DropDatabase(DB_NAME);
}
public static bool DropDatabaseSilent(String dbName)
{
bool retVal = true;
try
{
DropDatabase();
}
catch
{
retVal = false;
}
return retVal;
}
public static void DropDatabase(String dbName)
{
ExecuteNonQuery(
new SqlConnection("server=localhost;Integrated
Security=SSPI"),
String.Format("IF EXISTS (SELECT * FROM sys.databases WHERE
name = '{0}') DROP DATABASE {0}", dbName));
}
public static DbConnection CreateDbConnection()
{
return new SqlConnection(CONNECTION_STRING);
}
public static int ExecuteNonQuery(String statement)
{
using (DbConnection connection = CreateDbConnection())
{
return ExecuteNonQuery(connection, statement);
}
}
public static int ExecuteNonQuery(DbConnection connection, String
statement)
{
bool hasToBeClosed = false;
if (connection.State != ConnectionState.Open)
{
connection.Open();
hasToBeClosed = true;
}
try
{
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = statement;
return cmd.ExecuteNonQuery();
}
}
finally
{
if (hasToBeClosed)
{
connection.Close();
}
}
}
}
}
date: Fri, 23 Nov 2007 01:09:02 -0800
author: heinzroth
|
|
