Yes, I finally worked that out. Oracle allows you to start an independant embedded transaction to specifically tailor for this type of task. I hope MSSQL catches up on some of these features. One Other thought.. if there are 2 databases sitting on the same server. Can I access a table on one db and force it to commit independantly of the database I am talking with? or do they sit in the same transaction too..
On Mon, Dec 1, 2008 at 3:36 PM, Myles Penlington <[EMAIL PROTECTED]> wrote: > With MS SQL, only the top level commit/rollback matters, so to get real > sequential numbers you would have to do this in a initial call/transaction > to the db, before you perform the rest of the updates in a second > transaction, else in case of a rollback, the allocate key update would also > be rolledback. > > > > The nested transactions in SQL are basically present to handle nested > stored procedure calls that deal with transactions. > > > > Myles. > > > > *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > *On Behalf Of *Kyley Harris > *Sent:* Monday, 1 December 2008 3:17 p.m. > *To:* NZ Borland Developers Group - Delphi List > *Subject:* Re: [DUG] MSSQL Question > > > > Thanks Neven, > > > > I think your idea of keeping the key fetching in a seperate transaction is > the most likely to prevent any locks happening. Key fetch speed is not a > huge issue. This is very easy to implement in my middle layer. My issue is > then that I have a few triggers that insert data that call on a sequence > number.. if the server code that initiated this rollsback a transaction,I > guess my worst case scenrio is simply a deadlock on a table for a second or > so.. > > > > Thanks. > > > > > > Now admittedly, I am just trying to be sure, because I'm still not familiar > with the in's and outs of MSSQL and how it locks. > > > > Typically, I have 0 rollbacks at all.. infact, rollbacks occur only as part > of software failure to protect the database. Our average transaction time is > less than 100ms, generally about 5-10ms as we pre-do everything in OO and > then commit at the end of each area in a short fast swoop in a middle tier. > This tends to eliminate lock contention, I just want to cover all the bases > and make sure the keys don't rollback. > > > > > > > > On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan <[EMAIL PROTECTED]> wrote: > > 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 > > > > > -- > Kyley Harris > Harris Software > +64-21-671-821 > Attention: > This communication is confidential and may be legally privileged. If you > are not the intended recipient, please do not use, disclose, copy or > distribute it, other than to return it to us with your confirmation that it > has been deleted from your system. > > _______________________________________________ > 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
