You receive a deadlock in Commerce Server 2002 in the catalogproducts_upd_tr trigger (828524)

The information in this article applies to:

Microsoft Commerce Server 2002
Microsoft Commerce Server 2002 SP1
Microsoft Commerce Server 2002 SP2
Microsoft Commerce Server 2002, Service Pack 3 (SP3)

SYMPTOMS

When you update or delete items in the catalog programmatically in Microsoft Commerce Server 2002, you may notice that your application does not respond. Additional investigation indicates that a deadlock occurred on the Microsoft SQL Server. In a deadlock, various threads cannot continue because they are waiting on a set of resources that are held by each other or that are held by other threads.

CAUSE

Catalog tables have a trigger that is named catalogproducts_upd_tr. This trigger has a section that updates the global catalog information with a date that the catalog was last updated. This behavior occurs to support the DeltaExportXML method. With the DeltaExportXML method, you can export just the updated sections of the catalog.

RESOLUTION

To resolve this behavior, edit the name_catalogproducts_upd_tr catalog trigger, where name is the name of your catalog. Then, remove the sections that are marked with braces ("{ }"):

CREATE TRIGGER [name_catalogproducts_upd_tr] ON [name_catalogproducts] FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [Ad_catalogproducts]
SET LastModified=getdate()
from [Ad_catalogproducts] P, INSERTED I
where P.oid = I.oid 
{UPDATE Catalogglobal}
{SET ProductTableUpdated = getdate()}
{Where catalogname = N'name' }
END

STATUS

This behavior is by design.

Modification Type:	Major	Last Reviewed:	7/15/2004
Keywords:	kbprb KB828524 kbAudDeveloper