Stored Procedure Thread Safe?

[ghd]

Below i am producing a series of steps that creates a stored procedure. The stored procedure behaves like a sequence in oracle.

The code below creates the table by name 'sequences'. This table will be used to hold the next value that can be used for a particular sequence

               CREATE TABLE sequences (
                 seq            varchar(100) primary key,
                 sequence_id    int
               );

The following code creates the stored procedure that gets the next value from the specified sequence in the 'sequences' table.

               CREATE PROCEDURE nextval
                 @sequence varchar(100),
                 @sequence_id INT OUTPUT
               AS

               -- return an error if sequence does not exist
               -- so we will know if someone truncates the table
               set @sequence_id = -1
              
               UPDATE sequences
               SET    @sequence_id = sequence_id = sequence_id + 1
               WHERE  seq = @sequence

               RETURN @sequence_id

Now my question is that if this procedure is run on the same database instance by multiple users simultaneously, will the procedure be 'synchronized' (that is will it run to completion for a user before starting to run for another one)?

kind regards,

ghd

[debasisdas]

NO, there is no synchronization between calls.

[ghd]

How can we remedy the situation then? Does the sql server provide any solution?

kind regards,

ghd

[ck9663]

Use trigger to cascade your updates and use transactions so that you can commit or rollback as necessary.

Happy coding.

-- CK

[ghd]

Use trigger to cascade your updates and use transactions so that you can commit or rollback as necessary.

Happy coding.

-- CK

But how do i detect that a race condition has occured and be able to correct the situation. Worst still, how do i notify the user(s) who have got the wrong value(s) due to such a race condition?

kind regards

ghd

[ghd]

But how do i detect that a race condition has occured and be able to correct the situation. Worst still, how do i notify the user(s) who have got the wrong value(s) due to such a race condition?

kind regards

ghd

I think i need to use transaction isolation level. I will come back if that doesn't work.

kind regards,

ghd