Prevent concurrent execution of stored procedure with ADO.NET in ASP.NET

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 ADP.NET transaction by setting isolation level to Serializable

Yes you can you can use the Serializable isolation level with your ADP.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.