Overview

Implementation of transaction processing timeout and rollback (Transaction Rollback) began in AGRIS 10.3.1 (issue AGR-52033) with the Microsoft SQL (MSSQL) database only.

Transaction Rollback allows a partially updated transaction to rollback previous updates when the transaction does not complete.  This prevents out-of-balance conditions and other partial updates of transactions during errors, timeouts, loss of power, and other abrupt endings to an AGRIS session.  With Transaction Rollback, inserts and updates are not committed to the database until all related inserts and updates are successful.

Transaction Rollback is enabled in the following areas in AGRIS 10.3.1 and greater on MSSQL:

• Accounts Payable Enter Disbursements
• Accounts Receivable Apply Payments
• Commodity Ticket Import
• Commodity Produce A Settlement
• Commodity Automatic Settlements
• Inventory Stock Additions (Add and Adjust)
• Inventory Invoice Entry (for each split invoice, not all split invoices as a whole)
• Inventory Automatic Invoices

Transaction Rollback is enabled in these additional areas in AGRIS 10.3.2 and greater on MSSQL:

• Accounts Payable Voucher Import
• Accounts Receivable Invoice Import
• Accounts Receivable Payment Import
• Commodity Actual Weights & Grades Import
• Commodity Apply From Storage
• Commodity Delivery Sheets
• Commodity Freight Expenses
• Commodity Reverse Tickets
• Commodity Void Settlements
• Inventory Reverse Invoice Entry
• Inventory Delivery Ticket
• Name/Address Import

Configuration

Transaction Rollback is enabled by default for all MSSQL datasets

The Transaction (record-in-use) timeout length is configurable in Customize > System Configuration.  The default is 5 minutes.  Set the value to 0 to disable Transaction Rollback in all datasets.

Business Rules

Please note the following additional business rules regarding AGRIS record locks encountered during a Transaction:

• SQL Transaction Timeout is the amount of time the process will wait for the record lock to clear during a Transaction.  If not cleared, the transaction is rolled back.
• F4 on a record-in-use is disabled with the Transaction Rollback feature.  In order to continue, either remove the record lock or wait for it to timeout.
• The ability to set the record-in-use timeout length is crucial.  Committing an entire transaction at once potentially creates record locks at the database level on those rows not yet committed.  This is different than AGRIS record locks.  Database locks have the potential to block another user's queries.  Because of this the record-in-use timeout should be relatively short - enough time to potentially find the user that can undo the record lock, but not long enough to cause problems for other users trying to access the same data.

Transaction Rollback Example

The user is producing a sales settlement.  The settlement contains 5 delivery sheets.  The settlement will create an A/R Invoice.

After the user enters invoice information, the update process starts.  The settlement updates.  The first 4 delivery sheets and contracts update.  The 5th delivery sheet updates.  The 5th contract attempts to update but is locked by another user.  The settlement user sees a record-in-use.  The record-in-use times out.  The transaction rolls back.  The user sees an InUse error box.  The process returns to the main menu.  The net result is the settlement is a zero-amount voided settlement.  The invoice is a zero-amount voided invoice.  The delivery sheets and contracts are unchanged.  The delivery sheets are available for inclusion on a new settlement.