Solution:

During busy times of year when many orders are being entered and shipped, the possibility of transaction rollbacks due to file locking is increased. Rollback is a regular feature of the program that was introduced in Sage Pro ERP 6.5. It saves the information entered and provides the opportunity to retry the transaction if the required tables are temporarily locked by another user. Under usual circumstances, these files should only be locked for short periods of time and rollbacks should be infrequent. The retry option will most often be successful in these cases.

Important
Rollback will not actually occur until the transaction has tried to repeatedly write to the affected tables for a full three seconds.

Examples of rollback messages that result in retry option:

• Update could not be done on table [table name] since it is being updated by another user.
• The automatic numbering routine was unable to lock the transaction file or system data file. Another user may be using the file.
• Another user is using the file. Shipment process not successful for order [order number].

This article will examine some of the causes of excessive rollback messages and what may contribute to these situations.

Hardware/Environment

Please refer to the list below for some suggestions to improve performance. Additional information may be found in the “Pro Series 7.x Performance Tuning Tips” whitepaper available on the web site.

• The workstations and servers should be well above minimum requirements. Generally, processor speed is the key factor in determining how fast a process completes.

• Verify that other applications are not taxing server resources. For example, Microsoft SQL Server should be on its own server without other applications, as per system requirements.

• Other workstation issues such as a bad network interface card (NIC), disconnects to the server or ODBC connection if using Microsoft SQL version may contribute to this issue.

Process and Business Practices

It is important to keep in mind that some processes update more tables than others. Specific examples include shipping sales orders and receiving purchase orders, especially drop ship orders. If excessive rollbacks are occurring, they are usually caused by these processes and being compounded by factors such as serial numbers or bills of materials being exploded.

Typical usage of Sage Pro should not create many rollback situations. However, they may occur when shipping or receiving orders with a large number of line items during peak usage times. The larger the order, the longer it will take to update tables for all items. This is also true for shipping multiple orders at once using the Quick Ship feature. In extreme cases, it may be worthwhile to consider changing this practice of creating large orders to creating smaller orders instead. It may also be helpful to establish an end-of-day process for shipping and receiving orders (at different times) when there are fewer people in the system. If orders are being shipped complete, the Quick Ship feature can be very useful as it allows for one user to ship all orders which avoids any possibility of conflict.

Additional Resources

The following list may be helpful in determining the cause of excessive rollbacks. In all cases, look for a pattern such as a particular user and workstation which could indicate environmental issues or look for a specific process such as shipping large orders.

• Transaction Log
  The Transaction Log in System Manager records rollback information and can be run in detail or summary form. The Error Log will usually not be helpful as it does not record rollback information, only error information.
• Microsoft SQL Profiler
  If using the SQL Server version of Sage Pro, the SQL Profiler may be useful in helping identify users and processes that are locking files for extended periods of time.Related Reading
  Please refer to Knowledge Base article <rn:answer_xref> for additional information regarding using SQL Profiler.
• Microsoft SQL Locks
  If using the SQL Server version of Sage Pro, it is possible to identify who has a file locked. This can be done in the SQL Server Enterprise Manager:
  1. Open Enterprise Manager.
  2. Expand Management.
  3. Expand Current Activity.
  4. Expand Locks/Object and select the locks on the table in question.
  5. Note the Process ID for any that locks that are populated with an “X” in the Mode column; this indicates an exclusive lock.
  6. To locate the user who has this file locked, click Process Info under Current Activity.
  7. Locate the Process ID that is locked and look at the User column to find the Login of the user.

Sage Pro users will have an “AI” preceding the user same in SQL. For example “ADMN” would appear as “AIADMN” in SQL. It should also be noted that SQL Server Enterprise Manager should be refreshed regularly or the initial values will remain. It does not automatically update the view with changes

© Copyright Sage Software