SQL Server 2008 插入后运行触发器，更新锁定原始表 [英] SQL Server 2008 Running trigger after Insert, Update locks original table

问题描述

我有一个严重的性能问题.

I have a serious performance problem.

我有一个数据库(与这个问题相关)，有 2 个表.

I have a database with (related to this problem), 2 tables.

1 表包含带有一些全局信息的字符串.第二个表包含分解为每个单独单词的字符串.所以这个字符串就像在第二个表中索引一样，一个字一个字.

1 Table contains strings with some global information. The second table contains the string stripped down to each individual word. So the string is like indexed in the second table, word by word.

第二个表中数据的有效性不如第一个表中数据的有效性重要.

The validity of the data in the second table is of less important then the validity of the data in the first table.

由于第一个表可以像 1*10^6 记录一样增长，而第二个表对于 1 个字符串的平均 10 个单词可以像 1*10^7 记录一样增长，我使用 nolock 来读取其次，这让我可以自由地插入新记录而不锁定它(期望对两个表进行多次读取).

Since the first table can grow like towards 1*10^6 records and the second table having an average of like 10 words for 1 string can grow like 1*10^7 records, i use a nolock in order to read the second this leaves me free for inserting new records without locking it (Expect many reads on both tables).

我有一个脚本，它不断向 MERGE 语句中的第一个表添加和更新行.平均而言，被合并的数据一次大约是 20 个字符串，脚本每 5 秒运行一次.

I have a script which keeps on adding and updating rows to the first table in a MERGE statement. On average, the data beeing merged are like 20 strings a time and the scripts runs like ones every 5 seconds.

在第一个表上，我有一个触发器，它在插入或更新时被调用，它获取新插入或更新的数据并在其上调用存储过程，以确保数据在第二个表中建立索引.(这需要一些相当长的时间).

On the first table, i have a trigger which is beeing invoked on a Insert or Update, which takes the newly inserted or updated data and calls a stored procedure on it which makes sure the data is indexed in the second table. (This takes some significant time).

问题是当触发器被禁用时，读取第一个表会在几毫秒内发生.但是，当启用触发器并且您在更新时尝试读取第一个表时运气不好，我们的网络服务器会在 10 秒后给您一个超时时间(无论如何都是很长的时间).

The problem is that when having the trigger disbaled, Reading the first table happens in a few ms. However, when enabling the trigger and your in bad luck of trying to read the first table while this is beeing updated, Our webserver gives you a timeout after 10 seconds (which is way to long anyways).

我可以从这部分质疑，在运行触发器时，第一个表(部分)保持在锁中，直到触发器完成.

I can quess from this part that when running the trigger, the first table is kept (partially) in a lock untill the trigger is completed.

如果我是对的，您认为有什么简单的方法可以解决这个问题吗?

What do you think, if i'm right, is there a easy way around this?

提前致谢！

根据要求:

ALTER TRIGGER [dbo].[OnFeedItemsChanged] 
   ON  [dbo].[FeedItems] 
   AFTER INSERT,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @id int;
    SELECT @id = ID FROM INSERTED;
    IF @id IS NOT NULL
    BEGIN
        DECLARE @title nvarchar(MAX);
        SELECT @title = Title FROM INSERTED;
        DECLARE @description nvarchar(MAX);
        SELECT @description = [Description] FROM INSERTED;

        SELECT @title = dbo.RemoveNonAlphaCharacters(@title)
        SELECT @description = dbo.RemoveNonAlphaCharacters(@description)

        -- Insert statements for trigger here
        EXEC dbo.usp_index_itemstring @id, @title;
        EXEC dbo.usp_index_itemstring @id, @description;
    END
END

FeedItems 表由以下查询填充:

The FeedItems table is populated by this query:

MERGE INTO FeedItems i
USING @newitems d ON i.Service = d.Service AND i.GUID = d.GUID
WHEN matched THEN UPDATE
    SET i.Title = d.Title,
        i.Description = d.Description,
        i.Uri = d.Uri,
        i.Readers = d.Readers
WHEN NOT matched THEN INSERT
    (Service, Title, Uri, GUID, Description, Readers)
    VALUES
    (d.Service, d.Title, d.Uri, d.GUID, d.Description, d.Readers);

sproc:IndexItemStrings 正在填充第二个表，执行这个 proc 确实需要他的时间.问题是在执行这个触发器时.应用于 FeedItems 表的查询大多超时(即使是那些不使用第二个表的查询)

The sproc: IndexItemStrings is populating the second table, executing this proc does indeed take his time. The problem is that while executing this trigger. Queries applied to the FeedItems table are mostly timing out (even those queries who dont uses the second table)

第一张桌子:

USE [ICI]
GO

/****** Object:  Table [dbo].[FeedItems]    Script Date: 04/09/2010 15:03:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FeedItems](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Service] [int] NOT NULL,
    [Title] [nvarchar](max) NULL,
    [Uri] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [GUID] [nvarchar](255) NULL,
    [Inserted] [smalldatetime] NOT NULL,
    [Readers] [int] NOT NULL,
 CONSTRAINT [PK_FeedItems] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FeedItems]  WITH CHECK ADD  CONSTRAINT [FK_FeedItems_FeedServices] FOREIGN KEY([Service])
REFERENCES [dbo].[FeedServices] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FeedItems] CHECK CONSTRAINT [FK_FeedItems_FeedServices]
GO

ALTER TABLE [dbo].[FeedItems] ADD  CONSTRAINT [DF_FeedItems_Inserted]  DEFAULT (getdate()) FOR [Inserted]
GO

第二个表:

USE [ICI]
GO

/****** Object:  Table [dbo].[FeedItemPhrases]    Script Date: 04/09/2010 15:04:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FeedItemPhrases](
    [FeedItem] [int] NOT NULL,
    [Phrase] [int] NOT NULL,
    [Count] [smallint] NOT NULL,
 CONSTRAINT [PK_FeedItemPhrases] PRIMARY KEY CLUSTERED 
(
    [FeedItem] ASC,
    [Phrase] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FeedItemPhrases]  WITH CHECK ADD  CONSTRAINT [FK_FeedItemPhrases_FeedItems] FOREIGN KEY([FeedItem])
REFERENCES [dbo].[FeedItems] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FeedItemPhrases] CHECK CONSTRAINT [FK_FeedItemPhrases_FeedItems]
GO

ALTER TABLE [dbo].[FeedItemPhrases]  WITH CHECK ADD  CONSTRAINT [FK_FeedItemPhrases_Phrases] FOREIGN KEY([Phrase])
REFERENCES [dbo].[Phrases] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FeedItemPhrases] CHECK CONSTRAINT [FK_FeedItemPhrases_Phrases]
GO

还有更多:

ALTER PROCEDURE [dbo].[usp_index_itemstring] 
    -- Add the parameters for the stored procedure here
    @item int, 
    @text nvarchar(MAX) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- DECLARE a table containing all words within the text
    DECLARE @tempPhrases TABLE 
    ( 
        [Index] int,
        [Phrase] NVARCHAR(256) 
    );

    -- extract each word from text and store it in the temp table
    WITH Pieces(pn, start, [stop]) AS 
    ( 
        SELECT 1, 1, CHARINDEX(' ', @text) 
        UNION ALL 
        SELECT pn + 1, CAST([stop] + 1 AS INT), CHARINDEX(' ', @text, [stop] + 1) 
        FROM Pieces 
        WHERE [stop] > 0 
    )   
    INSERT INTO @tempPhrases
    SELECT pn, SUBSTRING(@text, start, CASE WHEN [stop] > 0 THEN [stop]-start ELSE LEN(@text) END) AS s 
    FROM Pieces
    OPTION (MAXRECURSION 0);    

    WITH CombinedPhrases ([Phrase]) AS 
    (
        -- SELECT ALL 2-WORD COMBINATIONS
        SELECT w1.[Phrase] + ' ' + w2.[Phrase] 
        FROM @tempPhrases w1
        JOIN @tempPhrases w2 ON w1.[Index] + 1 = w2.[Index]
        UNION ALL -- SELECT ALL 3-WORD COMBINATIONS
        SELECT w1.[Phrase] + ' ' + w2.[Phrase] + ' ' + w3.[Phrase]
        FROM @tempPhrases w1
        JOIN @tempPhrases w2 ON w1.[Index] + 1 = w2.[Index]
        JOIN @tempPhrases w3 ON w1.[Index] + 2 = w3.[Index]
        UNION ALL  -- SELECT ALL 4-WORD COMBINATIONS
        SELECT w1.[Phrase] + ' ' + w2.[Phrase] + ' ' + w3.[Phrase] + ' ' + w4.[Phrase]
        FROM @tempPhrases w1
        JOIN @tempPhrases w2 ON w1.[Index] + 1 = w2.[Index]
        JOIN @tempPhrases w3 ON w1.[Index] + 2 = w3.[Index]
        JOIN @tempPhrases w4 ON w1.[Index] + 3 = w4.[Index]
    )

    -- ONLY INSERT THE NEW PHRASES IN THE Phrase TABLE      
    INSERT INTO @tempPhrases
    SELECT 0, [Phrase] FROM CombinedPhrases

    -- DELETE PHRASES WHICH ARE EXCLUDED
    DELETE FROM @tempPhrases
    WHERE [Phrase] IN
    (
        SELECT [Text] FROM Phrases p
        JOIN ExcludedPhrases ex
        ON ex.ID = p.ID
    );

    MERGE INTO Phrases p
    USING 
    (
        SELECT DISTINCT Phrase FROM @tempPhrases
    ) t
    ON p.[Text] = t.Phrase
    WHEN NOT MATCHED THEN
        INSERT VALUES (t.Phrase);


    -- Finally create relations between the phrases and feeditem,   
    MERGE INTO FeedItemPhrases p
    USING 
    (
        SELECT @item as [Item], MIN(p.[ID]) as Phrase, COUNT(t.[Phrase]) as [Count]
        FROM Phrases p WITH (NOLOCK)
        JOIN @tempPhrases t ON p.[Text] = t.[Phrase]
        GROUP BY t.[Phrase]
    ) t
    ON p.FeedItem = t.Item
    AND p.Phrase = t.Phrase
    WHEN MATCHED THEN
        UPDATE SET p.[Count] = t.[Count]
    WHEN NOT MATCHED THEN
        INSERT VALUES (t.[Item], t.Phrase, t.[Count]);
END

以及更多:

ALTER Function [dbo].[RemoveNonAlphaCharacters](@Temp NVarChar(max)) 
Returns NVarChar(max) 
AS 
Begin 
    SELECT @Temp = REPLACE (@Temp, '%20', ' ');

    While PatIndex('%[^a-z ]%', @Temp) > 0 
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z ]%', @Temp), 1, '') 
    Return @TEmp 
End 

推荐答案

我在 Internet 上环顾四周，找不到任何方法可以在不要求锁定的情况下触发触发器.因此，我选择通过存储过程进行插入，然后执行之前在触发器中找到的逻辑.这使我能够在插入实际数据并解除插入锁之后在事务中执行触发器的内容.

I looked around on the internet, and I couldn't find any way of making the trigger happen without claiming a lock. Therefore I choose to do the inserts via a stored procedure, which in turn performs the logic previously found in the trigger. This allowed me to execute the content of the trigger in a transaction AFTER the actual data was inserted and the insertion lock was lifted.

希望这有帮助！

这篇关于SQL Server 2008 插入后运行触发器，更新锁定原始表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！