web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Table Select Blocked b...
Microsoft Dynamics GP (Archived)

Table Select Blocked by sySecurityModAltOperations or GP Power Tools if installed

(0) ShareShare
ReportReport
Posted on by chadbruels 1,588

I am getting a timeout on a Select statement. When I search for blocking the identified process is an operation on SY10800 (sySecurityAssignModAltOperations). Here are details on that blocking:

Block_5F00_by_5F00_SY10800.png

I can see in a Script log that GP code which interacts with SY10800 is running right before my custom code begins:

Blocking_5F00_Details_5F00_Script_5F00_Lg.png

In another installation I have GP Power Tools installed; I get the same blocking behavior but the identified blocking session is an operation on one of that product's tables:

Block_5F00_by_5F00_Power_5F00_Tools.png

I find myself especially wondering why each listed blocking operation is a "CREATE PROC" for a "zDP_" procedure. I understand what the zDP procedures are and do, but I don't understand why Create statements would be run for them in the middle of transaction processing nor why they would create a LCK_M_S lock.

Now I could add WITH (NOLOCK) to my first Select operation, but then I have others and the next one simply times-out. I'll accept adding WITH (NOLOCK) to everything if that is the only viable solution. However, I am skeptical and that feels like a band-aid solution.

*This post is locked for comments

I have the same question (0)
  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Is your SELECT statement running from a Dex application or is this a SELECT statement executed in Management Studio? If the former, is this statement running as a result of a database trigger (Trigger_RegisterDatabase)?

  • chadbruels Profile Picture
    chadbruels 1,588 on at

    The statements are running from a .NET library whose methods are called from my Dexterity product.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Do the zDP_ procedures referenced by the CREATE statements actually exist? If they don't, can you open the GP SQL Maintenance window and recreate them for those two tables (drop auto procedure, recreate auto procedure)?

  • chadbruels Profile Picture
    chadbruels 1,588 on at

    They do exist.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Under the dbo schema? Or do they belong to a different schema -- I had to ask

  • chadbruels Profile Picture
    chadbruels 1,588 on at

    They are under the dbo schema.

  • chadbruels Profile Picture
    chadbruels 1,588 on at

    Performing a SQL trace, I don't see the CREATE PROC that is shown in the results of the blocking reveal query. I only see the execution of the zDP procedure. This blocking is occurring on every post of a Sales Invoice. I have seen it report LCK_M_S and LCK_M_X. Here is the query I am using to investigate:

    SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests 
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50 
AND blocking_session_id > 0
UNION
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections 
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id 
                    FROM sys.dm_exec_requests 
                    WHERE blocking_session_id > 0)


  • chadbruels Profile Picture
    chadbruels 1,588 on at

    Considering how I was getting different results of what was the blocking statement between the environments with and without GP Power Tools, I started investigating a different line of thinking. I started commenting out some of my code leading up to the call into the DLL. I found the reported blocking statement will always be whatever the last statement which was run. My current theory then is that I have triggered my Dexterity code to a point after which GP standard code has issued a BEGIN TRANSACTION as part of the Sales Invoice posting process. I am assuming my .NET called code cannot run in this context, but I don't have anything to support that assumption.

    UPDATE 1: I performed a test of the idea that a transaction is directly responsible. This was done outside the Sales Invoice posting process, where my real scenario takes place. Here is the psuedo code of the test:

    { Dexterity Trigger in my dictionary fires }
transaction begin;

call global procedure WebServiceStub;

{ Web Service Stub }
using MyDLL.Utilities;

local dotNETObject l_dot_net_object;

l_dot_net_object = new dotNETObject();
l_dot_net_object.Calculate();

{ Exit Web Service Stub }
transaction commit;


    The .NET code in the DLL ran without issue. No blocking occurred. Of course, in the real scenario the transaction is started/committed in the Dynamics.dic code instead of in my dictionary. I just don't know why that would make a difference. From Dexterity Help: "If your integrating application registers a trigger that is activated within a Microsoft Dynamics GP transaction, any table operations your script performs will become part of the transaction."

  • Verified answer
    chadbruels Profile Picture
    chadbruels 1,588 on at

    My test scenario mentioned above, it was not accurately representing an item I overlooked. To better represent the scenario, I needed to add my transaction begin statement earlier, so that operations are performed on the tables I am trying to interact with in the .NET code. When I do this, I can confirm that operations on a table become part of the transation such that they are locked for operations on another connection until the transaction is committed or rolled back. So my resolution must be to either identify a different trigger point that does not fall within the Dexterity database transaction -OR- I may be able to define a second trigger point where my first interaction with my tables in my Dexterity code occurs before the transaction starts.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Now that I understand the problem, what we've done to get pass this issue is to, execute our code within the same connection opened by GP as supposed to opening a new connection. I will confirm and get back to you in the AM with how we did this.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans