DB Locks - How would one handle long transactions and maintain high concurrency?

Question

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.

Answer 1

Firstly the batch order should be validated first and then committed. The transaction that commits the inserts into the database should as you say be as short as possible. In the perfect world this would mean that you do all the validation up front before beginning the transaction and committing.

From what you have said I doubt such a restructure is on the cards, so I would suggest investigating why the transaction is locking the whole table. Ideally such an insert would only have row level locks.

Depending on the structure of the data it may be possible to open a transaction and commit after each row.

Lastly, it maybe be possible to batch up the data and send it all to the database for processing.

All of these options will require a non-trivial amount of changes.

Edit:

Given your comments it would seem that you need this batch process to happen in a single transaction. This would indicate that the locking is correct. The next step is to understand what is causing the batch process to take so long. If the batch process is taking a considerable amount of time there is either an issue in the queries that read the data that is used for validation or how the data required for the validation is stored.

You will need to start looking at each query that is run as part of the validation to understand what is taking the longest and then optimise that query and so on until the whole process is much more streamlined.

Answer 2

Check with the business team to see if you can restructure the business process so that you can start and comit a new transaction for each row in the excel file. If anything failed, record the fail rows so that they would know which row was successful and which one failed

Answer 3

Can you load the record into a staging table first and perform the checks and then load it into your Orders table? This way your data is already verified and then you could either load the entire table or process them in batches into your order table. SQL server as a norm best performs on set-based operations that can be optimized by the engine by running them across multiple threads.