begin trans A begin trans B increment key value commit B
-- don't care about trans A here it can be commited or roll backed Edward Koryagin --- On Mon, 1/12/08, Kyley Harris <[EMAIL PROTECTED]> wrote: > From: Kyley Harris <[EMAIL PROTECTED]> > Subject: Re: [DUG] MSSQL Question > To: "NZ Borland Developers Group - Delphi List" <delphi@delphi.org.nz> > Received: Monday, 1 December, 2008, 11:51 AM > 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]> 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 > > > 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 > > > > > > -- > 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 Easy recipes for Christmas entertaining on Yahoo!Xtra Lifestyle- http://nz.lifestyle.yahoo.com/food-recipes _______________________________________________ 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
