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

[Breck_Carter]

The code you show is extremely dangerous. Its operation depends on whether the connection is operating in CHAINED = 'ON' or CHAINED = 'OFF' mode. Its operation also depends on whether a transaction is already in progress when the block is entered or not. There is no way to inspect just this code, and determine what it does.

It is highly recommended you avoid using the BEGIN TRANSACTION statement altogether, and use the CHAINED = 'ON' mode (which is the default except for Open Client and jConnect connections, where you have to remove or modify or override the effect of the evil sp_tsql_environment system procedure).

Avoiding BEGIN TRANSACTION implies you will also be avoiding use of nested COMMIT statements which are misleading to the reader. In fact, nested COMMIT statements don't do anything at all and there is no such thing as a nested transaction.

If this code is used to generate unique auto-incrementing primary key values, then switch to using DEFAULT AUTOINCREMENT on the column (like Volker said), and do not specify an explicit value when doing the INSERT. SQL Anywhere will generate unique values with no locks that cause blocks, and if you need to know what value got generated just SELECT @@IDENTITY after the INSERT.

[former_SQLA_member1694902]

I'm very aware that this is not the right way to do things. As I said, I'm pretty new to new to writing stored procedures but I'm a fairly careful programmer and I wanted to be sure about something. Apologies. I'll keep my questions to myself in future.

[Breck_Carter]

I have edited my reply to make it sound less like a personal attack. You did say it was legacy code so it was an easy assumption that you did not have a personal stake in it.

[VolkerBarth]

there is no such thing as a nested transaction

I agree that this aspect should be pointed out in the current discussion to prevent a common misunderstanding (and your original posting w.r.t. @@TRANCOUNT may have sounded harsh but was very true - and made me smile:)).

Nevertheless, it should also be stated that nested transactions (like savepoints) can be used to be able to rollback parts of a transaction without having to rollback the whole transaction - say, if you try to store a bunch of objects in one transaction, and one object would fail for any unexpected reason, and you want to store the rest of them - we have such business rules...

[VolkerBarth]

I hope the wealth of answers here in short time (and from volunteers, not to forget) is of help to you, and I hope you don't feel that we are unrespectful to your questions - please keep asking...

[Breck_Carter]

To be 100% clear, a nested savepoint and rollback is not the same as "nested transaction". When folks hear "nested transaction" they think "nested commit"... which is a dangerous fantasy 🙂