committed inserted data not immediately available for select involving full text search

Question

I have 2 stored procedures - simplified/pseudo code:

CREATE PROCEDURE [SomeSchema].[Sproc1]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION X;

        -- Insert lots of data

        COMMIT TRANSACTION X;
    END TRY
    BEGIN CATCH
        SELECT  
            @ErrorNumber = ERROR_NUMBER() ,
            @ErrorSeverity = ERROR_SEVERITY() ,
            @ErrorState = ERROR_STATE() ,
            @ErrorProcedure = ERROR_PROCEDURE() ,
            @ErrorLine = ERROR_LINE() ,
            @ErrorMessage = ERROR_MESSAGE();

        ROLLBACK TRANSACTION X;
     END CATCH; 
END;

CREATE PROCEDURE [SomeSchema].[Sproc2]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION X;

        -- Perform full text search on old and inserted data and return

        COMMIT TRANSACTION X;
    END TRY
    BEGIN CATCH
        SELECT  
            @ErrorNumber = ERROR_NUMBER() ,
            @ErrorSeverity = ERROR_SEVERITY() ,
            @ErrorState = ERROR_STATE() ,
            @ErrorProcedure = ERROR_PROCEDURE() ,
            @ErrorLine = ERROR_LINE() ,
            @ErrorMessage = ERROR_MESSAGE();

        ROLLBACK TRANSACTION X;
     END CATCH; 
END;

The first stored procedure Sproc1 inserts some data into several normalized tables. The second Sproc2 then selects data from the database using full text search. I run both stored procedures as follows:

EXEC [SomeSchema].[Sproc1]
EXEC [SomeSchema].[Sproc2]

Unfortunately data inserted via Sproc1 is not yet available when Sproc2 is run - only after about 1-3 seconds (guesstimate). What could be the reason for this? Should all this not be synchronous/atomic - i.e. the data should be available/selectable at the time Sproc2 executes?

Any suggestions to enforce that data insert/index is completed before Sproc2 is invoked would be very much appreciated. Thanks.

PS:

Just isolated the problem a sproc that is invoked inside Sproc2. This sproc uses sp_executesql and does not run inside a transaction. Not sure why this causes problems though ...

PPS:

It all seems to be related to full text search. This is part of my SSDT post-deployment script:

CREATE FULLTEXT CATALOG [SomeFullTextCatalog]
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT;
CREATE UNIQUE CLUSTERED INDEX ClusteredIndex_SomeView 
    ON [SomeSchema].[SomeView] (SomeId);
GO
CREATE FULLTEXT INDEX ON [SomeSchema].[SomeView ](
[Some1] LANGUAGE 'British English', 
[Some2] LANGUAGE 'British English', 
[Some3] LANGUAGE 'British English', 
[Some4] LANGUAGE 'British English')
KEY INDEX [ClusteredIndex_SomeView] ON ([SomeFullTextCatalog], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

How can I 'refresh' this after an insert?

I can do:

I understand that I can do:

SELECT FULLTEXTCATALOGPROPERTY('SomeFullTextCatalog', 'PopulateStatus') AS Status

to check the status of the full text catalog and wait until its value is 0 again. Is this possible?

nope it is read committed tried dirty read and everything. Just isolated the problem a sproc that is invoked inside Sproc2. This sproc uses sp_executesql and does not run inside a transaction. Not sure why this causes problems though ... — cs0815, Jul 06 '16 at 14:22

score 0 · Answer 1 · answered Jul 06 '16 at 14:26

0

Do you need to recreated the full text index again, actually you need to do it once a day, to get all the inserted data for that day available.

answered Jul 06 '16 at 14:26

Cristian Jose Brito

1

It seems to work about 2 seconds later though without having to recreate the full text index though. Btw, I do my full text index against a view. How can force this to be refreshed after the insert? – cs0815 Jul 06 '16 at 14:36

score 0 · Accepted Answer · edited May 23 '17 at 12:32

The problem was that the full text index needs some time to refresh. To create a wait for this I have used this sproc taken from here:

CREATE PROCEDURE [Core].[USP_Core_WaitForFullTextIndexing]
    @CatalogName VARCHAR(MAX)
AS
BEGIN
    DECLARE @status int;
    SET @status = 1;
    DECLARE @waitLoops int;
    SET @waitLoops = 0;

    WHILE @status > 0 AND @waitLoops < 100
    BEGIN       
        SELECT @status = FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        FROM sys.fulltext_catalogs AS cat;

        IF @status > 0
        BEGIN
            -- prevent thrashing
            WAITFOR DELAY '00:00:00.1';
        END
        SET @waitLoops = @waitLoops + 1;
    END
END

committed inserted data not immediately available for select involving full text search

2 Answers2