[DUG] MSSQL Question
Kyley Harris
kyleyharris at gmail.com
Mon Dec 1 15:42:00 NZDT 2008
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 <myles at ams.co.nz> 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:* delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz]
> *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 <neven at mwk.co.nz> 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 <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
>
> _______________________________________________
> 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
> 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 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/59964be4/attachment-0001.html
More information about the Delphi
mailing list