> -----Original Message-----
> > 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.
And if you don't have a transaction ... you end up with missing numbers,
partial orders, and worse still, more deadlocks. This is because
> SQl.Add('update incrementTable
> set nextValue = :newValue
> where keyid = :keyid
> and nextValue = :originalValue');
causes the server to generate an implicit transaction - one which you have
no control over.
It is also slower because you generate two transactions instead of one; one
for the update and another for the new order insert.
If you add another field to the number generation table, and use that as a
synchronisation point to serialise inserts/updates to orders, it makes it
impossible to deadlock, thought the real solution to deadlocking is to
design your inserts/updates so that they DONT deadlock.
Regards,
Dennis.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
