Today we found Database lock wait for this queries.
How to solve this critical issue?
Current version is Kentico 12.0.98 (Portal Engine)
Query1:
DELETE FROM CMS_ScheduledTask WHERE (([TaskObjectType] = @TaskObjectType AND [TaskObjectID] IN (
SELECT [StateID]
FROM CMS_AutomationState
WHERE ([StateObjectType] = @StateObjectType AND [StateObjectID] = 192875297)
)) AND [TaskObjectID] IS NOT NULL)
Query2:
WITH q AS
(
SELECT TOP(@BatchSize) [TaskID], [TaskName], [TaskDisplayName], [TaskAssemblyName], [TaskClass], [TaskInterval], [TaskData], [TaskLastRunTime],
[TaskNextRunTime], [TaskEnabled], [TaskIsRunning], [TaskSiteID], [TaskDeleteAfterLastRun], [TaskServerName], [TaskGUID], [TaskExecutions],
[TaskResourceID], [TaskRunInSeparateThread], [TaskUseExternalService], [TaskAllowExternalService], [TaskLastExecutionReset], [TaskCondition],
[TaskRunIndividually], [TaskUserID], [TaskType], [TaskObjectType], [TaskObjectID], [TaskExecutingServerName]
FROM CMS_ScheduledTask
WHERE TaskID > @LastProcessedId AND TaskNextRunTime IS NOT NULL AND TaskNextRunTime <= @DateTime AND TaskEnabled = 1 AND TaskIsRunning = 0
AND (TaskSiteID = @TaskSiteID OR TaskSiteID IS NULL)
AND (TaskServerName IS NULL OR TaskServerName = '' OR TaskServerName = @TaskServerName)
ORDER BY TaskID
)
UPDATE q SET TaskIsRunning = 1, TaskExecutingServerName = @TaskServerName
OUTPUT INSERTED.[TaskID], INSERTED.[TaskName], INSERTED.[TaskDisplayName], INSERTED.[TaskAssemblyName], INSERTED.[TaskClass], INSERTED.[TaskInterval], INSERTED.[TaskData], INSERTED.[TaskLastRunTime],
INSERTED.[TaskNextRunTime], INSERTED.[TaskEnabled], INSERTED.[TaskIsRunning], INSERTED.[TaskSiteID], INSERTED.[TaskDeleteAfterLastRun], INSERTED.[TaskServerName], INSERTED.[TaskGUID], INSERTED.[TaskExecutions],
INSERTED.[TaskResourceID], INSERTED.[TaskRunInSeparateThread], INSERTED.[TaskUseExternalService], INSERTED.[TaskAllowExternalService], INSERTED.[TaskLastExecutionReset], INSERTED.[TaskCondition],
INSERTED.[TaskRunIndividually], INSERTED.[TaskUserID], INSERTED.[TaskType], INSERTED.[TaskObjectType], INSERTED.[TaskObjectID], INSERTED.[TaskExecutingServerName]
Query3:
SELECT TOP 1 *
FROM CMS_ScheduledTask
WHERE ([TaskName] = @TaskName AND [TaskSiteID] = @TaskSiteID)