SQL Server function - prevent duplicates

Question

I have a SQL Server 2005 database. Table A has an identity column in it as well as another manual Id column that spans several rows. The second id column is in the format of '0000000098' and must be a string. I cannot change the data type.

Function A gets the max ID column value, increments it by one, cast as string.

My stored proc gets the new ID, and then does several inserts using this id.

How can I prevent two clients from running the stored procedure and getting the same ID before the update occurs? Can I lock table a from reads until the proc is done processing or is there a more efficient way?

If I could change the data type and/or structure, it would be easy, but I can't.

Answer 1

If you can formulate your function as a single UPDATE statement, then no explicit locking is needed - the UPDATE statement will require an update lock (U) and this is exclusive, e.g. no two readers can get an update lock on the same row at the same time.

UPDATE dbo.TableA
SET ManualID = CAST(CAST(ManualID AS INT) + 1 AS VARCHAR(20))
OUTPUT Inserted.ManualID  -- return the newly inserted manual ID for your use
WHERE ..........

If you need to have a two-step process - SELECT before UPDATE - then I'd use the WITH (UPDLOCK) hint on the SELECT

DECLARE @OldManualID VARCHAR(20)

BEGIN TRANSACTION

SELECT @OldManualID = ManualID 
FROM dbo.TableA WITH (UPDLOCK)
WHERE........

-- do something with that manual ID

UPDATE dbo.TableA
SET ManualID = (new value of ManualID)
WHERE ..........

COMMIT TRANSACTION

In both cases, since the single UPDATE or the SELECT/UDPATE run under an update lock, no two processes can run this at the same time. I don't think you need any further locking at all - most definitely not a total table lock....

Answer 2

Yes, you can lock the table. You need to do all the things inside the transaction and when you read the table for the very first time you can place on it an exclusive continuous lock with hints (updlock, holdlock) or even (TABLOCKX)