[DUG] MSSQL Question
Kyley Harris
kyleyharris at gmail.com
Mon Dec 1 15:17:09 NZDT 2008
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081201/18c3f365/attachment-0001.html
More information about the Delphi
mailing list