I have a client running 10.6.30.17 with an intermittent on-line payment issue which I would like to throw open to the community for comment. All suggestions appreciated :-)
The issue is, about 2% of on-line transactions are failing with database deadlock issues. Stored procedure asiGetCounter deadlocks when retrieving next TRANS counter value.
The deadlocks happens when
1. payments are being made both on-line and via back-office,
2. both via coldfusion web payment pages
3. and during periods of heavier activity (which in tis case is, 30-60 payments per hour)
My thoughts at this stage are that the SQL server is that there is an issue with the SQL server responsiveness rather than the stored procedure, as with 1 transaction per minute it seems unlikely that deadlocks should have this impact.
Has anyone else experienced similar issues? Performance issues with SQL server? And any thoughts on how this can be pinned down? All suggestions/considerations/commiserations very welcome!!
I have seen this
It depends on what you mean by ColdFusion. Some of the older versions of eContentManager (4.x) handled transactions in a rather non-standard way, especially if you have any Consulting customizations in place. (Sorry, ASI.) For example, I have seen code which did something along these lines:
[insert order details into Basket_Meeting and Basket_Order]
[include eSeries: Source\Meetings\cBasketProcessCheckout.cfm to insert the order in iMIS]
cfset l_OrderNumber = (select max(ORDER_NUMBER) from Orders)
[cfloop detail lines]
-- cfset l_NewLine = select max(LINE_NUMBER) + 1 from Order_Lines where ORDER_NUMBER = l_OrderNumber
-- insert Order_Lines (ORDER_NUMBER, LINE_NUMBER, ...) values (l_OrderNumber, l_NewLine, ...)
[/cfloop]
[insert a payment for that order number]
When transaction volume is heavy, it's very possible for two orders to be started at the same time, but all the detail ends up on the second order.
Deadlocks happen when two processes lock the same tables in the opposite order. Desktop iMIS has been the "gold standard" for the preferred order to touch the tables, and eSeries generally followed the same sequence. Event Manager, Dues Manager and COM iBO have all varied a bit from this. It's very possible one of these is the source of your problem, but the only way I know of to prove it is to trace (SQL Profiler) each one of them independently and make note of what tables are touched in which order. If you can demonstrate the problem, then ASI can reproduce it and fix it.
--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.