We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle our transaction. We're considering of enabling read-committed snapshot on our DB, but according to this answer, it seems that won't really solve anything and is prone to introduce more issues later on.
The whole process of ordering products to shipping is very intricated (The application has been in use for 19 years and has been consistently maintained by numerous developers). Here's a pseudocode of how a batch ordering process uploaded from an excel file is locking up our DB:
Load all items from excel files Begin Transaction Loop per item Perform all validation checks, such as checking current stocks, authorization to order etc (There's a lot of complex checks going on here) Insert new item to order table If an error occurs during insert, rollback and return End Loop Commit Transaction
Since our order table is within a transaction, this means other users must wait until the whole job is done to perform any operation on it. This particularly is causing a lot of issues for warehouse employees, where they must wait for a long time until they can mark the items as shipped on our order table before actually shipping them.
One might say "The transactions should be kept as humanly short as possible", but the thing is, if any error occurs during this order process, the whole job should be rollbacked to initial state. This is part of the program requirement.
I'm not a DB expert, so I maybe missing something critical here but given our requirements, it seems like the only option is to enable row versioning on our transactions.
I'd really like to know how this type of issue could be resolved.