In a recent customer project I had to fix an error where a certain record seemed to be updated on EP but not in AX.
In an expense module one would have the ability to upload expenses/receipts. For each expence you would be able to add expense lines.
In this case when uploading an expence and returning and trying to add expence lines in an AX bound grid – the grid would update just fine, but when looking in AX, the record wouldn’t have updated.
I spent the first couple of days trying to figure out a way to force EP to update the record in Ax without any luck – and I was really trying everything and everyway like described here.
I almost by accident discovered that having uploaded a new receipt and trying to update the record from EP – it was also not possible to update the record from within AX – ax just hang. This was somekind of breakthrough since I now had an idea of the record being locked. I started to take interest in the database and analyzing locks:
select * from sys.tables where OBJECT_id in
(select distinct rsc_objid from sys.syslockinfo)
Navigating my flow on EP, I realized that a lock would be created in the EPSTATESTORE table and this lock wouldn’t disappear by itself as it should. The big question was now when this lock would be created. It took some time narrowing down where the lock would be created and wich of my webparts actually caused it but finally I was certain to have found the right webpart. Analyzing the code gave me absolutely no clue ablut where the lock could be created –everything seemed just fine. Once again I had to take drastic measures and started to tear down the webpart commenting one method at a time trying to identify which method would cause the lock. As You can imagine this was pretty time consuming but I was certain that finding and removing the cause of the lock would solve almost every problem we had.
Finally i found that a call to a static method of the dataset in AX would cause the lock and lloking into that method in AX It was obvious that an inbalance in ttsbegin and tts commit caused the lock. Ttsbegin was places outside an if statement where the ttscommit was placed inside the if statement.
The great point of this article is that next time when I experience something not working from EP that should be working I will start looking at possible locks a bit sooner as i did facing this problem.
A link to two other sql scripts that I used can be found here