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

[thomas_duemesnil]

You can not be sure.

What I use is the following. ( Probably this is also not safe 🙂 But has worked for me many years )

BEGIN ATOMIC

    select ORDERKEY_KEY, ORDERKEY_LASTCHANGED
    into nKey, tsDate
    from "AFAdmin"."ORDERKEY" with (holdlock)
    where ORDERKEY_TYPE = cORDERKEY_TYPE;

    if nKey is null
        -- insert
    else
        -- update
    end if;

END

What you also have to be aware of is that these building your own PK Generator is Top 38 on the list How To Make SQL Anywhere Slow.

In newer Versions of SQLA you should try to create a sequences. Creating a sequence

[former_SQLA_member1694902]

I'd need to create a sequence for each new Id, I wanted to store, though, wouldn't I? The advantage of the hack I've got at the moment is that it grows dynamically.

[thomas_duemesnil]

Yes. But it is define one time and working much faster and more efficient than any approach you can come up with ! Depends on your needs.