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)?