x Want to participate? Join the group to interact
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 9 replies
  • Subscribers 17 subscribers
  • Views 1954 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
murni

Deadlock error on customized Sales Order

SOLVED
Posted By

Hi all,

I am making a custom screen for Sales order. Everything was fine when I am making a sales order from 1 PC. However, when 2 users from 2 different PCs is making a sales order and posting the sales orders at the same time, this error occurs. It occurs while adding item line (SOPOrderReturnLine) for the sale order.

Sage.ObjectStore.DeadlockException: Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at Sage.Common.Exceptions.ExceptionManager.Throw(Exception e)
   at Sage.ObjectStore.DataSourceExecutor.<>c__DisplayClass4.<RunLocal>b__0()
   at Sage.ObjectStore.TransactionContext.RunAction(Action action)
   at Sage.ObjectStore.DataSourceExecutor.RunLocal()
   at Sage.ObjectStore.DataSourceExecutor.Run()
   at Sage.ObjectStore.DataSourceAccessor.DataSourceCollectionEnumerator.Run()
   at Sage.ObjectStore.DataSourceAccessor.DataSourceCollectionEnumerator.System.Collections.IEnumerator.Reset()
   at Sage.ObjectStore.DataSourceAccessor.GetEnumerator()
   at Sage.ObjectStore.PersistentObjectCollection.Fill()
   at Sage.ObjectStore.PersistentObjectCollection.FillInternal()
   at Sage.ObjectStore.PersistentObjectCollection.get_Count()
   at Sage.ObjectStore.PersistentObjectCollection.AddNew()
   at Sage.Accounting.SOP.SOPStandardItemLine.CreateOrderAllocationLine(AllocationBalance oAllocationBalance)
   at Sage.Accounting.SOP.SOPStandardItemLine.OnStockAllocation(AllocationBalance oAllocationBalance, Decimal QuantityNotAllocated, Boolean AllocationFailure)
   at Sage.Accounting.Stock.StockPosting.OnStockAllocation(AllocationBalance oAllocationBalance, Decimal QuantityNotAllocated, Boolean AllocationFailure)
   at Sage.Accounting.Stock.StockWarehouseAllocationPosting.UpdateInternal()
   at Sage.Accounting.Stock.StockPosting.Update()
   at Sage.Accounting.Stock.StockWarehouseAllocationActivity.UpdateBinItem(BinItem oBinItem, Decimal dfQuantityToAllocate, Decimal dfQuantityToReserve, Boolean bAllowNegativeStock, Boolean bCreatePreReceipts)
   at Sage.Accounting.Stock.StockWarehouseAllocationActivity.ReserveStock()
   at Sage.Accounting.Stock.StockAllocationActivity.SetQuantityProtected(Decimal value)
   at Sage.Accounting.Stock.StockAllocationActivity.UpdateInternal()
   at Sage.Accounting.Stock.StockActivity.UpdateWorker(Boolean inServiceCall)
   at Sage.Accounting.Stock.StockActivity.Update()
   at Sage.Accounting.SOP.SOPStandardItemLine.CreateAllocationActivity(BinItem oBin, Decimal dAllocateQuantity, TraceableSOPAllocationAdjustment oTraceableAdjustment)
   at Sage.Accounting.SOP.SOPStandardItemLine.AdjustAllocationLines(Decimal dQuantityAdjustment, BinItem oBinItem, Boolean bAllocateIndividualItems, TraceableSOPAllocationAdjustment oTraceableAdjustment)
   at Sage.Accounting.SOP.SOPStandardItemLine.AdjustAllocationLines(Decimal dQuantityAdjustment, BinItem oBinItem)
   at Sage.Accounting.SOP.SOPStandardItemLine.PostProtected(Boolean CreateCancelledLine)
   at Sage.Accounting.SOP.SOPOrderReturnLine.Post(Boolean CreateCancelledLine)
   at Infusion.Fastafood.Sage2002011.Business.SOPOrderLayer.AddNonTraceableStandardItemLine(SOPOrderReturnLine Line)

In the process, this is what I did. 

1. create order

2. lock order

3. create item line "SOPOrderReturnLine" < The error occurs here

4. add charge line, discount like if any

5. complete order, unlock order

6. immediately make deliveries and invoices.

The making of order:

public void CreateOrder(SYS.SOPOrderType SOPOrderType, long formid)
        {
            bool applyBestDiscount = false;
            bool overrideOnHold = true;
            bool confirmLines = true;

            bool successful = false;
            orderType = SOPOrderType;
            Sage.Accounting.Common.ActiveLock activeLock = null;
            try
            {
                //int unprintedInvoiceCount = GetUnprintedInvoiceCount();
                Sage.Accounting.SalesLedger.SalesCreditNoteInvoiceNumber salesCreditNoteInvoiceNumber = Sage.Accounting.SalesLedger.SalesCreditNoteInvoiceNumberFactory.Factory.Fetch();
                //Set the order number
                if (!this.sopLedger.Setting.AutoGenOrderReturnNos)
                {
                    long nextInvoiceNo = salesCreditNoteInvoiceNumber.NextInvoiceNumber;
                    long currentInvoiceNo = 0;
                    order.DocumentNo = (salesCreditNoteInvoiceNumber.NextInvoiceNumber).ToString();
                    SharedLayer sharedLayer = new SharedLayer();
                    do
                    {
                        currentInvoiceNo = nextInvoiceNo;
                        this.order.DocumentNo = nextInvoiceNo.ToString();
                        int numberOfZero = 10 - this.order.DocumentNo.Length;
                        while (numberOfZero != 0)
                        {
                            this.order.DocumentNo = "0" + this.order.DocumentNo;
                            numberOfZero--;
                        }
                        nextInvoiceNo++;
                    } while (sharedLayer.isDocumentNoExist(this.order.DocumentNo));

                    order.Fields.FindItem("InvoiceNumber").Value = currentInvoiceNo;
                    
                    salesCreditNoteInvoiceNumber.NextInvoiceNumber = currentInvoiceNo + 1;
                    salesCreditNoteInvoiceNumber.Update();
                    salesCreditNoteInvoiceNumber.Dispose();
                    salesCreditNoteInvoiceNumber = null;
                }

                // Set the order to be full order entry (as opposed to trade and rapid)
                order.EntryType = Sage.Accounting.SOP.SOPOrderEntryTypeEnum.EnumSOPOrderEntryTypeFull;

                //Warehouse
                Sage.Accounting.Stock.Warehouses warehouses = new Sage.Accounting.Stock.Warehouses();
                order.Warehouse = warehouses.First;

                //SOP Order Type
                switch (orderType)
                {
                    case SYS.SOPOrderType.Normal: order.Fields.FindItem("SOPOrderType").Value = "Normal"; break;
                    case SYS.SOPOrderType.Mixed: order.Fields.FindItem("SOPOrderType").Value = "Mixed"; break;
                    case SYS.SOPOrderType.Prepaid: order.Fields.FindItem("SOPOrderType").Value = "Prepaid"; break;
                    case SYS.SOPOrderType.Reserved: order.Fields.FindItem("SOPOrderType").Value = "Reserved"; break;
                }

                order.DocumentCreatedBy = Sage.Accounting.Application.ActiveUserName.ToString();
                // Save the order before we add lines
                order.Update();
                // Lock the order so that no one else can amend it
                activeLock = Sage.Accounting.Application.Lock(order);

                originalSequence = new List<short>();
                //Standard LIne
                if (this.order.Lines.Count > 0)
                {
                    foreach (SOPOrderReturnLine line in order.Lines)
                    {
                        line.Fields.FindItem("XCustomerID").Value = order.Customer.SLCustomerAccount;
                        Sage.Accounting.Stock.StockItems sis = new Sage.Accounting.Stock.StockItems();
                        sis.Query.Filters.Add(new Sage.ObjectStore.Filter(Sage.Accounting.Stock.StockItem.FIELD_CODE, line.ItemCode));
                        sis.Find();
                        if(sis.First != null)
                        {
                            line.Fields.FindItem("XItemID").Value = sis.First.Item;
                        }
                        AddNonTraceableStandardItemLine(line);
                    }
                }
                // Add a standard stock item
                AddCustomizeLine();

                // Add an additional charge for carriage
                AddAdditionalChargeLine();
                // Amend auto discount unit price


                // Apply order discounts
                ApplyDiscount();

                // Validate the order
                ConfirmSalesOrder(order, applyBestDiscount, overrideOnHold, confirmLines);

                Sage.Accounting.Application.AllowableWarnings.Add(order, typeof(Ex20235Exception));
                order.Post(applyBestDiscount, overrideOnHold);
                if (orderType == SYS.SOPOrderType.Normal)
                {
                    rearrangePrintSequenceNumber(order.SOPOrderReturn, originalSequence);
                }
                successful = true;

            }
            catch (Sage.Accounting.Exceptions.Ex20579Exception Ex20579Exception)
            {
                MessageBox.Show(Ex20579Exception.Message + " Order is cancelled. ");
                Logger.WriteLog("Ex20579Exception error=" + Ex20579Exception.ToString());
                order.Cancel();
            }catch (OrderNumberAlreadyExistsException orderNoAlreadyExistsEx) {
                Logger.WriteLog("DocumentNo="+order.DocumentNo+"; orderNoAlreadyExistsEx="+ orderNoAlreadyExistsEx.ToString()+";");
                MessageBox.Show("["+order.DocumentNo + "] " + orderNoAlreadyExistsEx.Message);
                order.Cancel();
            }
            catch (Exception oException)
            {
                MessageBox.Show(oException.Message + " Order is cancelled. ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Logger.WriteLog("oException error=" + oException.ToString());
                order.Cancel();
            }
            finally
            {
                // Make sure we unlock the order
                if (activeLock != null)
                {
                    activeLock.Dispose();
                }

                if (successful)
                {
                    Sage200InvoiceTransaction(formid);
                }
            }
        }

AddNonTraceableStandardItemLine() function is called to create new SOPOrderReturnLine for the order. The error is happening during the SOPOrderReturnLine.Post();

private void AddNonTraceableStandardItemLine(SOPOrderReturnLine Line)
        {
            //Tax Code
            Sage.Accounting.TaxModule.TaxCode taxCode = Sage.Accounting.TaxModule.TaxCodeFactory.Factory.FetchTaxCodeWithCode(2);

            Sage.Accounting.Stock.Views.WarehouseStockItemViews warehouseStockItemViews = new Sage.Accounting.Stock.Views.WarehouseStockItemViews();

            SOPStandardItemLine line = Line as SOPStandardItemLine;

            originalSequence.Add(short.Parse(line.PrintSequenceNumber.ToString()));

            try
            {
                //(1) POPOrder
                line.SOPOrderReturn = order;

                //(2) WarehouseItem
                Sage.Accounting.Stock.Views.WarehouseStockItemView warehouseStockItemView = null;

                // Add the stock item filter
                warehouseStockItemViews.Query.Filters.Add(new Sage.ObjectStore.Filter(Sage.Accounting.Stock.Views.WarehouseStockItemView.FIELD_ITEM, line.Item.Item));
                warehouseStockItemViews.Query.Filters.Add(new Sage.ObjectStore.Filter(Sage.Accounting.Stock.Views.WarehouseStockItemView.FIELD_USEFORSALESTRADING, true));
                warehouseStockItemViews.Query.Sorts.Add(new Sage.ObjectStore.Sort(Sage.Accounting.Stock.Views.WarehouseStockItemView.FIELD_WAREHOUSEID, false));
                warehouseStockItemViews.Query.Find();

                // Find the first warehouse bin that has available stock
                foreach (Sage.Accounting.Stock.Views.WarehouseStockItemView view in warehouseStockItemViews)
                {
                    if (view.FreeStockAvailable > System.Decimal.Zero)
                    {
                        warehouseStockItemView = view;
                        break;
                    }
                }

                if (warehouseStockItemView != null)
                {
                    line.WarehouseItem = warehouseStockItemView.WarehouseItem;

                    line.SellingUnit = line.Item.Units.First;
                    line.LineTaxValue = 0;
                    line.TaxCode = taxCode;
                    line.TaxCodeDbKey = taxCode.SYSTaxRate;
                    line.AnalysisCode1 = order.AnalysisCode1;

                    // Save the line
                    line.Post(sopLedger.Setting.RecordCancelledOrdLines);
                    line.Update();

                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                Logger.WriteLog("AddNonTraceableStandardItemLine - "+ex.ToString());
            }
        }

After I Google the error, it is related to SQL Server where more than 2 processes is waiting for the other to finish transaction. I am suspecting I messed up at stage 2, but locking the order is necessary. 

How do I prevent this error?

Thank you.

  • 0

    There's a few things we need to consider before getting too deep into this one.

    Firstly : the 'lock' that you're applying to the Sales Order record is entirely separate from a SQL Server lock (and it's SQL Server locks that ultimately lead to deadlocks). An ActiveLock just creates an entry in the SYSActiveLock table and is used at the Sage 200 application level to stop other users from modifying a locked record. It doesn't issue any kind of X/IX lock. To see this, you can either lock a record in code or simply edit a Sales Order in Sage 200 itself. Then you can run the following t-sql query (obviously you can only do this on a test machine where you're the only user - you can't really do this on a production system where other users might be placing locks):

    You should find that the query doesn't show any locks at the row (or page, or table) level. However, if you do something like this in SSMS....

    ...where we're updating a row but not committing or rolling back the transaction, then the query above should show an IX lock.

    So that's a long-winded way of saying that the ActiveLock in your code probably isn't to blame here.

    The next question I would ask is : Is this repeatable? Can you reliably create a deadlock every time you have two users performing these actions? Also : is this happening in production or is it on your test system?

    If the deadlock is just something you're coming up with when you're testing then maybe you don't want to spend too much time investigating. It might be the case that you're creating an atypical scenario which might not happen in production. But if it's repeatable then there are a few things you can do to help you diagnose the problem. Let me know if this is the case and I'll give you some pointers as to where you might go next to investigate.

    That said : judging by the stack trace I can get a rough idea of where the problem might lie. The SQL Server deadlock monitor will always choose whichever transaction will take the least amount of time/resource to roll back as the deadlock victim. So you find that reads tend to end up as the victim when the conficting transaction is updating or inserting data. And I think that's the case here. We see in the trace that we're getting as far as the CreateOrderAllocationLine method of SOPStandardItemLine - then we see AddNew(), get_Count(), FillInternal etc. Basically what's happening here is that Sage200 is attempting to read from the SOPAllocationLine table, and something has presumably placed a conflicting lock on a row that it wants to read. This is a bit weird, unless there's something else going on in your code that I'm not aware of (an outer transaction?)- or something very suboptimal happening with a SQL Server query plan (if it's a test system then you could maybe just try running DBCC FREEPROCCACHE in SSMS to make sure you've not got a strange cached plan somewhere).

    Let me know if it is a repeatable problem and if so I'll go into more detail about deadlock graphs and all that fun stuff.

  • 0 in reply to Chris Burke

    Hi Chris,

    the error is in the test server and it is repeatable. Oddly, if the users only adding 1 item line for each orders, there is no error. If the users is adding multiple item lines for each orders, the deadlock error come up.

  • 0 in reply to murni

    Hi,

    I'm wondering if you'll get the problem if you miss out step 6 - where you're immediately processing the order (as this will require updates of SOPAllocationLine). It would be useful if you can get hold of a couple of deadlock graphs, as they will show exactly what tables/indexes are involved.

    The easiest way to do this without setting up a custom Extended Events session is to go here in SSMS:

    ...then right-click on the 'package0.event_file' node and select 'View Target Data'. Then you can filter the result via the Extended Events -> Filters menu to create a filter on 'name' with a value of 'xml_deadlock_report'.

    Then you need to recreate the deadlock a few times and wait for the deadlock report to show up (it can take a couple of minutes for this to happen). You'll get a graph that looks something like this (this is just a deadlock I deliberately created in SSMS):


    We need to see if the graph is basically the same each time. If you can post the graph (or the xml representation) up here then that would be handy.

  • 0 in reply to Chris Burke

    Hi Chris, this is the deadlock that I derive from the deadlock event on 2 Jan 2022. Sorry I am not able to recreate it because I do not have 2 PC at the moment.

  • 0 in reply to murni

    Geat, I'll have a proper look at that later (got work to do now!). Although I can see that my initial guess was correct - it's the session that's trying to read SOPAllocationLine that is the deadlock victim. It's strange that it's using the PK_SOPAllocationLine clustered index though - that's indicating a table scan, where I'd have hoped it would have used the IX_SOPAllocationLine_SOPOrderReturnLine index to do a seek-and-range-scan. Maybe that's the problem and we need to nudge the query optimizer in the right direction. 

  • 0 in reply to murni

    Hi,

    As this is your test system I'm wondering....are you just using the same Stock Item over and over again on each line of the orders while you're testing? We can see from the graph that the deadlock victim has an exclusive lock on BinItem and that's what the the other session is trying to read (again, this might be down to the fact that it's doing a table scan rather than an index seek so it might be a red herring).

  • 0 in reply to Chris Burke

    Hi Chris, for this order, all of the item lines are unique.

    (I did not show the Stock Code and description earlier because its a real product. But I guess I can show you the stock code above.)

    Or do you means the both orders might have same stock code as item line?

  • 0 in reply to murni

    Hi,

    I've been very busy so I've not had much of a chance to have a look at this. Have you got any further?

    If we go back to the stack trace that you initially posted, I can see that Sage 200 begins a SQL transaction in the ReserveStock() method of StockWarehouseAllocationActivity at line 20 (that's not obvious from the stack trace, that's by examining the source in Reflector) and that transaction is still open at the point you're getting the exception. So that ties in with the deadlock graph; the victim has an exclusive lock on BinItem - which will have been placed when the BinItem record is updated in UpdateBinItem(). Then by the time it gets to CreateOrderAllocationLine() it's unable to read SOPAllocationLine because a row lock has been placed (so I have to assume a table scan). As the locks demanded by this other process are happening in the opposite order to the victim (it places an exclusive lock on SOPAllocationLine then wants to read BinItem), I'm guessing it must be the stuff that's immediately dispatching orders that is doing this. Have you tried missing out that step to try to prove or disprove this? Also - are any parts of your code wrapped in an outer PersistentObjectTransaction?

  • +1
    verified answer

    Hi Murni,

    Looking at this case, it looks to be as though a lock in one of your processes has yet to be cleared. This is not something however that Developer Support would assist with as you are working in the context of a custom screen, and the functionality would not be replicable in a vanilla 200 environment.

    Chris looks to have given you some suggestions to work through. Hopefully these steps/processes resolve your issue.

*Community Hub is the new name for Sage City