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.
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....