|
|
You're in the
sql server full text search
group:FTS and deadlocks
sql server full text search:
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 [quoted text, click to view] "Alex Sibilev" <admin_NOSPAM_@sql.ru> wrote in message news:57dc19c04b1bc8c85a11d0d5e5dc@msnews.microsoft.com... > > 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, > Alex > > >
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
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. -- 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 [quoted text, click to view] "Alex Sibilev" <admin_NOSPAM_@sql.ru> wrote in message news:57dc19c04b5028c85a43467ded62@msnews.microsoft.com... > 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, Alex > >
Hello Hilary,
[quoted text, click to view] > 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
Hello Hilary,
[quoted text, click to view] > 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
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 [quoted text, click to view] "Alex Sibilev" wrote:
> 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. > > > Alex > >
Incremental populations take far longer than a population which uses change
tracking. -- 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 [quoted text, click to view] "Alex Sibilev" <admin_NOSPAM_@sql.ru> wrote in message news:57dc19c04bc028c85d3324edf226@msnews.microsoft.com... > 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, Alex > >
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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |