CODEWITHSUNDEEP
sql-serverconcurrencytransactionslocks
Mohit
Mohit

Reputation: 51

Sql Server Stored Procedure Concurrency Issue

We have some SQL Server stored procedure. It selects some rows from a table and puts them into the temp table to apply and calculate some data validations. The next part of the procedure either updates the actual table based on the temp table data or sends back the error status. Initially selected rows can only be updated once and no further updates are allowed to the same rows.

The problem, we are facing is like some time, 2 simultaneous threads execute the procedure at the same time and both pass the initial validation block as in-memory temp data is not processed yet. 2nd thread is able to overwrite the first transaction.

We applied the transaction mechanism to prevent duplicate inserts and updates by checking the affected rows count by update query and aborting the transaction.

I am not sure if it's correct and optimized or not. Also, can we lock rows with a select statements as well ?

enter image description here

Upvotes: 0

Views: 351

Answers (1)

Mohit
Mohit

Reputation: 51

This has been solved using the UPDLOCK on select query inside the transaction. It locks the specific rows and allow the transaction to proceed in isolation. Thanks Everyone for your help.

Upvotes: 1

Related Questions