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. <div><br></div><div>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.. <br>
<br><div class="gmail_quote">On Mon, Dec 1, 2008 at 3:36 PM, Myles Penlington <span dir="ltr"><<a href="mailto:myles@ams.co.nz">myles@ams.co.nz</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-NZ" link="blue" vlink="purple">
<div>
<p><span style="font-size:11.0pt;color:#1F497D">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.</span></p>
<p><span style="font-size:11.0pt;color:#1F497D"> </span></p>
<p><span style="font-size:11.0pt;color:#1F497D">The nested transactions in SQL are basically present to handle nested
stored procedure calls that deal with transactions.</span></p>
<p><span style="font-size:11.0pt;color:#1F497D"> </span></p>
<p><span style="font-size:11.0pt;color:#1F497D">Myles.</span></p>
<p><span style="font-size:11.0pt;color:#1F497D"> </span></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p><b><span lang="EN-US" style="font-size:10.0pt">From:</span></b><span lang="EN-US" style="font-size:10.0pt"> <a href="mailto:delphi-bounces@delphi.org.nz" target="_blank">delphi-bounces@delphi.org.nz</a>
[mailto:<a href="mailto:delphi-bounces@delphi.org.nz" target="_blank">delphi-bounces@delphi.org.nz</a>] <b>On Behalf Of </b>Kyley Harris<br>
<b>Sent:</b> Monday, 1 December 2008 3:17 p.m.<br>
<b>To:</b> NZ Borland Developers Group - Delphi List<div class="Ih2E3d"><br>
<b>Subject:</b> Re: [DUG] MSSQL Question</div></span></p>
</div>
<p> </p>
<p>Thanks Neven, </p><div><div></div><div class="Wj3C7c">
<div>
<p> </p>
</div>
<div>
<div>
<p>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.. </p>
</div>
<div>
<p> </p>
</div>
<div>
<p>Thanks.</p>
</div>
<div>
<p> </p>
</div>
</div>
<div>
<p> </p>
</div>
<div>
<p>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.</p>
</div>
<div>
<p> </p>
</div>
<div>
<p>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. </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
<div>
<p>On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan <<a href="mailto:neven@mwk.co.nz" target="_blank">neven@mwk.co.nz</a>> wrote:</p>
<p>Kyley<br>
<br>
Firstly if you put the SequenceNo stored proc 'in' the transaction,<br>
don't you already run the risk of deadlock? (as you<br>
can't be sure of the updates the client procedures table updates and order)<br>
<br>
So to avoid this you could 'pre' fetch the keys in a separate trans, or<br>
post fetch them, depending on where the biggest 'risk' of rollback is<br>
<br>
ie for statements (pre fetch)<br>
<br>
x = no of statements to produce<br>
repeat (Begin trans, GenerateKeys(x), commit/rollback) until commit<br>
Begin trans, Generate statements using keys, commits/rollback<br>
<br>
Worst case you will 'lose a sequence'<br>
<br>
but since there is almost no chance of a fail on exec FetchKeys then I would<br>
<br>
if (x = Begin trans, Generate statements, null keys, commits == commit) {<br>
repeat {<br>
Begin trans, GenerateKeys(x), commit/rollback<br>
} until commit<br>
apply keys to statements<br>
<br>
If you set TRANSACTION ISOLATION to SERIALIZABLE in GenerateKeys you<br>
will have sole access to the table and as there is only<br>
one table involved I cannot see a huge overhead<br>
<br>
HTH<br>
Neven</p>
<div>
<p><br>
> Hi Neven,<br>
><br>
> Currenlty I am using a stored proc that accesses a table in the form of<br>
><br>
> (<br>
> SequenceName varchar(100);<br>
> KeyValue Int<br>
> )<br>
><br>
> I'm using update locks etc..<br>
><br>
> my worry is that<br>
> Transaction A asks for "ClientNO" 1-3 keys<br>
> Transaction B asks for "ClientNO" 4-6 keys<br>
><br>
> A Rolls Back<br>
><br>
> Transaction C or d or e in the future may get 4-6 taken by B again<br>
> because of A's Rollback.<br>
><br>
> The keys are not used for Primary or Relational anything. I use<br>
> UniqueIdentifier for that.. The sequences do not need to be perfect,<br>
> gaps are allowable.. these sequences are for Data Values used by the<br>
> software users.. ie Batch No, Client No, Debtor No, Statement No,<br>
> Invoice No.. etc etc. and these numbers are then used for quick<br>
> referencing of information..<br>
><br>
> in interbase and Oracle.. its very easy..<br>
><br>
> have to pop out for an hour.. Thanks if anyone has any ideas for<br>
> making sure I don't get lock contention, or reissuing of the same<br>
> number twice.<br>
><br>
> On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan <<a href="mailto:neven@mwk.co.nz" target="_blank">neven@mwk.co.nz</a></p>
</div>
<div>
<div>
<p>> <mailto:<a href="mailto:neven@mwk.co.nz" target="_blank">neven@mwk.co.nz</a>>>
wrote:<br>
><br>
> Kyley<br>
><br>
> Why not just use a stored proc as a 'generator' or a
high/low<br>
> sequence,<br>
> I take it that you want to know the surrogate PK Value?<br>
> Must they be sequential?<br>
><br>
> Neven<br>
> > pretend IDENTITY columns dont exist, because they don't
do what I<br>
> > want, which is to create non-rollback numbers like IB
Generators or<br>
> > Oracle Sequences.<br>
> ><br>
> > No matter how much rowlocking, updatelocks etc.. if a
rollback<br>
> happens<br>
> > then the Sequence numbers can get scrambled depending
on transaction<br>
> > order.<br>
> ><br>
> > I've read under SQL 2008 (im using '05) that you can
create a remote<br>
> > stored procedure using a loobback connection, where the
remote call<br>
> > will then commit the increments outside of the local
transaction<br>
> > running? Can anyone confirm this?<br>
> ><br>
> > My only other thought is to write a CLR (when I learn
how) that uses<br>
> > critical sections and a seperate database connection to
access<br>
> and do<br>
> > all the commits to the Sequence generation table..<br>
> ><br>
> > any thoughts comments appreciated.<br>
> ><br>
>
------------------------------------------------------------------------<br>
> ><br>
> > _______________________________________________<br>
> > NZ Borland Developers Group - Delphi mailing list</p>
</div>
</div>
<p>> > Post: <a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a> <mailto:<a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a>></p>
<div>
<p>> > Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> > Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a></p>
</div>
<p>> <mailto:<a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a>>
with Subject: unsubscribe</p>
<div>
<p>><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list</p>
</div>
<p>> Post: <a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a> <mailto:<a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a>></p>
<div>
<p>> Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a></p>
</div>
<p>> <mailto:<a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a>>
with Subject: unsubscribe</p>
<div>
<div>
<p>><br>
><br>
><br>
><br>
> --<br>
> Kyley Harris<br>
> Harris Software<br>
> +64-21-671-821<br>
> ------------------------------------------------------------------------<br>
><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list<br>
> Post: <a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a><br>
> Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a>
with Subject: unsubscribe<br>
<br>
_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a>
with Subject: unsubscribe</p>
</div>
</div>
</div>
<p><br>
<br clear="all">
<br>
-- <br>
Kyley Harris<br>
Harris Software<br>
+64-21-671-821</p>
</div>
</div></div></div>
<font face="Verdana" size="1">Attention:<br>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.<br></font>
</div>
<br>_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a> with Subject: unsubscribe<br></blockquote></div><br><br clear="all"><br>-- <br>Kyley Harris<br>Harris Software<br>
+64-21-671-821<br>
</div>