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.