sql server - TSQL mutual exclusive access in a stored procedure

Question

Several web servers access a SQL Server to get a numeric code, when this code doesn't exist, it has to be autogenerated by the SQL Server.

I need to ensure that even if two concurrent calls come in and the code doesn't exist, only one code is created and both calls return the same code. So I have to do something like this:

begin lock
  if code exists
    return code
  else
    generate code
    return code
end lock

I've been reading a little about isolation levels and table locking, but I have a terrible mess with all that. First I thought that a SERIALIZABLE isolation level is what I need, but apparently it's not.

So, what would you do to accomplish a "lock" in TSQL?

Thanks a lot.

UPDATE:

I got this error when I try to set the serializable level using this as example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE get_code 
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION

    select code from codes where granted is null;
END
GO

Msg 1018, Level 15, State 1, Procedure get_code, Line 4 Incorrect syntax near 'SERIALIZABLE'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'END'.

What does it means?

Answer 1

SERIALIZABLE is an isolation level for locking, not a semaphore.

It won't work in this case all you'll do is persist a read lock to the end of the TXN that doesn't prevent another process into the code reading.

You need to use sp_getapplock in Transaction mode. You can configure it to wait, bomb immediately etc: up to you

This is based on my template from Nested stored procedures containing TRY CATCH ROLLBACK pattern?

ALTER PROCEDURE get_code 
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int, @result int;

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0 BEGIN TRANSACTION

    EXEC @result = sp_getapplock 'get_code', 'Exclusive', 'Transaction', 0 
    IF @result < 0
        RAISERROR('INFO: One at a time please`!', 16, 1);

    [...Perform work...]


    IF @starttrancount = 0 
        COMMIT TRANSACTION
    ELSE
        EXEC sp_releaseapplock 'get_code';
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO