Prevent concurrent execution of SQL Server stored

Question

I want to prevent two users from executing the same stored procedure concurrently. If two asp.net requests come in to execute that stored procedure, then those should execute in serial manner one after another.

SQL Server database and execution is handled by ado.net.

Will any of below methods help to achieve this ? What is the most suitable method ? Is there any other ways to achieve the same ?

1. Execute the stored procedure with ado.net transaction by setting isolation level to Serializable

2. Use sp_getapplock inside stored procedure and release at the end

Answer 1

Execute the stored procedure with ado.net transaction by setting isolation level to Serializable

Yes you can you can use the Serializable isolation level with your ado.net transaction. It is the highest isolation level and it relies on pessimistic concurrency control & guarantees consistency by assuming that two transactions may try to update the same data and uses locks to ensure that they do not. One transaction must wait for the other to complete and they can deadlock.

Use sp_getapplock inside stored procedure and release at the end

Yes you can use SQL Server application locks sp_getapplock. You will have to release the lock with sp_releaseapplock

Another option is to use Global Temporary Tables.

Answer 2

I would recommend utilizing a queuing system in the middle tier of your architecture, between ASP.NET and your database. That way requests recieved and processed in order. You can then configure the system to only process one request at a time,