SQL Server 2008 Running trigger after Insert, Update locks original table(SQL Server 2008 插入后运行触发器,更新锁定原始表)
问题描述
我有一个严重的性能问题.
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 插入后运行触发器,更新锁定原始表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:SQL Server 2008 插入后运行触发器,更新锁定原始表
基础教程推荐
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01