Lock database table for just a couple of sentences

Question

Suppose a table in SQLServer with this structure:

TABLE t (Id INT PRIMARY KEY)

Then I have a stored procedure, which is constantly being called, that works inserting data in this table among other kind of things:

BEGIN TRAN

DECLARE @Id INT = SELECT MAX(Id) + 1 FROM t

INSERT t VALUES (@Id)

...
-- Stuff that gets a long time to get completed
...

COMMIT

The problem with this aproach is sometimes I get a primary key violation because 2 or more procedure calls get and try to insert the same Id on the table.

I have been able to solve this problem adding a tablock in the SELECT sentence:

DECLARE @Id INT = SELECT MAX(Id) + 1 FROM t WITH (TABLOCK)

The problem now is sucessive calls to the procedure must wait to the completion of the transaction currently beeing executed to start their work, allowing just one procedure to run simultaneosly.

Is there any advice or trick to get the lock just during the execution of the select and insert sentence?

Thanks.

Answer 1

TABLOCK is a terrible idea, since you're serialising all the calls (no concurrency). Note that with an SP you will retain all the locks granted over the run until the SP completes. So you want to minimise locks except for where you really need them.

Unless you have a special case, use an internally generated id:

CREATE TABLE t (Id INT IDENTITY PRIMARY KEY)

Improved performance, concurrency etc. since you are not dependent on external tables to manage the id.

If you have existing data you can (re)set the start value using DBCC

DBCC CHECKIDENT ('t', RESEED, 100)

If you need to inject rows with a value preassigned, use:

SET IDENTITY_INSERT t ON

(and off again afterwards, resetting the seed as required).

[Consider whether you want this value to be the primary key, or simply unique. In many cases where you need to reference a tables PK as a FK then you'll want it as PK for simplicity of join, but having a business readable value (eg, Accounting Code or OrderNo+OrderLine is completely valid) : that's just modelling]