-
Notifications
You must be signed in to change notification settings - Fork 1.7k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Deadlock In multiple server #2374
Comments
I Think the problem is because of this
When the background jobs can not aggregate the |
I have multiple server that they run jobs base on hangfire. but I got this error for running hangfire for running this query in sql server.
I use sql server version 2022 and my hangfire version 1.8.11
this the query that run every time
(@count int)DECLARE @RecordsToAggregate TABLE ( [Key] NVARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL, [Value] INT NOT NULL, [ExpireAt] DATETIME NULL ) SET XACT_ABORT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET DEADLOCK_PRIORITY LOW BEGIN TRAN DELETE TOP (@count) C OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate FROM [HangFire].[Counter] C WITH (READPAST, XLOCK, INDEX(0)) SET NOCOUNT ON ;MERGE [HangFire].[AggregatedCounter] WITH (FORCESEEK, HOLDLOCK) AS [Target] USING ( SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt]) ON [Target].[Key] COLLATE DATABASE_DEFAULT = [Source].[Key] COLLATE DATABASE_DEFAULT WHEN MATCHED THEN UPDATE SET [Target].[Value] = [Target].[Value] + [Source].[Value], [Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt])) WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]); COMMIT TRAN
and this is the error that I got
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
The text was updated successfully, but these errors were encountered: