Could a SELECT inside of a transaction lock the table?

I would like to know if it’s possible that a select is blocking a table if it’s inside a transaction.

It’s something like this:

CREATE PROCEDURE InsertClient (@name   NVARCHAR(256))
AS
BEGIN
    DECLARE @id INT = 0;

    BEGIN TRY 
        BEGIN TRAN InsertingClient
            SELECT @id = MAX(ID) + 1 FROM Clients;

            INSERT INTO Clients (Id, Name) 
            VALUES (@id, @name);

            SELECT id, name 
            FROM Clients;

            COMMIT TRAN InsertingClient
     END TRY
     BEGIN CATCH      
         ROLLBACK TRAN InsertingClient
     END CATCH;
END

It’s a dummy example, but if there’s a lot of records in that table, and an API is receiving a lot of requests and calling this stored procedure, could be blocked by the initial and final select? Should I use the begin and commit only in the insert to avoid the block?

Thanks!

Answer

Based on the sample code you have provided it is critical that the first select is within the transaction because it appears you are manually creating an id based on the max id in the table, and without locking the table you could end up with duplicates. One assumes your actual code has some locking hints (e.g. with (updlock,holdlock)) to ensure that.

However your second select should not be in your transaction because all it will serve to do is make the locks acquired earlier in the transaction last the additional time of the select, when (again based on the sample code) there is no need to do that.

As an aside there are much better ways to generate an id such as using an identity column.