sql table lock while trying to do tw update_recordset on the same table

(2) Share

Report

Posted on by dark_knight

1,230

Hello,

I have a behavior I cannot to debug. Maybe you could point me out to the right direction please

I exten the standard class SalesPackingSlipDP for the stadard packing slip report.

In the method SalesPackingSlipDP.createSalesPackingSlip() it's passed userconnection to SalesPackingSlipDP.parmuserconnection()

protected SalesPackingSlipHeaderTmp createSalesPackingSlip()
{
    if (!this.parmUserConnection() && SalesPackingSlipDPDBInteractionInUsrConnectionFlight::instance().isEnabled())
    {
        #OCCRetryCount
        SalesPackingSlipHeaderTmp salesPackingSlipHeaderTmp;
    
        try
        {
            uc = new UserConnection();
            uc.ttsbegin();
            this.parmUserConnection(uc);
            salesPackingSlipHeaderTmp = this.createSalesPackingSlipHeader();
            this.createSalesPackingSlipLines(salesPackingSlipHeaderTmp);
            uc.ttscommit();
        }

then they set transaction connection on the report tables like that, for instance in the method SalesPackingSlipDP.initializeSalesPackingSlipLine()

I do exctly the same for my custom report table

 this.setTransactionConnection(myCustomTable);

then I fill up myCustomTable with records. Then I do update_recordset on the myCustomTable and it works fine, then I do another one update_recordset and it's suspended and AX freezes.

In the SQL activity monitor I see this

blockedby field points to this process

which doesn't give me a clue.

Is there any way to troubleshoot why the table is blocked during second update_recordset?

Thanks.

Categories:

Microsoft Dynamics AX

Dynamics 365 Finance

I have the same question (0)

All responses (14)

Answers (1)

Sort by

Martin Dráb 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Please give us more information.

First of all, let me try to give the context on your behalf. Please confirm that my understanding is correct, or explain where it's not.

It seems you added an additional table to SalesPackingSlipDP and because the DP class uses pre-processing with regular tables, you're trying to use the same pre-processing infrastructure for your table as well, so you've (hopefully) designed your table accordingly and you're reusing the custom connection for working with your table inside the DP.

Then you're doing two update_recordset against your table and the second one runs into an existing lock.

It sounds like a bug in your code only. Can't you reproduce the problem in a mere runnable class, so we don't have to talk about SalesPackingSlipDP and such things at all? You'll get a test case that is much easier to execute and understand, and much faster too.

Show us your code, including how you're dealing with transactions. Are both updates using the same connection?

It seems that your second update_recordset can't obtain an update lock of a resource because it's still locked by the first update. You seem to believe that the update lock should be released, but it's time to test it. I think you'll see that it isn't the case.

Of course, there is a quite a lot that you can do inside the DB, such as finding which resource is locked or the query obtaining it. For example, check out Finding blocking/locking queries in MS SQL (mssql).

Was this reply helpful? Yes No
dark_knight 1,230 on at

Like (0)

Report
Copy link

Link copied!
Hello Martin,

1) It seems you added an additional table to SalesPackingSlipDP and because the DP class uses pre-processing with regular tables, you're trying to use the same pre-processing infrastructure for your table as well, so you've (hopefully) designed your table accordingly and you're reusing the custom connection for working with your table inside the DP.

I put the same properties (colored in yellow on the screenshit below) to my custom table as on the standard report table except "disable lock escalation", such property isn't presented on my custom table.

2) Then you're doing two update_recordset against your table and the second one runs into an existing lock. - Yes, I'm doing two update_recordset against my table and the second one has got status suspended in the SQL management studio activity monitor and this second update never exnd causing AX to freeze.

3) It sounds like a bug in your code only. Can't you reproduce the problem in a mere runnable class, so we don't have to talk about SalesPackingSlipDP and such things at all? You'll get a test case that is much easier to execute and understand, and much faster too.

I tried to reproduce and wrote such code which repeats the call structure of my real code and no problem in that code. Both updates went well.

SalesPackingSlipDetailsWBundlesTmp salesPackingSlipDetailsWBundlesTmp GSalesPackingSlipDetailsWBundlesTmp salesPackingSlipDetailsWBundlesTmp1; UserConnection uc = new UserConnection(); uc.ttsbegin(); salesPackingSlipDetailsWBundlesTmp.setConnection(uc); salesPackingSlipDetailsWBundlesTmp.clear(); salesPackingSlipDetailsWBundlesTmp.SalesId = "1"; salesPackingSlipDetailsWBundlesTmp.BundleParent = "12"; salesPackingSlipDetailsWBundlesTmp.SalesPackingSlipTmpTableType = SalesPackingSlipTmpTableType::Current; salesPackingSlipDetailsWBundlesTmp.insert(); update_recordset salesPackingSlipDetailsWBundlesTmp setting BundleParent = "14" where salesPackingSlipDetailsWBundlesTmp.SalesPackingSlipTmpTableType == SalesPackingSlipTmpTableType::Current; salesPackingSlipDetailsWBundlesTmp1.setConnection(uc); salesPackingSlipDetailsWBundlesTmp1.clear(); salesPackingSlipDetailsWBundlesTmp1.SalesId = "2"; salesPackingSlipDetailsWBundlesTmp1.BundleParent = "13"; salesPackingSlipDetailsWBundlesTmp1.SalesPackingSlipTmpTableType = SalesPackingSlipTmpTableType::History; salesPackingSlipDetailsWBundlesTmp1.insert(); update_recordset salesPackingSlipDetailsWBundlesTmp1 setting BundleParent = "15" where salesPackingSlipDetailsWBundlesTmp1.SalesPackingSlipTmpTableType == SalesPackingSlipTmpTableType::History; uc.ttscommit();

4) what happens in real code

In the extension of the standard class SalesPackingSlipDP inside standard method createSalesPackingSlipLines I call two methods to fill up my tables.

protected void createSalesPackingSlipLines(SalesPackingSlipHeaderTmp _salesPackingSlipHeaderTmp) { next createSalesPackingSlipLines(_salesPackingSlipHeaderTmp); this.CreateSalesPackingSlipWithBundlesLines(); if (this.parmCustFormletterParameters().ItemsPastDeliveredOnPackingSlip == NoYes::Yes) { this.CreateSalesPastPackingSlipLinesWithBundlesLines(); } }

method CreateSalesPackingSlipWithBundlesLines() structure

SalesPackingSlipDetailsWBundlesTmp salesPackingSlipDetailsWBundlesTmp;//my custom table this.setTransactionConnection(salesPackingSlipDetailsWBundlesTmp); .... inserting some records to salesPackingSlipDetailsWBundlesTmp first update_recordset against salesPackingSlipDetailsWBundlesTmp

method CreateSalesPastPackingSlipWithBundlesLine() structure

SalesPackingSlipDetailsWBundlesTmp salesPackingSlipDetailsWBundlesTmp; this.setTransactionConnection(salesPackingSlipDetailsWBundlesTmp); .... inserting some records to salesPackingSlipDetailsWBundlesTmp second update_recordset against salesPackingSlipDetailsWBundlesTmp

on the second update_recordset I get the problem. I can confirm that both updates use the same connection because I use settransactionconnection method on both table buffers.

Also after first update I used this query to see the locks and nothing was found. Therefore the first update didn't lock the table as far as I get it.

SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = 'AxDB' AND blocking_session_id <> 0

after the second update the query above returned me one record, i.e. my lock.

Was this reply helpful? Yes No
Martin Dráb 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Your SQL code doesn't show locks; it returns blocked queries. When the first query obtains a lock, it's not blocked nor it's blocking anything. Only when the second query runs and tries to obtain an update lock, it's blocked by an existing lock. Of course that you won't see the second query blocked until you run it.

Was this reply helpful? Yes No
dark_knight 1,230 on at

Like (0)

Report
Copy link

Link copied!

Should I share the full code in my extended method? what can be the root cause of such behavior?

Was this reply helpful? Yes No
Martin Dráb 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I suggest you first verify which resource is locked and by what query. I'm guessing it's the whole table by your first update_recordset, but knowing is much better then assuming. You may have some other code in play that you aren't even aware of.

Also, in your test code, try updating all records in both cases, not different records.

Was this reply helpful? Yes No
dark_knight 1,230 on at

Like (0)

Report
Copy link

Link copied!
OK. Thanks. I run this SQL script to find the locked object and query causing the issue. I did after the first update is done and before the second update is done.

SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id

here are the results. I see multiple locks on my custom table SALESPACKINGSLIPDETAILSWBUNDLESTMP and also the standard report table SALESPACKINGSLIPDETAILSTMP. Before the first update I copy all the records from the standard table SALESPACKINGSLIPDETAILSTMP to my custom table SALESPACKINGSLIPDETAILSWBUNDLESTMP using insert_recordset AX function

SQLStatementText is fetchapi. It's locking my custom table as well as the standard table SALESPACKINGSLIPDETAILSTMP

then I run the query to find the original select query that is driving the cursor

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text FROM sys.dm_exec_cursors(75) c CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) t;

and i got such results

text is

(@1 numeric(10,0),@2 nvarchar(4000))SELECT [T1].[ADDITIONALDECLARATIONS],[T1].[ADDITIONALINSTRUCTIONS],[T1].[ADDITIONALNOTES],[T1].[BOLADDRESS],[T1].[BOLCARRIERNAME],[T1].[BOLFREIGHTEDBY],[T1].[COMPANYADDRESS],[T1].[COMPANYBANKACCOUNTNAME],[T1].[COMPANYBANKACCOUNTNUM],[T1].[COMPANYBANKACCOUNTREGNUM],[T1].[COMPANYCOMMERCIALREGISTER],[T1].[COMPANYCOMMERCIALREGISTERINSETNUMBER],[T1].[COMPANYCOMMERCIALREGISTERSECTION],[T1].[COMPANYCOREGNUM],[T1].[COMPANYEMAIL],[T1].[COMPANYENTERPRISECODE],[T1].[COMPANYENTERPRISENUMBER],[T1].[COMPANYGIRO],[T1].[COMPANYNAME],[T1].[COMPANYPHONE],[T1].[COMPANYREGNUM],[T1].[COMPANYTELEFAX],[T1].[CUSTOMERREF],[T1].[DELIVERYADDRESS],[T1].[DELIVERYDATE],[T1].[DELIVERYNAME],[T1].[ENTERPRISEREGISTER_NO],[T1].[INTERNALPACKINGSLIPID],[T1].[INVOICINGADDRESS],[T1].[INVOICINGNAME],[T1].[JOURNALRECID],[T1].[MCREXPEDITE],[T1].[MCREXPEDITEDORDER],[T1].[ORDERACCOUNT],[T1].[ORDERACCOUNTENTERPRISECODE],[T1].[ORDERACCOUNTREGNUM],[T1].[ORDERACCOUNTVATNUM],[T1].[PACKINGSLIPID],[T1].[PDSENABLED],[T1].[PREPRINTLEVEL],[T1].[PRINTLOGO],[T1].[PRINTSHIPPMENT],[T1].[PRINTTRANSPORTATIONDOCUMENT],[T1].[PURCHASEORDER],[T1].[SALESADMINISTRATOR],[T1].[SALESID],[T1].[SHIPCARRIERBLINDSHIPMENT],[T1].[TRANSPORTATIONCOMPILERNAME],[T1].[TRANSPORTATIONCONTACTPERSONNAME],[T1].[TRANSPORTATIONCONTACTPERSONTITLE],[T1].[TRANSPORTATIONCONTRACTORADDRESS],[T1].[TRANSPORTATIONCONTRACTORNAME],[T1].[TRANSPORTATIONISSUEDBYNAME],[T1].[TRANSPORTATIONISSUEDBYTITLE],[T1].[TRANSPORTATIONLOADEDDATETIME],[T1].[TRANSPORTATIONLOADEDDATETIMETZID],[T1].[TRANSPORTATIONLOADERADDRESS],[T1].[TRANSPORTATIONLOADERNAME],[T1].[TRANSPORTATIONOWNERADDRESS],[T1].[TRANSPORTATIONOWNERNAME],[T1].[TRANSPORTATIONPACKAGEDANGERDEGREE],[T1].[TRANSPORTATIONPACKAGEDESCRIPTION],[T1].[TRANSPORTATIONVEHICLEDESCRIPTION],[T1].[TRANSPORTATIONVEHICLEDRIVERNAME],[T1].[TRANSPORTATIONVEHICLEPLATENUMBER],[T1].[TRANSPORTATIONVEHICLETRAILERNUMBER],[T1].[COMPANYVATNUM],[T1].[SHOWCOMPANYVATNUM],[T1].[ISPROFORMA],[T1].[CREATEDDATETIME],[T1].[CREATEDBY],[T1].[CREATEDTRANSACTIONID],[T1].[RECVERSION],[T1].[PARTITION],[T1].[RECID],[T1].[COMPANYLOGO],[T1].[DELIVERYREASON],[T1].[DLVMODE],[T1].[DLVTERMS],[T1].[FORMLETTERREMARKS],[T1].[MAINNOTES],[T1].[SHIPPINGINFO] FROM [SALESPACKINGSLIPHEADERTMP] [T1] WHERE [PARTITION]=@1 AND [DATAAREAID]=@2

IN fact these are fields form the standard table SalesPackingSlipHeaderTmp. Does it mean my table is locked somehow by the table SalesPackingSlipHeaderTmp? how to further troubleshoot this?

SPID of the last records differ from the top ones

and SQLStatementText field value is

(@P1 nvarchar(max),@P2 nvarchar(max),@P3 nvarchar(max),@P4 nvarchar(max),@P5 nvarchar(21),@P6 bigint,@P7 nvarchar(1001),@P8 numeric(32,6),@P9 numeric(32,6),@P10 numeric(32,6),@P11 bigint,@P12 nvarchar(21),@P13 int,@P14 nvarchar(21),@P15 nvarchar(21),@P16 int,@P17 int,@P18 nvarchar(21),@P19 bigint,@P20 nvarchar(5),@P21 int,@P22 bigint,@P23 bigint)INSERT INTO SALESPACKINGSLIPDETAILSWBUNDLESTMP (PACKINGSLIPID,DELIVERYDATE,INVOICEID,INVOICEDATE,ITEMID,JOURNALRECID,NAME,ORDERED,QTY,REMAIN,CUSTPACKINGSLIPTRANS,INVENTTRANSID,BUNDLE,BUNDLEPARENT,SALESID,SALESPACKINGSLIPTMPTABLETYPE,EXPANDCOMPONENTSONPACKINGSLIPPRINT,CREATEDBY,CREATEDTRANSACTIONID,DATAAREAID,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23)

this lock is done by my first insert_recordset to my custom table which I do before the first update.

Was this reply helpful? Yes No
dark_knight 1,230 on at

Like (0)

Report
Copy link

Link copied!
After the second update I see again multiple locks

again standard table salespackingslipdetailstmp from which i copy records to by custom table like that

SalesPackingSlipDetailsWBundlesTmp salesPackingSlipDetailsWBundlesTmp; SalesPackingSlipDetailsTmp salesPackingSlipTmp = this.getSalesPackingSlipDetailsTmp(); this.setTransactionConnection(salesPackingSlipDetailsWBundlesTmp); salesPackingSlipDetailsWBundlesTmp.skipDataMethods(true); salesPackingSlipDetailsWBundlesTmp.skipDatabaseLog(true); insert_recordset salesPackingSlipDetailsWBundlesTmp ( JournalrecId, InventTransId, ItemId, Name, Ordered, Qty, Remain, CustPackingSlipTrans, SalesPackingSlipTmpTableType ) select JournalrecId, InventTransId, ItemId, Name, Ordered, Qty, Remain, CustPackingSlipTrans, currentTableType from salesPackingSlipTmp where salesPackingSlipTmp.TableNum == 1;

also new table showed up sysdocubranddetailsregular.

all these records relating to spid 68 are locked by this query

(@P1 int,@P2 int,@P3 nvarchar(21))UPDATE T1 SET BUNDLEPARENT=T3.BUNDLEPARENT,RECVERSION=@P1 FROM SALESPACKINGSLIPDETAILSWBUNDLESTMP T1 CROSS JOIN SALESLINE T2 CROSS JOIN SALESLINEBUNDLE T3 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'cwl')) AND (T1.SALESPACKINGSLIPTMPTABLETYPE=@P2)) AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'cwl')) AND (T2.INVENTTRANSID=T1.INVENTTRANSID)) AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'cwl')) AND ((T3.SALESLINERECID=T2.RECID) AND (T3.BUNDLEPARENT<>@P3)))

so it's expected my second update as far as i understand

in the end I again see these records relating to different SPID 69 and to my custom table. the records like afte the first update

blocking query is still insert_into

(@P1 nvarchar(max),@P2 nvarchar(max),@P3 nvarchar(max),@P4 nvarchar(max),@P5 nvarchar(21),@P6 bigint,@P7 nvarchar(1001),@P8 numeric(32,6),@P9 numeric(32,6),@P10 numeric(32,6),@P11 bigint,@P12 nvarchar(21),@P13 int,@P14 nvarchar(21),@P15 nvarchar(21),@P16 int,@P17 int,@P18 nvarchar(21),@P19 bigint,@P20 nvarchar(5),@P21 int,@P22 bigint,@P23 bigint)INSERT INTO SALESPACKINGSLIPDETAILSWBUNDLESTMP (PACKINGSLIPID,DELIVERYDATE,INVOICEID,INVOICEDATE,ITEMID,JOURNALRECID,NAME,ORDERED,QTY,REMAIN,CUSTPACKINGSLIPTRANS,INVENTTRANSID,BUNDLE,BUNDLEPARENT,SALESID,SALESPACKINGSLIPTMPTABLETYPE,EXPANDCOMPONENTSONPACKINGSLIPPRINT,CREATEDBY,CREATEDTRANSACTIONID,DATAAREAID,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23)

can this insert_recordset lock somehow affect second update and cause it to be blocked?

Was this reply helpful? Yes No
dark_knight 1,230 on at

Like (0)

Report
Copy link

Link copied!

Also, in your test code, try updating all records in both cases, not different records.

I tried and both updates went well.

Was this reply helpful? Yes No
Martin Dráb 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

No, I don't think that locks on your table are obtains by the select statement for the other table.

In my opinion, that you get blocked by the update lock must mean that the things happen in different transactions. If it was the same transaction, the second lock request would simply get the existing lock. It's blocked by the lock if it was obtained in another transaction. Focus on transactions in your further analysis.

I wonder what's the point of copying all records from SalesPackingSliptDetails? Maybe the overall design isn't ideal and your problems with queries will disappear if you use the database in a better way. I suggest you forget all the technical details for a moment and review the business requirement and your design of its implementation.

Was this reply helpful? Yes No
Martin Dráb 239,152 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Also, try if SrsReportDataProviderPreProcess::disableLockEscalation() won't make any difference. Its documentation says that it's explicitly intended "to get around the lock escalation issues exhibited by preprocessing reports tables that are populated using set-based inserts".

Was this reply helpful? Yes No