none
Deadlock problem with Commerce Server 2007 Catalog Adapter RRS feed

  • Question

  •  

    Hi All,

     

    I have a deadlock problem with Catalog Adapter for Commerce Server.

    The deadlock appears when following actions execute concurrently:

    1. Product data updates come from BizTalk Catalog Adapter

    2. An user is browsing the product catalog through commerce web site.

     

    Deadlock is received by the user.

     

    Here follows  the SQL Server log for this deadlock:

     

    2008-09-01 12:22:05.01 spid22s     deadlock-list
    2008-09-01 12:22:05.01 spid22s      deadlock victim=process6e8898
    2008-09-01 12:22:05.01 spid22s       process-list
    2008-09-01 12:22:05.01 spid22s        process id=process6e8898 taskpriority=0 logused=0 waitresource=OBJECT: 17:1502081283:0  waittime=3953 ownerId=189431846 transactionname=SELECT lasttranstarted=2008-09-01T12:22:01.060 XDES=0xe0da3c0 lockMode=IS schedulerid=1 kpid=4656 status=suspended spid=119 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2008-09-01T12:22:01.043 lastbatchcompleted=2008-09-01T12:22:01.043 clientapp=.Net SqlClient Data Provider hostname=COMMDEV hostpid=6684 loginname=COMMDEV\RunTimeUser isolationlevel=read committed (2) xactid=189431846 currentdb=17 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
    2008-09-01 12:22:05.01 spid22s         executionStack
    2008-09-01 12:22:05.01 spid22s          frame procname=adhoc line=1 stmtstart=66 sqlhandle=0x0200000068117505d840e73e7146fc9068610e93f5925bda
    2008-09-01 12:22:05.01 spid22s     Select A.*,Cast(0 As bit) As fVirtualCatalog FROM dbo.[BaseCatalog_el-GR] A, dbo.[BaseCatalog_CatalogHierarchy] B
    2008-09-01 12:22:05.01 spid22s       WHERE B.child_oid = @Oid AND B.oid = A.oid AND A.i_classtype = 1    
    2008-09-01 12:22:05.01 spid22s          frame procname=mssqlsystemresource.sys.sp_executesql line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000
    2008-09-01 12:22:05.01 spid22s     sp_executesql    
    2008-09-01 12:22:05.01 spid22s          frame procname=B2CSite_productcatalog.dbo.ctlg_GetParentCategories line=36 stmtstart=2582 stmtend=2792 sqlhandle=0x03001100cff1590fda40ea00b09a00000100000000000000
    2008-09-01 12:22:05.01 spid22s     EXEC sp_executesql @Query_tmp, N'@BCName nvarchar(85), @Oid int ',
    2008-09-01 12:22:05.01 spid22s        @BCName = @BCName, @Oid = @Oid    
    2008-09-01 12:22:05.01 spid22s         inputbuf
    2008-09-01 12:22:05.01 spid22s     Proc [Database Id = 17 Object Id = 257552847]   
    2008-09-01 12:22:05.01 spid22s        process id=process8dc5c8 taskpriority=0 logused=228 waitresource=OBJECT: 17:2014083107:0  waittime=3953 ownerId=189431842 transactionname=DROP INDEX lasttranstarted=2008-09-01T12:22:01.060 XDES=0x1d8599b0 lockMode=Sch-M schedulerid=2 kpid=7596 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-09-01T12:22:01.043 lastbatchcompleted=2008-09-01T12:22:01.043 clientapp=.Net SqlClient Data Provider hostname=COMMDEV hostpid=5800 loginname=COMMDEV\CatalogWebSvc isolationlevel=read committed (2) xactid=189431842 currentdb=17 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
    2008-09-01 12:22:05.01 spid22s         executionStack
    2008-09-01 12:22:05.01 spid22s          frame procname=adhoc line=1 sqlhandle=0x0100110037eaf13af8b23718000000000000000000000000
    2008-09-01 12:22:05.01 spid22s     DROP INDEX dbo.[BaseCatalog_CatalogProducts].Index_ParentOid_ClassType_VariantId    
    2008-09-01 12:22:05.01 spid22s          frame procname=mssqlsystemresource.sys.sp_executesql line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000
    2008-09-01 12:22:05.01 spid22s     sp_executesql    
    2008-09-01 12:22:05.01 spid22s          frame procname=B2CSite_productcatalog.dbo.ctlg_CreateDropTempIndexes line=23 stmtstart=1386 stmtend=1454 sqlhandle=0x03001100b1f6ff01d140ea00b09a00000100000000000000
    2008-09-01 12:22:05.01 spid22s     EXEC sp_executesql @Query_tmp    
    2008-09-01 12:22:05.01 spid22s         inputbuf
    2008-09-01 12:22:05.01 spid22s     Proc [Database Id = 17 Object Id = 33552049]   
    2008-09-01 12:22:05.01 spid22s       resource-list
    2008-09-01 12:22:05.01 spid22s        objectlock lockPartition=0 objid=2014083107 subresource=FULL dbid=17 objectname=B2CSite_productcatalog.dbo.BaseCatalog_el-GR_Catalog id=lock143e36c0 mode=IS associatedObjectId=2014083107
    2008-09-01 12:22:05.01 spid22s         owner-list
    2008-09-01 12:22:05.01 spid22s          owner id=process6e8898 mode=IS
    2008-09-01 12:22:05.01 spid22s         waiter-list
    2008-09-01 12:22:05.01 spid22s          waiter id=process8dc5c8 mode=Sch-M requestType=wait
    2008-09-01 12:22:05.01 spid22s        objectlock lockPartition=0 objid=1502081283 subresource=FULL dbid=17 objectname=B2CSite_productcatalog.dbo.BaseCatalog_CatalogProducts id=lock14503d40 mode=Sch-M associatedObjectId=1502081283
    2008-09-01 12:22:05.01 spid22s         owner-list
    2008-09-01 12:22:05.01 spid22s          owner id=process8dc5c8 mode=Sch-M
    2008-09-01 12:22:05.01 spid22s         waiter-list
    2008-09-01 12:22:05.01 spid22s          waiter id=process6e8898 mode=IS requestType=wait

     

    What I have tried is to modify ctlg_CreateDropTempIndexes stored procedure with SET DEADLOCK_PRIORITY_LOW and this making the BizTalk side the deadlock victim instead of the Web site. Also I made BizTalk orchestrations retry the update if a deadlock error is received.

     

    I'm not sure if it is the best solution - is there a way to prevent this deadlock?

    Also it seems strange for me that indexes are recreated each time a product update is done through Catalog Adapter - we need periodically to update over 10000 products and each time ctlg_CreateDropTempIndexes is called.

     

    I need help to resolve this issue

     

    Regards,

    Raicho

    Wednesday, September 3, 2008 7:38 AM

All replies

  •  

    Basically my question is why this stored procedure:

    ctlg_CreateDropTempIndexes (called by catalog adapter) 

    makes deadlocks with select statements on the catalog table (executed when the user browses the website) 

    and how to avoid these deadlocks?

    And I also do not understand why the Catalog Adapter drops indexes every time a product is inserted into Commerce Server through it?

    Thursday, September 4, 2008 7:37 AM