Ax2009, SQL2005 Lock Occurred
DBA
Now I'm using Dynamics Ax and SQL Server 2005.
Lock was occurred when I was doing some batch jobs.
Here's a query which lock began.. and locked tables.
I think some table relation check could be a reason, but I don't know how to
solve it.
- lock occurred query
SELECT TOP 1
A.ITEMGROUPID,A.ITEMID,A.ITEMNAME,A.ITEMTYPE,A.PURCHMODEL,A.HEIGHT,A.WIDTH,A.SALESMODEL,A.COSTGROUPID,A.REQGROUPID,A.EPCMANAGER,A.PRIMARYVENDORID,A.NETWEIGHT,A.DEPTH,A.UNITVOLUME,A.BOMUNITID,A.ITEMPRICETOLERANCEGROUPID,A.DENSITY,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.DIMENSION4_,A.DIMENSION5_,A.DIMENSION6_,A.COSTMODEL,A.USEALTITEMID,A.ALTITEMID,A.INTRACODE,A.PRODFLUSHINGPRINCIP,A.MINIMUMPALLETQUANTITY,A.PBAITEMAUTOGENERATED,A.WMSARRIVALHANDLINGTIME,A.BOMMANUALRECEIPT,A.STOPEXPLODE,A.PHANTOM,A.INTRAUNIT,A.BOMLEVEL,A.BATCHNUMGROUPID,A.AUTOREPORTFINISHED,A.ORIGCOUNTRYREGIONID,A.STATISTICSFACTOR,A.ALTCONFIGID,A.STANDARDCONFIGID,A.PRODPOOLID,A.ABCTIEUP,A.ABCREVENUE,A.ABCVALUE,A.ABCCONTRIBUTIONMARGIN,A.COMMISSIONGROUPID,A.CONFIGURABLE,A.SALESPERCENTMARKUP,A.SALESCONTRIBUTIONRATIO,A.SALESPRICEMODELBASIC,A.DEL_MINAVERAGESETTLE,A.NAMEALIAS,A.PRODGROUPID,A.PROJCATEGORYID,A.GROSSDEPTH,A.GROSSWIDTH,A.GROSSHEIGHT,A.STANDARDPALLETQUANTITY,A.QTYPERLAYER,A.SORTCODE,A.CONFIGSIMILAR,A.SERIALNUMGROUPID,A.DIMGROUPID,A.MODELGROUPID,A.ITEMBUYERGROUPID,A.TAXPACKAGINGQTY,A.WMSPALLETTYPEID,A.ORIGSTATEID,A.DEL_STOPEXPLODEPRICE,A.WMSPICKINGQTYTIME,A.TARAWEIGHT,A.PACKAGINGGROUPID,A.SCRAPVAR,A.SCRAPCONST,A.STANDARDINVENTCOLORID,A.STANDARDINVENTSIZEID,A.ITEMDIMCOMBINATIONAUTOCREATE,A.ITEMDIMCOSTPRICE,A.ALTINVENTSIZEID,A.ALTINVENTCOLORID,A.PALLETTAGGING,A.ITEMTAGGINGLEVEL,A.BOMCALCGROUPID,A.ITEMSPEC,A.ITEMSTATUS,A.CONVERTUNIT,A.TKR_STANDARDCODE,A.TKR_SAMPLEYN,A.TKR_MAKERCODE,A.TKR_ITEMORDERNO,A.TKR_ITEMPACKAGE,A.ABCCATEGORY,A.LARGECATEGORY,A.MEDIUMCATEGORY,A.SMALLCATEGORY,A.DETAILCATEGORY1,A.DETAILCATEGORY2,A.DELIVERYTARGET,A.TKR_GLMITEMNO,A.TKR_GLMITEMNAME,A.TKR_B2B,A.TKR_ITEMATTRIBUTE,A.TKR_ORDERITEM,A.TKR_SCREENOPTION1,A.TKR_MATERIALQUALITY,A.TKR_LATERPROCESS,A.CATEGORY3,A.CATEGORY2,A.CATEGORY1,A.TKR_WARANTY,A.TKR_VDDIRECT,A.TKR_ITEMDETAILCODE,A.TKR_ITEMMIDDLECODE,A.TKR_ITEMLARGECODE,A.TKR_ITEMSMALLCODE,A.PLANTCODE,A.TKR_PICKQTYCALC,A.TKR_ITEMCLASSFY1,A.TKR_ITEMCLASSFY2,A.TKR_ITEMCLASSFY3,A.TKR_ITEMCLASSFYSEQ,A.TKR_LOCKKEYYN,A.REPLACEMENTQTY,A.DETAILCATEGORY21,A.TKR_LEDGERACCOUNT,A.KEEPINVENTORY,A.TKR_ITEMATTRIBUTEQUALITY,A.TKR_HSCODE,A.TKR_ITEMDIVISION,A.TKR_ITEMMAKER,A.TKR_LEFTYN,A.TKR_PRICETAGYN,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.CREATEDDATETIME,A.CREATEDBY,A.RECVERSION,A.RECID
FROM INVENTTABLE A WHERE ((DATAAREAID=@P1) AND (ITEMID=@P2))
- locked tables
98 has 60 locks on object 191756186 (INVENTSUMDELTA)
98 has 111 locks on object 506588993 (SALESPARMLINE)
98 has 136 locks on object 521638243 (WMSORDER)
98 has 17 locks on object 649977592 (WMSPICKINGROUTE)
98 has 49 locks on object 1375109181 (PRODTABLE)
98 has 191 locks on object 1406732164 (PRODBOM)
98 has 17 locks on object 1530696751 (SALESJOURNALAUTOSUMMARY)
98 has 13 locks on object 1609981012 (WMSSHIPMENT)
98 has 48 locks on object 1610957311 (SMMTRANSLOG)
98 has 13 locks on object 1689773077 (SALESPARMSUBTABLE)
98 has 18 locks on object 1838069734 (SALESPARMTABLE)
98 has 22 locks on object 1851400098 (SALESTABLE)
98 has 186 locks on object 1875029961 (WMSORDERTRANS)
98 has 17 locks on object 1890157829 (CUSTTABLE)
98 has 196 locks on object 2000595507 (SALESLINE)
98 has 207 locks on object 2065494487 (INVENTTRANS)
Michal
> Now I'm using Dynamics Ax and SQL Server 2005.
> Lock was occurred when I was doing some batch jobs.
> Here's a query which lock began.. and locked tables.
> I think some table relation check could be a reason, but I don't know how
> to
> solve it.
Locks are normal way for the database to maintain data consistency. Locks
are set and released, them come and go.
I understand that you have a problem with PROLONGED blocks, taking an hour
or two.
> - lock occurred query
> SELECT TOP 1
> A.ITEMGROUPID,A.ITEMID,A.ITEMNAME,A.ITEMTYPE,A.PURCHMODEL,A.HEIGHT,A.WIDTH,A.SALESMODEL,A.COSTGROUPID,A.REQGROUPID,A.EPCMANAGER,A.PRIMARYVENDORID,A.NETWEIGHT,A.DEPTH,A.UNITVOLUME,A.BOMUNITID,A.ITEMPRICETOLERANCEGROUPID,A.DENSITY,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.DIMENSION4_,A.DIMENSION5_,A.DIMENSION6_,A.COSTMODEL,A.USEALTITEMID,A.ALTITEMID,A.INTRACODE,A.PRODFLUSHINGPRINCIP,A.MINIMUMPALLETQUANTITY,A.PBAITEMAUTOGENERATED,A.WMSARRIVALHANDLINGTIME,A.BOMMANUALRECEIPT,A.STOPEXPLODE,A.PHANTOM,A.INTRAUNIT,A.BOMLEVEL,A.BATCHNUMGROUPID,A.AUTOREPORTFINISHED,A.ORIGCOUNTRYREGIONID,A.STATISTICSFACTOR,A.ALTCONFIGID,A.STANDARDCONFIGID,A.PRODPOOLID,A.ABCTIEUP,A.ABCREVENUE,A.ABCVALUE,A.ABCCONTRIBUTIONMARGIN,A.COMMISSIONGROUPID,A.CONFIGURABLE,A.SALESPERCENTMARKUP,A.SALESCONTRIBUTIONRATIO,A.SALESPRICEMODELBASIC,A.DEL_MINAVERAGESETTLE,A.NAMEALIAS,A.PRODGROUPID,A.PROJCATEGORYID,A.GROSSDEPTH,A.GROSSWIDTH,A.GROSSHEIGHT,A.STANDARDPALLETQUANTITY,A.QTYPERLAYER,A.SORTCODE,A.CONFIGSIMILAR,A.SERIALNUMGROUPID,A.DIMGROUPID,A.MODELGROUPID,A.ITEMBUYERGROUPID,A.TAXPACKAGINGQTY,A.WMSPALLETTYPEID,A.ORIGSTATEID,A.DEL_STOPEXPLODEPRICE,A.WMSPICKINGQTYTIME,A.TARAWEIGHT,A.PACKAGINGGROUPID,A.SCRAPVAR,A.SCRAPCONST,A.STANDARDINVENTCOLORID,A.STANDARDINVENTSIZEID,A.ITEMDIMCOMBINATIONAUTOCREATE,A.ITEMDIMCOSTPRICE,
A.ALTINVENTSIZEID,
A.ALTINVENTCOLORID,A.PALLETTAGGING,A.ITEMTAGGINGLEVEL,A.BOMCALCGROUPID,A.ITEMSPEC,A.ITEMSTATUS,A.CONVERTUNIT,
//custom fields.
A.TKR_STANDARDCODE,A.TKR_SAMPLEYN,A.TKR_MAKERCODE,A.TKR_ITEMORDERNO,A.TKR_ITEMPACKAGE,A.ABCCATEGORY,A.LARGECATEGORY,A.MEDIUMCATEGORY,A.SMALLCATEGORY,A.DETAILCATEGORY1,A.DETAILCATEGORY2,A.DELIVERYTARGET,A.TKR_GLMITEMNO,A.TKR_GLMITEMNAME,A.TKR_B2B,A.TKR_ITEMATTRIBUTE,A.TKR_ORDERITEM,A.TKR_SCREENOPTION1,A.TKR_MATERIALQUALITY,A.TKR_LATERPROCESS,A.CATEGORY3,A.CATEGORY2,A.CATEGORY1,A.TKR_WARANTY,A.TKR_VDDIRECT,A.TKR_ITEMDETAILCODE,A.TKR_ITEMMIDDLECODE,A.TKR_ITEMLARGECODE,A.TKR_ITEMSMALLCODE,A.PLANTCODE,A.TKR_PICKQTYCALC,A.TKR_ITEMCLASSFY1,A.TKR_ITEMCLASSFY2,A.TKR_ITEMCLASSFY3,A.TKR_ITEMCLASSFYSEQ,A.TKR_LOCKKEYYN,A.REPLACEMENTQTY,A.DETAILCATEGORY21,A.TKR_LEDGERACCOUNT,A.KEEPINVENTORY,A.TKR_ITEMATTRIBUTEQUALITY,A.TKR_HSCODE,A.TKR_ITEMDIVISION,A.TKR_ITEMMAKER,A.TKR_LEFTYN,A.TKR_PRICETAGYN,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.CREATEDDATETIME,A.CREATEDBY,A.RECVERSION,A.RECID
> FROM INVENTTABLE A WHERE ((DATAAREAID=@P1) AND (ITEMID=@P2))
>
It is hard to find the exact statement which causes the block, unless you
know the code very well. The special AX add-ons exist to narrow down the
search.
AX uses readcommited transaction isolation, which means if inside
transaction one performs a read of the data MODIFIED but not commited in
other transaction, even it's only read it will be blocked. That your
situation- the query does not have UPDLOCK clause, it's ordinary read, and
it get's blocked anyway.
> - locked tables
> 98 has 60 locks on object 191756186 (INVENTSUMDELTA)
> 98 has 111 locks on object 506588993 (SALESPARMLINE)
> 98 has 136 locks on object 521638243 (WMSORDER)
> 98 has 17 locks on object 649977592 (WMSPICKINGROUTE)
> 98 has 49 locks on object 1375109181 (PRODTABLE)
> 98 has 191 locks on object 1406732164 (PRODBOM)
> 98 has 17 locks on object 1530696751 (SALESJOURNALAUTOSUMMARY)
> 98 has 13 locks on object 1609981012 (WMSSHIPMENT)
> 98 has 48 locks on object 1610957311 (SMMTRANSLOG)
> 98 has 13 locks on object 1689773077 (SALESPARMSUBTABLE)
> 98 has 18 locks on object 1838069734 (SALESPARMTABLE)
> 98 has 22 locks on object 1851400098 (SALESTABLE)
> 98 has 186 locks on object 1875029961 (WMSORDERTRANS)
> 98 has 17 locks on object 1890157829 (CUSTTABLE)
> 98 has 196 locks on object 2000595507 (SALESLINE)
> 98 has 207 locks on object 2065494487 (INVENTTRANS)
I affraid you are executing custom code- I can't remember operation in
standard AX which would involve these tables at once. You are not showing
the types of the locks.
Regards
--
Michal
AXImprove.co.uk | Solutions for AX performance and data storage.
DBA
Yes, It is customized code and there is no standard code that access all of
the tables..
I should investigate more details. thank you
"Michal" wrote:
> .
>