Using WITH(NOLOCK) Together with FREETEXTTABLE

Question

I'm confused about how to use WITH (NOLOCK) along with full-text search.

From SQL Server NOLOCK and joins I got an important information that we should use WITH (NOLOCK) in each table in a SQL join. However, it doesn't work with this query:

SELECT * 
FROM myTable AS FT_TBL WITH (NOLOCK)
FULL OUTER JOIN
    FREETEXTTABLE (myTable, fieldName,  'SRI') AS KEY_TBL WITH (NOLOCK)
ON FT_TBL.id = KEY_TBL.[KEY]

The error message is:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.'

The problem is in the 'AS KEY_TBL WITH (NOLOCK)', because it works without the 'WITH (NOLOCK)' on it. What should I do? I want that every similar record appears in the search result although somebody else updating those records. Please help and thanks in advance.

Using set transaction isolation level read uncommitted should at least work. I assume you know all the problems related to using read uncommitted / nolock. — James Z, Feb 25 '15 at 03:41
See [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is ***NOT RECOMMENDED*** to use this everywhere - quite the contrary! — marc_s, Feb 25 '15 at 06:09
@JamesZ : Do you mean like this? http://sleepingcoder.blogspot.com/2011/04/control-locking-with-sql-full-text.html — user1397595, Feb 25 '15 at 06:11
Why do you think you need NOLOCK? Sounds like another case of the [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) - you have problem X and think Y (NOLOCK) is the answer. What is the real problem you are trying to solve? — Panagiotis Kanavos, Feb 25 '15 at 07:53

score 0 · Accepted Answer · answered Feb 25 '15 at 07:50

FREETEXTTABLE doesn't support table hints. If you want uncommitted reads throughout (which is what NOLOCK achieves for a single table), set the transaction isolation level before, either in T-SQL:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * 
FROM myTable AS FT_TBL
FULL OUTER JOIN
    FREETEXTTABLE (myTable, fieldName,  'SRI') AS KEY_TBL
ON FT_TBL.id = KEY_TBL.[KEY]

Or in your client code using IsolationLevel.ReadUncommitted.

But you should be fully aware of the consequences of this: READ UNCOMMITTED will indeed issue fewer locks, but it can give back inconsistent results. If all you're after is reads that don't block, a worthwhile alternative to explore is snapshot isolation and the READ_COMMITTED_SNAPSHOT database option. The problems with NOLOCK as well as the alternatives are eloquently summed up in this article.

Using WITH(NOLOCK) Together with FREETEXTTABLE

1 Answers1