Forums (Obsolete)

Certified Developer v7

slycknick - 10/20/2009 3:43:52 AM

Error In Task Scheduler

I have a scheduled task that updates and inserts products to an eCommerce site. While the task appears to work ok it is generating a lot of errors like the one below.
Can anyone shed any light as to why I'm getting these messages and how to avaoid them.

Thanks,
Nick

[DataConnection.ExecuteQuery]: Query: DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT TOP 1000 [EventID] FROM [CMS_EventLog] WHERE SiteID IS NULL ORDER BY [EventID] DESC)) AND (SiteID IS NULL): caused exception: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at CMS.DataProviderSQL.DataConnection.ExecuteQuery(String queryText, Object[,] queryParams, QueryTypeEnum queryType)

Kentico Support

kentico_zdenekc - 10/20/2009 10:45:13 AM

RE:Error In Task Scheduler

Hi Nick,

could you please specify if you log something (using our event logging API) in the scheduled code? Would it be even possible posting the code here (e.g. some commented stub)?

Also I'd mention that for any DB operations and queries you use, transactioned approach should be used.
Some advices can be found also at sqlauthority.com blog post

Regards,
Zdenek C.

Certified Developer v7

slycknick - 10/20/2009 11:13:16 AM

RE:Error In Task Scheduler

Below is a summary of what I'm doing. A very simple product update.

I'm not using your event logging API at the moment which is why I don't understand the error. Does the API write to the CMS_EventLog in the background. Can I prevent this?

Nick

Dim newSKU As CMS.Ecommerce.SKUInfo
newSKU = CMS.Ecommerce.SKUInfoProvider.GetSKUInfoByGUID(guidSKU)

If newSKU Is Nothing Then

' Insert new product

newSKU = New CMS.Ecommerce.SKUInfo
With newSKU
.SKUGUID = guidSKU
.SKUNumber = strSKUNumber ' product id
' Set other defaults ....

End With

' Add SKU
CMS.Ecommerce.SKUInfoProvider.SetSKUInfo(newSKU)

Else

' Update sku
With newSKU
.SKULastModified = Now
End With

newSKU.Update()
CMS.Ecommerce.SKUInfoProvider.SetSKUInfo(newSKU)

End If

Certified Developer v7

slycknick - 10/23/2009 5:22:50 AM

RE:Error In Task Scheduler

An update for anyone who has the same problem.

It appears that the API writes to the CMS event log in the background and if you have two tasks running at the same time they can conflict and cause this error.

If you set ;

CMS.EventLog.EventLogProvider.LogFieldChanges = False
or set LogEvents = False to the individual objects

It doesn't write to the log and all is ok. There is also a setting to switch off log writes for metadata changes also.

Nick

Kentico Support

kentico_zdenekc - 10/28/2009 11:33:50 AM

RE:Error In Task Scheduler

Hi Nick,

yes, the API methods write to the EventLog,
however the DELETE query is called on CMS_EventLog table when the older logs are deleted. It seems that the scheduled task, when running as a separate thread (in background) adds the log records and this triggers the deleting of the older logs and such deadlocks happen. It should be prevented by transactioned processing, but apparently it is not somewhere...

We will check if this can be prevented in the better way than disabling logging of events, but anyway, for now it's a kind of suitable workaround.

Regards,
Zdenek C.