[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Transaction blocking while accesing a stored procedure SQL2000

Hi.

I have an application (in Delphi and using ADO) which looks like this:
-----------------------------------------------------------------------------------------------------------------
procedure Func;
Connection.IsolationLevel:=ilReadCommitted;
Connection.BeginTrans;
...
while(some condition)
begin
...
tmp:=GetNextSN;
...
end;
...
Connection.CommitTrans;

function GetNextSN: String;
var dsGetNextSN: TADODataSet;
begin
  dsGetNextSN.CommandText:='get_next_sn';
  dsGetNextSN.Open;
  dsGetNextSN.Close;
end;

Where 'get_next_sn' is a stored procedure:
CREATE PROCEDURE [get_next_sn]
AS  
BEGIN
  SELECT next_sn FROM table1;
  UPDATE table1 SET next_sn=next_sn+1;
END
-----------------------------------------------------------------------------------------------------------------

The problem is, that when I run multiple instances of this app, they block each other while accessing the 'get_next_sn' stored procedure. The first one reaching it runs, while the others wait until it commits.

What I would need, is to block multiple access to the SP, so that the returned 'next_sn' values are unique. Any suggestions?

Thanks.
0
BlueAlien
Asked:
BlueAlien
  • 3
  • 2
1 Solution
 
Titan522Commented:
Why are you using a table to store the value? Almost any database will have something to give you an increasing number that is thread safe
0
 
BlueAlienAuthor Commented:
I'm using the table because I didn't know that there are similar functions, could you tell me the name of that function? I'm using MSSQL2000. But I dont need that number for inserting a primary key or something like that and it cannot be random!
0
 
Titan522Commented:
In Sql there is a identity column. Create a coulmn of type int bigint smallint and towards the bottom when you are designing the table there is a idenity option.

http://www.databasejournal.com/features/mssql/article.php/3307541
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
BlueAlienAuthor Commented:
I don't need this number to insert rows! I just need a function which returns numbers in ascending order for every call.
0
 
BlueAlienAuthor Commented:
I finally solved the problem. I generate the numbers outside the transaction. Which has more logic because the numbers cannot be rolled back.
0
 
ee_ai_constructCommented:
PAQ / Refund
ee ai construct, community support moderator
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now