Comments below...
> Welcome to the world of C/S programming. You will need to use a
> transaction:
>
> TDatabase.StartTransaction;
> try
> TQuery.SQL.Text := 'update singleelementtable set
somefield=somefield+1';
> TQuery.ExecSQL;
> TQuery.SQL.Text := 'select somefield from singleelementtable';
> TQuery.Open;
> try
> SequentialOrderNumber := TQuery.Fields[0].AsInteger;
> finally
> TQuery.Close;
> end;
> // Insert your order here.
> TDatabase.Commit;
> except
> TDatabase.Rollback;
> raise;
> end;
>
> In C/S, you do not normally request an exclusive lock. The first query
> increments the field, and as the transaction is still pending, the server
> will lock the record. If the record is already locked (because another
> process is in the middle of a transaction), the server will block until
the
> lock is released.
And that blocking is very likely to occur if there are lots of processes
hitting this number generation table. That update becomes a bottle-neck, and
in my experience, quickly leads to problems with deadlocks.
What I think is a good database-independent implementation is something like
this (except make it syntactically correct):
You then use it like this:
OrderId := getNextSequence('orderno');
The way make sure that you have no deadlock problems is to ensure that it's
never called from inside a transaction.
create table incrementTable( keyid : varchar, nextValue : integer );
function getNextSequence( keyid : String ) : integer
var originalValue : integer;
newValue : integer;
begin
repeat
with Query1 do
begin
SQL.Add('select nextValue from incrementTable
where keyid = :keyid');
Params[0].AsString := keyid;
Open;
originalValue := fieldByName('nextValue');
newValue := originalValue + 1;
SQL.Clear;
SQl.Add('update incrementTable
set nextValue = :newValue
where keyid = :keyid
and nextValue = :originalValue');
ParamsAsAppropriate :-)
ExecSQL;
// See if the update worked
until Query1.RowsAffected = 1;
result := newValue;
end;
Of course you could just use the generator / sequence / serial no / whatever
for your particular flavour of database.
Cheers,
Kerry Sainsbury
Inprise/Borland New Zealand
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
