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

[MarkCulp]

You did not mention which version of SQLA you were using?

If you are using version 11 or above then I would suggest you using a MERGE statement.

Example: I believe that something like this will do everything you need (Note: I am using WATCOM SQL syntax):

begin
    set @IDValue = 1;  -- assume we are going to insert
    merge
     into "DBA"."UniqueIds" as ov( "IdName", "ID" )
    using ( select @idName as "IdName",
                   1       as "ID" ) nv
       on ov."IdName" = nv."IdName"
     when matched then update
      set ov."ID" = ov."ID" + 1,
          @IDValue = ov."ID"   -- extract the ID value from the table
     when not matched then insert;
   commit;
   return @IDValue;
end;

The way that the above MERGE statement works is this: We construct a row "nv" (for "New Value") and then merge that row into the existing "ov" (for "Old Value") that is your UniqueIds table. If the new value IdName matches a rows in old value (aka UniqueIds) then we update the row setting ov."ID" = ov."ID" + 1; else when there is not a match then we simply insert the new row into the UniqueIds table.

Regarding your question about locking: A row is not locked within a transaction until it is selected (depending on your isolation level) or updated. For example, if you are using isolation level 0 then no read locks are taken.

HTH

[former_SQLA_member1694902]

I'll have a look at MERGE. Looks interesting.

[former_SQLA_member1694902]

I'm using version 11.0.1.2661 by the way

[VolkerBarth]

Note: AFAIK, the important point is that selecting the ID value and possibly incrementing the next ID value are done in one single (and therefore atomic) statement here. That should prevent "dirty reads" here, as only one transaction can execute that statement for a given "IDName" - the next transaction trying to do so would be blocked by a write (or insert) lock.