Geek asks and answers

TSQL mutual exclusive access to stored procedure

Several web servers access SQL Server to obtain numerical code, when this code does not exist, it must be autogenerated by SQL Server.

I need to make sure that even if two simultaneous calls arrive and the code does not exist, only one code is generated, 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 read a little about isolation levels and table locking, but I have a terrible mess with all of this. At first I thought the SERIALIZABLE isolation level is what I need, but apparently it is not.

So, what would you do to perform a “lock” in TSQL?

Many thanks.

UPDATE:

, , this :

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, 15, 1, get_code, 4 'SERIALIZABLE. , A WITH . SQL Server . Msg 102, 15, 1, 5 "END".

?

+3
3

SERIALIZABLE - , semaphore.

, , TXN, .

sp_getapplock . , ..:

, TRY CATCH ROLLBACK?

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
+8

. MetaInfo MetaKey varchar(max) and MeatValueLong bigInt.

, , . rowlock . (, , , , .)

CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID bigInt 
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    -- This section can be removed if you want to pass in an id.
    SET @inID = 0

    UPDATE MetaInfo WITH (ROWLOCK) 
      SET MetaValueLong = CASE 
                            WHEN ISNULL(MetaValueLong,0) > @inID THEN MetaValueLong+1 
                            ELSE @inID+1
                          END 
    WHERE MetaKey = 'Internal-ID-Last'

    SELECT MetaValueLong 
    FROM MetaInfo
    WHERE MetaKey = 'Internal-ID-Last'

    COMMIT TRANSACTION 

END
+2

Yes, SET ISOLATION LEVEL SERIALIZABLE is exactly what you need. This does not allow dirty records and dirty reads. All db objects that are inside serializable transactions are blocked, so other connections can only read / write when the first one commits or rolls back.

+1
source

More articles:


All Articles