[DUG] MSSQL Question

[Kyley Harris]

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> 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
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>
> _______________________________________________
> 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
>



-- 
Kyley Harris
Harris Software
+64-21-671-821
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081201/9970df51/attachment.html