Dynamics Community Forum Thread Details

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

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

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).

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL