By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Stored Procedure Thread Safe?

P: 22
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
Expand|Select|Wrap|Line Numbers
  1.                CREATE TABLE sequences (
  2.                  seq            varchar(100) primary key,
  3.                  sequence_id    int
  4.                );
  5.  
The following code creates the stored procedure that gets the next value from the specified sequence in the 'sequences' table.

Expand|Select|Wrap|Line Numbers
  1.                CREATE PROCEDURE nextval
  2.                  @sequence varchar(100),
  3.                  @sequence_id INT OUTPUT
  4.                AS
  5.  
  6.                -- return an error if sequence does not exist
  7.                -- so we will know if someone truncates the table
  8.                set @sequence_id = -1
  9.  
  10.                UPDATE sequences
  11.                SET    @sequence_id = sequence_id = sequence_id + 1
  12.                WHERE  seq = @sequence
  13.  
  14.                RETURN @sequence_id
  15.  
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
Feb 19 '08 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
NO, there is no synchronization between calls.
Feb 19 '08 #2

P: 22
ghd
How can we remedy the situation then? Does the sql server provide any solution?

kind regards,

ghd
Feb 20 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Use trigger to cascade your updates and use transactions so that you can commit or rollback as necessary.

Happy coding.

-- CK
Feb 20 '08 #4

P: 22
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
Feb 21 '08 #5

P: 22
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
Feb 21 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.