Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks for the answer. One question though: the validation process mostly involves reads from other tables, and since data integrity is very important in this operation (I.E: We need to prevent negative stocks). Unless if I'm assuming wrong, shouldn't the validation process be in the transaction to prevent dirty data from being read? –  l46kok Feb 13 at 8:45
    
One more question, I don't really follow when you say "It may be possible to open a transaction and commit after each row". Our program requirement is that should any error occur during the batch placement of orders, all of the work including previous successful ones should be rollbacked to initial state. Is there some function in SQL Server I'm not aware of that achieves this functionality while having transactions open only on individual rows? –  l46kok Feb 13 at 8:51
    
In that case you should only be locking the row of product table on which you need to update the stock number. I would suggest that the validation process needs optimising you will need to investigate both how the data is stored (whether more relevant indexes are needed etc.) and how correct the queries are. This can be done by looking at the execution plans for each query (both estimated and actual). –  Zack Feb 13 at 20:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.