Does MS SQL Server hold transactions in queue?

Question

Is it possible for SQL Server to 'hold' transactions and then execute them in one go?

Just some background:

I have a client with a decently large SQL Server with their ERP databases and our scanning databases. By the way, we are a 3rd party scanning software company. The user scans an item and we check if that barcode exists in the database and is already scanned. If yes, we display an error on the scanner UI. If not, we allow the scan to be populated in our table.

What seems to happen sometimes is that SQL Server may be processing something heavy, and this causes our transaction to 'wait' in the queue. Once it's done, it then allows my transaction to go through.

The problem is that the user thinks that he did not scan properly, and so he does it again and again, resulting in multiple transactions queuing up. So now the stored proc does not validate properly i.e. the first transaction populates the table fine. The second one should have checked if the data exists and throw up an error. But because it does it all in one go, I get duplicate entries.

Does SQL server 'hold' transactions, and if yes, how do I go about avoiding the duplicates (aside from primary keys)?

Answer 1

What seems to happen sometimes is that the SQL server may be processing something heavy, and this causes our transaction to 'wait' in the queue.

What you might be experiencing is resource contention - physical limitations of the hardware behind the server being hit by a busy workload, depending on how heavy the something is.

When a query is running and something is causing it to wait, there is an associated wait type with that bottleneck. A helpful tool for identifying what a given query is waiting on is sp_WhoIsActive. Once you identify what the wait type is, you can find out more information on what it means and its root causes by using SQLSkills's Wait Type Library.

how do I go about avoiding the duplicates (aside from primary keys).

Hopefully you are using keys and constraints appropriately to help prevent data integrity issues in the database layer.

But also as mentioned in the comments, you should definitely be proactively preventing subsequent scans of the same item occurring in your application layer. Either your application should be waiting on the results from your SQL query before the user can re-scan the same item. Or if your application is sending database calls in an asynchronous fashion, then you should implement your own queuing system that processes each scan sequentially in the database layer, to allow the transaction to complete on one scan before processing the next one.