Kyley
Firstly if you put the SequenceNo stored proc 'in' the transaction,
don't you already run the risk of deadlock? (as you
can't be sure of the updates the client procedures table updates and order)
So to avoid this you could 'pre' fetch the keys in a separate trans, or
post fetch them, depending on where the biggest 'risk' of rollback is
ie for statements (pre fetch)
x = no of statements to produce
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit
Begin trans, Generate statements using keys, commits/rollback
Worst case you will 'lose a sequence'
but since there is almost no chance of a fail on exec FetchKeys then I would
if (x = Begin trans, Generate statements, null keys, commits == commit) {
repeat {
Begin trans, GenerateKeys(x), commit/rollback
} until commit
apply keys to statements
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you
will have sole access to the table and as there is only
one table involved I cannot see a huge overhead
HTH
Neven
> Hi Neven,
>
> Currenlty I am using a stored proc that accesses a table in the form of
>
> (
> SequenceName varchar(100);
> KeyValue Int
> )
>
> I'm using update locks etc..
>
> my worry is that
> Transaction A asks for "ClientNO" 1-3 keys
> Transaction B asks for "ClientNO" 4-6 keys
>
> A Rolls Back
>
> Transaction C or d or e in the future may get 4-6 taken by B again
> because of A's Rollback.
>
> The keys are not used for Primary or Relational anything. I use
> UniqueIdentifier for that.. The sequences do not need to be perfect,
> gaps are allowable.. these sequences are for Data Values used by the
> software users.. ie Batch No, Client No, Debtor No, Statement No,
> Invoice No.. etc etc. and these numbers are then used for quick
> referencing of information..
>
> in interbase and Oracle.. its very easy..
>
> have to pop out for an hour.. Thanks if anyone has any ideas for
> making sure I don't get lock contention, or reissuing of the same
> number twice.
>
> On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan <[EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]>> wrote:
>
> Kyley
>
> Why not just use a stored proc as a 'generator' or a high/low
> sequence,
> I take it that you want to know the surrogate PK Value?
> Must they be sequential?
>
> Neven
> > pretend IDENTITY columns dont exist, because they don't do what I
> > want, which is to create non-rollback numbers like IB Generators or
> > Oracle Sequences.
> >
> > No matter how much rowlocking, updatelocks etc.. if a rollback
> happens
> > then the Sequence numbers can get scrambled depending on transaction
> > order.
> >
> > I've read under SQL 2008 (im using '05) that you can create a remote
> > stored procedure using a loobback connection, where the remote call
> > will then commit the increments outside of the local transaction
> > running? Can anyone confirm this?
> >
> > My only other thought is to write a CLR (when I learn how) that uses
> > critical sections and a seperate database connection to access
> and do
> > all the commits to the Sequence generation table..
> >
> > any thoughts comments appreciated.
> >
> ------------------------------------------------------------------------
> >
> > _______________________________________________
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi@delphi.org.nz <mailto:delphi@delphi.org.nz>
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to [EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]> with Subject: unsubscribe
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi@delphi.org.nz <mailto:delphi@delphi.org.nz>
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to [EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]> with Subject: unsubscribe
>
>
>
>
> --
> Kyley Harris
> Harris Software
> +64-21-671-821
> ------------------------------------------------------------------------
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi@delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
