3

I am having an issue similar to the one described here:

How do I force a refresh of a fulltext index within a transaction in mssql?

However, the recommended solution posted there does not work. I tried posting a follow up to the same thread, but it was deleted by a moderator. So I am starting a new question.

Similar to the original query I am also attempting to implement a unit test within a transaction. I would like to insert data into a full text indexed column, query the data to check its validity, and then roll back the insert afterward.

The problem is that the index does not seem to update until after I have committed the transaction. I have tried "WAITFOR DELAY" approach, but no matter how long I wait the index does not update until after the transaction is committed.

Here's a sample of what I'm trying to do:

BEGIN TRAN

INSERT INTO AMMS.Content
(
    ContentTypeId,
    Name,
    ImportDate,
    IsDeleted,
    LastModifiedBy,
    LastModifiedAt,
    DisplayInPortal,
    StatusId
)
VALUES
(
    4,
    'my unit test content',
    GETUTCDATE(),
    0,
    1,
    GETUTCDATE(),
    1,
    2
)

declare @count int
set @count = 0
while @count < 10
begin
    SELECT FULLTEXTCATALOGPROPERTY('PRIMARY', 'PopulateStatus') AS Status
    select * from amms.Content where contains(Name, 'unit')
    waitfor delay '00:00:01'
    set @count = @count + 1
end

The populate status stays at 9 and the select returns no rows as long as the transaction is pending. Once I commit the populate status returns 0 and the select returns a single row as expected.

Am I missing something? Is there another way to accomplish this? Is this behavior different under different versions of SQL Server? (I'm currently testing using 2008)

Community
  • 1
  • 1
Chris
  • 81
  • 1
  • 8

2 Answers2

1

I think you're out of luck. Trying to start a manual refresh inside a transaction...

begin tran
alter fulltext index on dbo.FTS_Table start full population

...gives me this message:

Msg 574, Level 16, State 0, Line 1 ALTER FULLTEXT INDEX statement cannot be used inside a user transaction.

What alternatives you have probably depend on how you manage your unit tests, perhaps you can just DELETE the data again, or DROP the table completely if it's only used for this test.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • Thanks Pondlife. There are many unit tests run against the same database. So the idea is to insert the data necessary for the test, execute the procedure/function/trigger being tested, verify the results, then rollback the data. The goal is to try to avoid having to manually delete the data afterward, but unless someone else has some clever ideas it looks like this is what we may have to resort to for tests that involve full text. – Chris Jan 10 '13 at 22:09
  • I'm not sure why you mention deleting "manually", unit tests are usually automated. And surely you would have the same issue with any unit test query, whether it uses FTS or not? But if no one else has a better idea, I think you have to address this as a question of how you organize and execute your unit test code, e.g. putting the `DELETE` into a tear down method that's executed after the unit test (or after a set of tests). – Pondlife Jan 10 '13 at 22:22
  • By manually deleting I mean that we would have to write a tear down script to delete the data that was inserted. As opposed to simply having it all rolled back by the transaction. The way our unit tests are structured none of the data for the test is ever committed. So a tear down script is unnecessary. So the problem is only relevant to tests that involve full text, since (as far as I can tell) the inserts must be committed before the full text indexes will update. – Chris Jan 11 '13 at 17:29
0

Essentially SQL Server won't let you carry out certain actions in transaction (or in snapshot).

The Indexing service in particular quite deliberately runs externally to user connections due to the risks of contention and the huge overhead text indexing imposes.

If you step back and consider the implications for any other database connections and for the current one of a transaction re-writing the full text indexes I'm sure you'll appreciate that in this case Microsoft have a point.

Ed Green
  • 128
  • 1
  • 2