473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Stored Procedure Thread Safe?

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
5 6526
debasisdas
8,127 Expert 4TB
NO, there is no synchronization between calls.
Feb 19 '08 #2
ghd
22
How can we remedy the situation then? Does the sql server provide any solution?

kind regards,

ghd
Feb 20 '08 #3
ck9663
2,878 Expert 2GB
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
ghd
22
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
ghd
22
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

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

Similar topics

3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
3
by: Mark | last post by:
If a java applicaiton using the type 4 driver calls a DB2 stored procedure, does the stored procedure need to do its own commit when updates are completed? If the stored procedure does a commit or...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
2
by: trialproduct2004 | last post by:
Hi all, i am having application which is using stored procedure written in sql server. I want to develop multithreaded applicaton. The store procedure is returing list of rows of table. what i...
4
by: Magy | last post by:
What would be the best way to execute a Oracle stored procedure that excepts several input paramters, through a web method in vb.net. What would be a good way to get to the web method, the Oracle...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
10
by: Dick | last post by:
Using Visual Studio 2003 you were able to manage SQL stored procedures with Visual Source Safe. So long as Visual Studio was used as the editor, it was imposible to make a change to a stored...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
2
by: acw | last post by:
On a SQL Server 2000 db I would like to setup a stored procedure that accesses couple tables and runs the extended stored procedure xp..cmdshell. The goal is to grant users with limited privileges...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.