 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / January 2005Ignore table lock with CONTAINSTABLE
Hi, Is there a way to ignore table locks when using the CONTAINSTABLE statement ? Here is a code sample : SELECT [KEY], RANK FROM CONTAINSTABLE(MyTable, *, 'MySearchTerm') as S, MyTable WITH(NOLOCK) WHERE MyTable.ID = S.[KEY] This query cannot return results when there is a lock on "MyTable", even with the (NOLOCK) statement. Is there a specific syntax to use to return rows while LOCK is still active ? It works with the following query : SELECT * FROM MyTable WITH (NOLOCK) WHERE CONTAINS(MySearchColumn, 'MySearchTerm') But I prefer using the CONTAINSTABLE statement, as I've got a lot of rows in "MyTable", and need to limit the number of rows returned.. Thanks, Fred. Fred, What is the exact version of SQL Server you using? Could you post the full output of -- SELECT @@version -- as this is most helpful in troubleshooting SQL FTS issues such as this one. Table locks held by a SELECT... CONTAINSTABLE should not be occurring for any length of time as this is seldom an issue. Perhaps the best approach to this issue is to determine what is causing the locks in the first place. What is causing the locking on your "MyTable"? Are you doing extensive update/inserts/deletes of a range of rows from this table when the SELECT... CONTAINSTABLE query is executed? Thanks, John  SignatureSQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ > Hi, > [quoted text clipped - 19 lines] > > Thanks, Fred. Hi John, Here is the SELECT @@Version result : Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: ) >Are you doing extensive update/inserts/deletes of >a range of rows from this table when the SELECT.. >CONTAINSTABLE query is executed? Yes. This only happens when extensive updates are made on the full text column (there is only one FT column), but "dirty read" should not be a problem for the full text index in my case, as the populated data does not necessarily need to match with the table data (I don't use Change tracking, only Full updates, as almost all the data is renewed every 24 hours, and does not change then for that period). The FT column preparation (remove accents, add some data from other table) can take a few minutes, and creates the lock : but in my case I cannot stop search, even for these few minutes. When updates are not in progress, there is no problem, everything is working fine. Fred In fact, this issue could also be summarized as : I'm looking for the WITH(NOLOCK) (used for standard Tables) equivalent for CONTAINSTABLE statement, if it exists.. :-) Fred can you post the results of sp_lock here? SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html > Hi, > [quoted text clipped - 19 lines] > > Thanks, Fred. >can you post the results of sp_lock here? That's when the FT column update is in progress : 51 4 0 0 DB S GRANT 52 4 0 0 DB S GRANT 53 1 85575343 0 TAB IS GRANT 54 7 1563152614 0 TAB IS GRANT 54 7 137767548 1 KEY (c50084adf097) S GRANT 54 7 137767548 0 TAB IS GRANT 54 7 1216723387 0 TAB Sch-S GRANT 54 7 0 0 DB S GRANT 54 7 137767548 1 PAG 1:4136 IS GRANT 54 7 857770113 0 TAB X GRANT 54 7 1563152614 1 KEY (480031501b80) S GRANT 54 7 1563152614 1 PAG 1:454 IS GRANT Thanks, Fred Fred, SPID 54 has a "TAB" lock on your FT-enable table. Specifically, this means that the "Entire table, including all data and indexes" are locked. As I said in my original post - "Perhaps the best approach to this issue is to determine what is causing the locks in the first place" - and you should instead look to reducing the length of the transactions, i.e., a smaller number of rows affected, performed more frequently. As using CONTAINSTABLE with NOLOCK is not the issue here, as any SELECT will be blocked in this situation until the TAB lock is released on this table. Regards, John SignatureSQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ > >can you post the results of sp_lock here? > That's when the FT column update is in progress : [quoted text clipped - 16 lines] > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! Ok thans John, I will take a look on that point, if there is no other solution.. But what I cannot understand is why CONTAINSTABLE is hold by locks, whereas it's data is populated once, and isn't modified until the next Population (when Change Tracking is disabled). It means that even if data in the indexed table is modified, the CONTAINSTABLE table is not modified in real time. I can't understand why it should wait for the lock to be queried. A query like this one below should return results immediatly : SELECT [KEY], RANK FROM CONTAINSTABLE(MyTable, *, 'MySearchTerm') Do you see what I mean ? Fred You're welcome, Fred, I was going to follow-up and ask what type of FT Indexing you have on your FT-enable table, i.e.. Full, Incremental or "Change Tracking" with "Update Index in Background". When you disable, CT do you also disenable "Update Index in Background"? From you sp_lock info (edited), the table also has schema, page and key locks: 54 7 137767548 1 KEY (c50084adf097) S GRANT 54 7 1216723387 0 TAB Sch-S GRANT 54 7 137767548 1 PAG 1:4136 IS GRANT 54 7 857770113 0 TAB X GRANT 54 7 1563152614 1 KEY (480031501b80) S GRANT 54 7 1563152614 1 PAG 1:454 IS GRANT Sch-S or "schema locks are: schema modification (Sch-M) and schema stability (Sch-S). " See, SQL 2000 BOL title "Understanding Locking in SQL Server" for more info on lock types. What type of transactions are you doing against this table when the containstable query is executed? Regards, John SignatureSQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ > Ok thans John, I will take a look on that point, if there is no other > solution.. [quoted text clipped - 16 lines] > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.