[DUG] MSSQL Question
Myles Penlington
myles at ams.co.nz
Mon Dec 1 15:36:04 NZDT 2008
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081201/112e5ed1/attachment.html
More information about the Delphi
mailing list