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) / eConnect Deadlock Errors
Microsoft Dynamics GP (Archived)

eConnect Deadlock Errors

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

We have just recently updated a customer to GP10 service pack 5 and now their integrations which create a number of sales and inventory transactions are creating deadlock errors.  This did not happen previously.  When run individually the integrations all succeed, when run together they cause the deadlocks but it is at random times.  Has anyone come across this before?

 

THanks

R

*This post is locked for comments

I have the same question (0)
  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at

    So was it solved? Anyone else have this issue? I am facing similar deadlock issue.

  • Community Member Profile Picture
    Community Member on at

    This problem was not resolved, although when the client got to GP2013 the issues went away.  So, not sure what caused the initial problem.

  • Derek Albaugh Profile Picture
    Derek Albaugh on at

    Hello Almas,

    While we don't frequently see deadlock issues with eConnect, we have seen a case or two where it occurs.

    To avoid deadlocks with eConnect, we recommend the following:

    1) If we don't have multiple users trying to run the same eConnect procedures in the same company database at the same time, that would usually prevent deadlocks from happening.

    2) If the eConnect procedures are processing large sets of data, try splitting that data into smaller sets, so instead of one, long process that can be interrupted by other users running the procedures, it would be more of a short, small blip, which wouldn't be as likely to be interrupted or disconnected by another user/process.

    Thanks,

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at

    @Robert , thanks for your reply. : )

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at

    Thanks Derek

    What if we can not avoid point 1.

    for point 2, can I have some example?

    Also can we do something about it by eConnect.Threads?? I was reading below post. Any idea?

    dynamicsgpland.blogspot.com/.../is-econnect-multi-threaded.html

  • Derek Albaugh Profile Picture
    Derek Albaugh on at

    I don't know that anyone here has tested multi-threading as mentioned in the blog, and even the blog itself mentions it isn't a good idea, so I wouldn't go that route.

    In regards to option 2, it just means that, using payables transactions as an example, instead of trying to integrate a large volume of transactions through eConnect into Dynamics GP at any one time, you instead split them up into parts, such as instead of 1000 transactions, break it up into 4 sets of 250, etc.

    Basically, the shorter the process time to integrate the data through eConnect into Dynamics GP, the less likely you are to run into any type of deadlock scenario.

    That being said, the Incoming Service may be an option as well, as it queues things up, which should also limit the deadlocks, rather than just doing a 'blast' of requests to the integration service all at once.

    In the eConnect Programmer's Guide, on page 85 (91 of 160), it starts talking about the Incoming Service and a sample setup. (I tried to attach the guide here, but it wouldn't let me......)

    Thanks,

  • steveendow Profile Picture
    steveendow 2,281 on at

    Almas,

    Do you know if there are / were any modified eConnect Pre or Post procs? And are there any custom SQL triggers?

    I've imported tens of millions of transactions with eConnect, and have never personally encountered a deadlock, so my first inclination is to look at anything outside of a vanilla eConnect insert that might be causing the issue.

    Robert's note about the issue starting after upgrading to GP 10 SP 5, but then going away after upgrading to GP 2013, makes me think that particular customer's issues might have been due to some quirk in the GP 10 SP5 eConnect update.

    I like Derek's suggestion to work with smaller data sets.  I personally never submit more than one transaction to eConnect at a time.  Even if I have 10,000 transactions in the queue, I only submit one transaction at a time to eConnect, as I find it easier to manage errors.

    And regarding my blog post about multi-threading with eConnect, I would not recommend it.  If you are getting deadlocks with a single threaded import, the problems would only get worse with a multi-threaded import.

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at

    No worries Derek, I have that guide, I will give it a look. thanks. :)

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at

    Steve,

    No there is no pre and post procs modifications. and no custom SQL trigger.

    but there is gp trigger fires each time IV00102 gets update

    Date,Source,Severity,Message
02/27/2018 10:46:34,spid34s,Unknown,waiter id=processb7382564e8 mode=X requestType=wait
02/27/2018 10:46:34,spid34s,Unknown,waiter-list
02/27/2018 10:46:34,spid34s,Unknown,owner id=processb851047c28 mode=X
02/27/2018 10:46:34,spid34s,Unknown,owner-list
02/27/2018 10:46:34,spid34s,Unknown,keylock hobtid=72057594046185472 dbid=5 objectname=DYNAMICS.dbo.SY01500 indexname=PKSY01500 id=lockb836ce2400 mode=X associatedObjectId=72057594046185472
02/27/2018 10:46:34,spid34s,Unknown,waiter id=processb851047c28 mode=U requestType=wait
02/27/2018 10:46:34,spid34s,Unknown,waiter-list
02/27/2018 10:46:34,spid34s,Unknown,owner id=processb7382564e8 mode=X
02/27/2018 10:46:34,spid34s,Unknown,owner-list
02/27/2018 10:46:34,spid34s,Unknown,ridlock fileid=1 pageid=24219 dbid=8 objectname=TWO.dbo.IV00101 id=lockb851771680 mode=X associatedObjectId=72057594060537856
02/27/2018 10:46:34,spid34s,Unknown,resource-list
02/27/2018 10:46:34,spid34s,Unknown,BEGIN DECLARE @stored_proc_name char(31) DECLARE @retstat int DECLARE @param3 numeric(19<c/>5) DECLARE @param4 int set nocount on SELECT @stored_proc_name = 'DYNAMICS.dbo.smGetNextNoteIndex' EXEC @retstat = @stored_proc_name 1<c/> 98<c/> @param3 OUT<c/> @param4 OUT SELECT @retstat<c/> @param3<c/> @param4 set nocount on END
02/27/2018 10:46:34,spid34s,Unknown,inputbuf
02/27/2018 10:46:34,spid34s,Unknown,unknown
02/27/2018 10:46:34,spid34s,Unknown,frame procname=adhoc line=1 stmtstart=376 stmtend=504 sqlhandle=0x02000000e091251478d0394c1dd2e4453b94d4ce4d85fb520000000000000000000000000000000000000000
02/27/2018 10:46:34,spid34s,Unknown,update   SY01500 set   @O_mNoteIndex = NOTEINDX<c/>  NOTEINDX = NOTEINDX + 1.0 where   CMPANYID = @I_sCompanyI
02/27/2018 10:46:34,spid34s,Unknown,frame procname=DYNAMICS.dbo.smGetNextNoteIndex line=1 stmtstart=1152 stmtend=1366 sqlhandle=0x03000500ee011c37eb74d1002ea7000001000000000000000000000000000000000000000000000000000000
02/27/2018 10:46:34,spid34s,Unknown,executionStack
02/27/2018 10:46:34,spid34s,Unknown,process id=processb7382564e8 taskpriority=0 logused=0 waitresource=KEY: 5:72057594046185472 (e1784bd73cba) waittime=2995 ownerId=42693835 transactionname=user_transaction lasttranstarted=2018-02-27T10:46:27.140 XDES=0xb72a6dd900 lockMode=X schedulerid=8 kpid=10912 status=suspended spid=77 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-02-27T10:46:31.670 lastbatchcompleted=2018-02-27T10:46:31.670 lastattention=1900-01-01T00:00:00.670 hostpid=9048 loginname=FAABS isolationlevel=read committed (2) xactid=42693835 currentdb=5 lockTimeout=4294967295 clientoption1=539033632 clientoption2=119856
02/27/2018 10:46:34,spid34s,Unknown,Proc [Database Id = 8 Object Id = 1299900098]
02/27/2018 10:46:34,spid34s,Unknown,inputbuf
02/27/2018 10:46:34,spid34s,Unknown,unknown
02/27/2018 10:46:34,spid34s,Unknown,frame procname=TWO.dbo.taSopLineIvcInsert line=1 stmtstart=245860 stmtend=246234 sqlhandle=0x03000800c2e67a4d3246d5002ea7000001000000000000000000000000000000000000000000000000000000
02/27/2018 10:46:34,spid34s,Unknown,UPDATE dbo.IV00101 SET DEX_ROW_TS = GETUTCDATE() FROM dbo.IV00101<c/> inserted WHERE IV00101.ITEMNMBR = inserted.ITEMNMB
02/27/2018 10:46:34,spid34s,Unknown,frame procname=TWO.dbo.zDT_IV00102U line=1 stmtstart=174 stmtend=408 sqlhandle=0x03000800bb34447002aed4002ea7000000000000000000000000000000000000000000000000000000000000
02/27/2018 10:46:34,spid34s,Unknown,executionStack
02/27/2018 10:46:34,spid34s,Unknown,process id=processb851047c28 taskpriority=5 logused=9832 waitresource=RID: 8:1:24219:4 waittime=3034 ownerId=42694153 transactionname=user_transaction lasttranstarted=2018-02-27T10:46:31.627 XDES=0xb815aeb088 lockMode=U schedulerid=6 kpid=4292 status=suspended spid=80 sbid=0 ecid=0 priority=-5 trancount=2 lastbatchstarted=2018-02-27T10:46:31.630 lastbatchcompleted=2018-02-27T10:46:31.640 lastattention=1900-01-01T00:00:00.640 clientapp=.Net SqlClient Data Provider hostname=BEGP1 hostpid=1332 loginname=INTRA\gpservice isolationlevel=read uncommitted (1) xactid=42694153 currentdb=8 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
02/27/2018 10:46:34,spid34s,Unknown,process-list
02/27/2018 10:46:34,spid34s,Unknown,deadlock victim=processb851047c28
02/27/2018 10:46:34,spid34s,Unknown,deadlock-list
02/27/2018 10:46:34,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000B815AEB088 Mode: U SPID:80 BatchID:0 ECID:0 TaskProxy:(0x000000B7B4FB48A0) Value:0x4ca1da00 Cost:(5/9832)
02/27/2018 10:46:34,spid5s,Unknown,Victim Resource Owner:
02/27/2018 10:46:34,spid5s,Unknown,
02/27/2018 10:46:34,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000B72A6DD900 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x000000B72A6968A0) Value:0xff9240 Cost:(0/0)
02/27/2018 10:46:34,spid5s,Unknown,Requested by:
02/27/2018 10:46:34,spid5s,Unknown,Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 1299900098]
02/27/2018 10:46:34,spid5s,Unknown,SPID: 80 ECID: 0 Statement Type: UPDATE Line #: 1
02/27/2018 10:46:34,spid5s,Unknown,Owner:0x000000B851778E40 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:80 ECID:0 XactLockInfo: 0x000000B725C13938
02/27/2018 10:46:34,spid5s,Unknown,Grant List 2:
02/27/2018 10:46:34,spid5s,Unknown,KEY: 5:72057594046185472 (e1784bd73cba) CleanCnt:2 Mode:X Flags: 0x1
02/27/2018 10:46:34,spid5s,Unknown,Node:2
02/27/2018 10:46:34,spid5s,Unknown,
02/27/2018 10:46:34,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000B815AEB088 Mode: U SPID:80 BatchID:0 ECID:0 TaskProxy:(0x000000B7B4FB48A0) Value:0x4ca1da00 Cost:(5/9832)
02/27/2018 10:46:34,spid5s,Unknown,Requested by:
02/27/2018 10:46:34,spid5s,Unknown,Input Buf: Language Event: BEGIN DECLARE @stored_proc_name char(31) DECLARE @retstat int DECLARE @param3 numeric(19<c/>5) DECLARE @param4 int set nocount on SELECT @stored_proc_name = 'DYNAMICS.dbo.smGetNextNoteIndex' EXEC @retstat = @stored_proc_name 1<c/> 98<c/> @param3 OUT<c/> @param4 OUT S
02/27/2018 10:46:34,spid5s,Unknown,SPID: 77 ECID: 0 Statement Type: UPDATE Line #: 1
02/27/2018 10:46:34,spid5s,Unknown,Owner:0x000000B84CA3CBC0 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:77 ECID:0 XactLockInfo: 0x000000B801F19938
02/27/2018 10:46:34,spid5s,Unknown,Grant List 3:
02/27/2018 10:46:34,spid5s,Unknown,RID: 8:1:24219:4               CleanCnt:2 Mode:X Flags: 0x3
02/27/2018 10:46:34,spid5s,Unknown,Node:1
02/27/2018 10:46:34,spid5s,Unknown,
02/27/2018 10:46:34,spid5s,Unknown,Wait-for graph
02/27/2018 10:46:34,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

  • Derek Albaugh Profile Picture
    Derek Albaugh on at

    The zDT_IV00102U trigger on the IV00102 table is simply doing the following, which I'd doubt would cause deadlock errors:

    UPDATE dbo.IV00101 SET DEX_ROW_TS = GETUTCDATE() FROM dbo.IV00101, inserted WHERE IV00101.ITEMNMBR = inserted.ITEMNMBR END

    As others have mentioned, it's rare to get eConnect deadlock issues as we don't frequently see them, and when we do, it's usually something external causing the issue. For example, I had one case where they were seeing deadlock errors with eConnect, granted they had users from all over the world running the same eConnect integrations 24/7.

    What we found was happening, is there were global tables, such as “##taAnalyticsDistributionUSRDEFND4” and “##taAnalyticsDistributionUSRDEFND5" that in this case, eConnect was using.

    When the eConnect process would complete for one user, it would then drop these temporary tables, even though other eConnect processes were using them. Because of this, it caused a deadlock type issue, as the temp tables were missing from the database.

    That's as close to I have seen with eConnect and deadlocks.

    Thanks

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