Locking in a SQL Server Stored Procedure

Question

I have a stored procedure that manipulates an integer table field (which represents a sequence number) based on some criteria - the criteria can reset the field back to zero. In a multiuser environment there is a possibility that the field may be referenced by 1 user before it is updated by another user so I would like to prevent this by limiting the stored procedure to only run for 1 user at a time. Is there a way to do this within my stored procedure?

Answer 1

If you wrap your statements inside a transaction, they will all be performed atomically. You may need to increase the transaction isolation level depending on your needs, though.

For example, if you don't want anyone else reading or writing to a particular table while you execute a bunch of statements, this statement at the top will make that happen:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

I don't recommend escalating to this level unless absolutely necessary, though, because it basically breaks the concurrency benefits.

Instead, consider using something lower or reworking your logic to remove the need of the critical section.