[DUG] MSSQL Question

[Neven MacEwan]

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 <neven at mwk.co.nz 
> <mailto:neven at mwk.co.nz>> 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 at delphi.org.nz <mailto:delphi at delphi.org.nz>
>     > Admin: http://delphi.org.nz/mailman/listinfo/delphi
>     > Unsubscribe: send an email to delphi-request at delphi.org.nz
>     <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
>     _______________________________________________
>     NZ Borland Developers Group - Delphi mailing list
>     Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
>     Admin: http://delphi.org.nz/mailman/listinfo/delphi
>     Unsubscribe: send an email to delphi-request at delphi.org.nz
>     <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
>
>
>
> -- 
> Kyley Harris
> Harris Software
> +64-21-671-821
> ------------------------------------------------------------------------
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject: unsubscribe