FTS and deadlocks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex Sibilev - 09 Jun 2006 19:27 GMT

Hello,

I'm trying to figure out how to eliminate deadlocks in the system. It looks
like one process
involved in deadlock is process that populates FT index. The second process
- is replication procedure
that updates that ft-indexed table (tab1).

At the moment FT is setup with change tracking + scheduled update_index every
10 minutes.
Updates to the tables happen in blocks of 100-1000 transactions every 1 minute.
Under a heavy load, when
sp_fulltexttable 'update_index' is executing and replicated commands are
applied to the table we experience deadlocks situations.

Here is the example of logged deadlock:
---------------
2006-06-08 00:23:51.36 spid17s Error: 1205, Severity: 13, State: 51.
2006-06-08 00:23:51.36 spid17s Transaction (Process ID 17) was deadlocked
on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.
2006-06-08 00:23:51.36 spid20s deadlock-list
2006-06-08 00:23:51.36 spid20s deadlock victim=process33b0e38
2006-06-08 00:23:51.36 spid20s process-list
2006-06-08 00:23:51.36 spid20s process id=processfd55b8 taskpriority=0
logused=35132 waitresource=KEY: 5:72057594080198656 (ab00f7a18be1) waittime=3640
ownerId=237894155 transactionname=user_transaction lasttranstarted=2006-06-08T00:23:47.623
XDES=0x3dc33b68 lockMode=U schedulerid=8 kpid=20916 status=suspended spid=61
sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2006-06-08T00:23:47.657
lastbatchcompleted=2006-06-08T00:23:47.623 clientapp=SV_DATA hostname=SVSRV
hostpid=8400 loginname=SQL isolationlevel=read committed (2) xactid=237894155
currentdb=5 lockTimeout=4294967295 clientoption1=671156320 clientoption2=128056
2006-06-08 00:23:51.36 spid20s executionStack
2006-06-08 00:23:51.36 spid20s frame procname=FP.dbo.sp_MSupd_dbotab1
line=6 stmtstart=1292 stmtend=6640 sqlhandle=0x0300050019fc0c0771edd600d39700000100000000000000
2006-06-08 00:23:51.36 spid20s update "dbo"."tab1" set
2006-06-08 00:23:51.36 spid20s "sv_name" = case substring(@bitmap,1,1)
& 2 when 2 then @c2 else "sv_name" end
..skipped
2006-06-08 00:23:51.36 spid20s inputbuf
2006-06-08 00:23:51.36 spid20s Proc [Database Id = 5 Object Id = 118291481]

2006-06-08 00:23:51.36 spid20s process id=process33b0e38 taskpriority=20
logused=189668 waitresource=KEY: 5:72057594080198656 (7800019533e2) waittime=3609
ownerId=237893973 transactionname=ftAutoInterface lasttranstarted=2006-06-08T00:23:47.577
XDES=0x361174e0 lockMode=U schedulerid=15 kpid=20240 status=background spid=17
sbid=0 ecid=0 priority=0 transcount=0
2006-06-08 00:23:51.36 spid20s executionStack
2006-06-08 00:23:51.36 spid20s inputbuf
2006-06-08 00:23:51.36 spid20s resource-list
2006-06-08 00:23:51.36 spid20s keylock hobtid=72057594080198656 dbid=5
objectname=FP.sys.fulltext_index_map_70291310 indexname=i1 id=lock3dab9d80
mode=X associatedObjectId=72057594080198656
2006-06-08 00:23:51.36 spid20s owner-list
2006-06-08 00:23:51.36 spid20s owner id=processfd55b8 mode=X
2006-06-08 00:23:51.36 spid20s waiter-list
2006-06-08 00:23:51.36 spid20s waiter id=process33b0e38 mode=U requestType=wait
2006-06-08 00:23:51.36 spid20s keylock hobtid=72057594080198656 dbid=5
objectname=FP.sys.fulltext_index_map_70291310 indexname=i1 id=lock53155540
mode=X associatedObjectId=72057594080198656
2006-06-08 00:23:51.36 spid20s owner-list
2006-06-08 00:23:51.36 spid20s owner id=process33b0e38 mode=X
2006-06-08 00:23:51.36 spid20s waiter-list
2006-06-08 00:23:51.36 spid20s waiter id=processfd55b8 mode=U requestType=wait
-------------------------

I would appreciate any help with this issue. I'm running out of options.
Previously FT was configured with background index updates - but that was
even worse.

Many thanks,
Ale

Reply to this Message

Hilary Cotter - 09 Jun 2006 20:56 GMT

Hey Alex, what is the resource_usage?

select fulltextserviceproperty('resourceusage')

See what happens if you set it to 1

sp_fulltext_service 'resource_usage', 1

> Hello,
>
[quoted text clipped - 73 lines]
> Many thanks,
> Alex

Reply to this Message

Alex Sibilev - 10 Jun 2006 01:21 GMT

Hello Hilary,

> select fulltextserviceproperty('resourceusage')
>
> See what happens if you set it to 1
>
> sp_fulltext_service 'resource_usage', 1

I have that situation on 2 servers whihch have the same data. On one resource_usage

was set to 5 on another to 3. Changed to 1 - didn't make any visible difference.

I have started to get a feeling that it might be because of "change tracking"
turned on.
However I don't think that by adding timestamp column and using incremental
population
without change tracking it will work faster or produce less locks. Or I'm
wrong here?

Regards, Ale

Reply to this Message

Hilary Cotter - 10 Jun 2006 11:58 GMT

Use change tracking over the other population methods if the tables are big.

The timestamp column won't really help you with change tracking once the
initial population has completed.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hello Hilary,
>
[quoted text clipped - 16 lines]
>
> Regards, Alex

Reply to this Message

Alex Sibilev - 10 Jun 2006 12:14 GMT

Hello Hilary,

> Use change tracking over the other population methods if the tables
> are big.
>
> The timestamp column won't really help you with change tracking once
> the initial population has completed.

Thank for your reply.

Table is quite big 2-3M records. If change tracking is recommended way of
FT indexing large tables, then how do I get rid of locks which blocks updating
underlying table. As I see it - "change tracking" analyses modified record
and if FT-indexed column(s) been modified then it add this record PK id
to the list of documents required reindexing (fulltext_index_map??) When
I call sp_fulltext_table 'update_index' it gets these IDs reindexes documents
and removes them from the list. Both processes lock fulltext_index_map and
under certain conditions deadlocks occur. Plus "change tracking" need some
resources to perform all those things.

As for the timestamp I mean using it with incremental population and change
tracking turned off.

Ale

Reply to this Message

Jingwei Lu - 11 Jun 2006 23:05 GMT

Hi Alex,

Let me first explain how deadlock is expected in this kind of situation.
When you have change tracking turned on(manual or auto), every DML operation
to your table will trigger an update/insert to our internal tracking table.
When auto crawl running at the background(if you set change tracking to
auto), it will continousely scan the internal tracking table to crawl the
document. Now you can see because user can update documents in any order it
is quite possible that our background crawl task will deadlock with user DML
if both try to lock multiple documents out of order at same time. The
background crawl task will always be deadlock victim and it will retry.

To reduce deadlock you can set change tracking to manual and start crawl
manually when system load is not high. Our recommandation of using full-text
with replication is to use incremental crawl when you expect heavy load(build
a secondary index on your time stamp column and issue crawl manualy when
system load is low). Change tracking has a trigger cost when replication
replays the change on destination database. It is better to be turned off in
your case.

In any case you should avoid crawl and heavy load of replication at same
time. Deadlock could happen as long as user update and crawl running at same
time. Logically we will handle dealock correctly but it will hurt your
performance.

thanks,

Jingwei

> Hello Hilary,
>
[quoted text clipped - 20 lines]
>
> Alex

Reply to this Message

Alex Sibilev - 13 Jun 2006 19:03 GMT

Hello Jingwei,

Thanks for you response.
That's how I thought it works.

In case of incremental crawl with change tracking turned off - you don't
use internal table and thus there is less room for deadlock to happen?
The only issues with incremental population is that it would reindex the
data even if ft-indexed fields didn't change.

Regards, Ale

Reply to this Message

Hilary Cotter - 13 Jun 2006 22:08 GMT

Incremental populations take far longer than a population which uses change
tracking.

Signature

> Hello Jingwei,
>
[quoted text clipped - 6 lines]
>
> Regards, Alex

Reply to this Message