Transact SQL Procedure BEGIN TRANSACTION / COMMIT TRANSACTION Question

[former_SQLA_member1694902]

I have a questions about an SQL procedure. I'm replacing some code in the front end application with an SQL Procedure to allow other languages to use the same logic, shocking behaviour, I know. The procedure checks for the existence of a record, if it exists it increments it and returns the incremented value or inserts a new record where necessary and returns "1". Anyway, we have a couple of hundred users all potentially accessing this code, so I want to be absolutely certain that it was going to work how it's supposed to. My question is this: on the comment marked "HERE", is there any possibility that another connection could alter that value before the SELECT sets the variable? Is the row locked by the BEGIN TRANSACTION command in a way that will avoid concurrency issues? From the documentation, I gather that it does but I wanted to ask a person/people.

Also, is there a better/faster way for this code to work? Any tips would be appreciated.

The second/inner BEGIN/COMMIT section is probably unnecessary but, I'm new to SQL Procedures so I'm covering all bases.

 DECLARE @IDValue BIGINT

    BEGIN TRANSACTION
      IF EXISTS(SELECT * FROM "DBA"."UniqueIds" WHERE "IdName" = @IdName)
        BEGIN
           BEGIN TRANSACTION
             UPDATE "DBA"."UniqueIds" SET "Id" = "Id" + 1 WHERE "IdName" = @IdName
             //--------HERE--------
             SELECT @IDValue = "ID" FROM "DBA"."UniqueIds" WHERE "IdName" = @IdName
           COMMIT TRANSACTION 
        END
      ELSE
        BEGIN
          INSERT INTO "DBA"."UniqueIds" ("IdName", "Id") VALUES(@IdName, 1)
          SET @IDValue = 1
      END

   COMMIT TRANSACTION
   RETURN @IdValue

[former_SQLA_member1694902]

How about this, the suggestion came up a couple of time to use shadow tables and GET_IDENTITY to solve the problem.

ALTER FUNCTION "DBA"."GetNextIdNew"(@IdName CHAR(20) )
RETURNS BIGINT
AS
BEGIN
    DECLARE @ReturnValue BIGINT

  DECLARE @TableExists INT
  DECLARE @TableName CHAR(40)
  DECLARE @OldIdValue INT
  SET @OldIdValue = 0
  SET @TableName = 'Shadow_'||@IdName

  SELECT @TableExists = COUNT(1) FROM sysObjects WHERE "Name" = @TableName

  IF @TableExists = 0
    BEGIN
      //MESSAGE 'Table Does Not Exist' TO CLIENT
      EXECUTE CreateShadowTable @IdName

      SELECT @OldIdValue = "Id" FROM UniqueIds WHERE "IdName" = @IdName
      IF @OldIdValue > 0
        EXECUTE SA_RESET_IDENTITY @TableName, 'DBA', @OldIdValue
      SET @ReturnValue = GET_IDENTITY(@TableName, 1)
    END
  ELSE
    BEGIN
      //MESSAGE 'Table Exists' TO CLIENT
      SET @ReturnValue = GET_IDENTITY(@TableName, 1)
    END

    RETURN @ReturnValue
END

The entire @TableExists = 0 section will only ever happen once for each Id and I'll go through each one in the old table and call this proc to create the shadow tables. It will also take care of any new Ids added this way, although that shouldn't be happening any more.

The create table proc is watcom procedure because I couldn't get it to work with t-sql, because I'm dumb: The IF NOT EXISTS is probably unnecessary, too.

ALTER PROCEDURE "DBA"."CreateShadowTable"(IN tablename char(20) )

BEGIN
   EXECUTE IMMEDIATE
   'CREATE TABLE IF NOT EXISTS Shadow_' || tablename ||
   ' ( IdValue INT PRIMARY KEY DEFAULT AUTOINCREMENT)'
END