This blog article discusses the Sage 200 Database settings to reduce possible deadlock errors upon posting transactions.
In certain environments, users may experience deadlock errors when processing a transaction. This will most commonly be seen in source document processing like Customer invoices/Returns or Purchase orders and GRVs.
The error may display itself in the following way:
We have found that this can occur in very specific environments with very high volumes of transactions that are going through on a continuous basis. An FMCG (Fast Moving Consumer Goods) environment may be an ideal candidate for this type of error to occur if the hardware or network components at the site are dated or have bottlenecks in one or more places.
We have found in the event that this does occur there is a struggle from multiple users to write data into a common table/database field on the SQL database. The records then basically allow one user at a time to write and lock out all other users.
In a fast efficient environment, this should not be a problem. However, as mentioned above, the environment seems to play a big role in this occurrence.
We have acknowledged that not all environments can be spruced up or upgraded at will, so we have made an option available in the Sage 200 application that defers the posting to the _etblAccblnc table. This is the table where all General Ledger accounts' balances are housed, and reporting is usually generated from this GL balances table.
Do the following to help prevent Deadlock Errors when processing transactions:
1. Go to Administration | System Configuration | System Wizard | Database Settings tab.
2. Unselect the option marked below. In addition, also specify the number of days after which you should be warned if a GL Relink has not been run.
Doing this may lead to a better user processing experience where no deadlock errors are experienced.
Please Also Note:
Unselecting the above option should result in the _etblAccblnc table not being constantly / automatically updated when processing any transaction in the company, as is usually the case.
However, you should therefore manually run the General Ledger | Maintenance | General Ledger Relink to update the _etblAccblnc table and to establish having updated GL-related reports, e.g., Income Statement, Balance Sheet, etc. Do this only after making a company backup and ensuring all other users are logged out of the company.
Running a GL Relink should in this case be done at the end of each business day, but its frequency should be defined by yourself. As seen above there is also a reminder that will notify if a GL Relink has not been run.
This is a useful option if you are struggling with Deadlock errors when processing.
The above can be used in troubleshooting or resolving the problem on hand until such time that the network or hardware can be reviewed.