Thanks Neven,&nbsp;<div><br></div><div><div>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..&nbsp;</div>
<div><br></div><div>Thanks.</div><div><br></div></div><div><br></div><div>Now admittedly, I am just trying to be sure, because I&#39;m still not familiar with the in&#39;s and outs of MSSQL and how it locks.</div><div><br>
</div><div>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&#39;t rollback.&nbsp;</div>
<div><br></div><div><br></div><div><br><div class="gmail_quote">On Mon, Dec 1, 2008 at 12:46 PM, Neven MacEwan <span dir="ltr">&lt;<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Kyley<br>
<br>
Firstly if you put the SequenceNo stored proc &#39;in&#39; the transaction,<br>
don&#39;t you already run the risk of deadlock? (as you<br>
can&#39;t be sure of the updates the client procedures table updates and order)<br>
<br>
So to avoid this you could &#39;pre&#39; fetch the keys in a separate trans, or<br>
post fetch them, depending on where the biggest &#39;risk&#39; 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 &#39;lose a sequence&#39;<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>
 &nbsp;repeat {<br>
 &nbsp; &nbsp;Begin trans, GenerateKeys(x), commit/rollback<br>
 &nbsp; &nbsp;} until commit<br>
apply keys to statements<br>
<br>
If you set TRANSACTION ISOLATION to SERIALIZABLE in &nbsp;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<br>
<div class="Ih2E3d"><br>
&gt; Hi Neven,<br>
&gt;<br>
&gt; Currenlty I am using a stored proc that accesses a table in the form of<br>
&gt;<br>
&gt; (<br>
&gt; SequenceName varchar(100);<br>
&gt; KeyValue Int<br>
&gt; )<br>
&gt;<br>
&gt; I&#39;m using update locks etc..<br>
&gt;<br>
&gt; my worry is that<br>
&gt; Transaction A asks for &quot;ClientNO&quot; 1-3 keys<br>
&gt; Transaction B asks for &quot;ClientNO&quot; 4-6 keys<br>
&gt;<br>
&gt; A Rolls Back<br>
&gt;<br>
&gt; Transaction C or d or e in the future may get 4-6 taken by B again<br>
&gt; because of A&#39;s Rollback.<br>
&gt;<br>
&gt; The keys are not used for Primary or Relational anything. I use<br>
&gt; UniqueIdentifier for that.. The sequences do not need to be perfect,<br>
&gt; gaps are allowable.. these sequences are for Data Values used by the<br>
&gt; software users.. ie Batch No, Client No, Debtor No, Statement No,<br>
&gt; Invoice No.. etc etc. and these numbers are then used for quick<br>
&gt; referencing of information..<br>
&gt;<br>
&gt; in interbase and Oracle.. its very easy..<br>
&gt;<br>
&gt; have to pop out for an hour.. Thanks if anyone has any ideas for<br>
&gt; making sure I don&#39;t get lock contention, or reissuing of the same<br>
&gt; number twice.<br>
&gt;<br>
&gt; On Mon, Dec 1, 2008 at 11:41 AM, Neven MacEwan &lt;<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a><br>
</div><div><div></div><div class="Wj3C7c">&gt; &lt;mailto:<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>&gt;&gt; wrote:<br>
&gt;<br>
&gt; &nbsp; &nbsp; Kyley<br>
&gt;<br>
&gt; &nbsp; &nbsp; Why not just use a stored proc as a &#39;generator&#39; or a high/low<br>
&gt; &nbsp; &nbsp; sequence,<br>
&gt; &nbsp; &nbsp; I take it that you want to know the surrogate PK Value?<br>
&gt; &nbsp; &nbsp; Must they be sequential?<br>
&gt;<br>
&gt; &nbsp; &nbsp; Neven<br>
&gt; &nbsp; &nbsp; &gt; pretend IDENTITY columns dont exist, because they don&#39;t do what I<br>
&gt; &nbsp; &nbsp; &gt; want, which is to create non-rollback numbers like IB Generators or<br>
&gt; &nbsp; &nbsp; &gt; Oracle Sequences.<br>
&gt; &nbsp; &nbsp; &gt;<br>
&gt; &nbsp; &nbsp; &gt; No matter how much rowlocking, updatelocks etc.. if a rollback<br>
&gt; &nbsp; &nbsp; happens<br>
&gt; &nbsp; &nbsp; &gt; then the Sequence numbers can get scrambled depending on transaction<br>
&gt; &nbsp; &nbsp; &gt; order.<br>
&gt; &nbsp; &nbsp; &gt;<br>
&gt; &nbsp; &nbsp; &gt; I&#39;ve read under SQL 2008 (im using &#39;05) that you can create a remote<br>
&gt; &nbsp; &nbsp; &gt; stored procedure using a loobback connection, where the remote call<br>
&gt; &nbsp; &nbsp; &gt; will then commit the increments outside of the local transaction<br>
&gt; &nbsp; &nbsp; &gt; running? Can anyone confirm this?<br>
&gt; &nbsp; &nbsp; &gt;<br>
&gt; &nbsp; &nbsp; &gt; My only other thought is to write a CLR (when I learn how) that uses<br>
&gt; &nbsp; &nbsp; &gt; critical sections and a seperate database connection to access<br>
&gt; &nbsp; &nbsp; and do<br>
&gt; &nbsp; &nbsp; &gt; all the commits to the Sequence generation table..<br>
&gt; &nbsp; &nbsp; &gt;<br>
&gt; &nbsp; &nbsp; &gt; any thoughts comments appreciated.<br>
&gt; &nbsp; &nbsp; &gt;<br>
&gt; &nbsp; &nbsp; ------------------------------------------------------------------------<br>
&gt; &nbsp; &nbsp; &gt;<br>
&gt; &nbsp; &nbsp; &gt; _______________________________________________<br>
&gt; &nbsp; &nbsp; &gt; NZ Borland Developers Group - Delphi mailing list<br>
</div></div>&gt; &nbsp; &nbsp; &gt; Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> &lt;mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>&gt;<br>
<div class="Ih2E3d">&gt; &nbsp; &nbsp; &gt; Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
&gt; &nbsp; &nbsp; &gt; Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><br>
</div>&gt; &nbsp; &nbsp; &lt;mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>&gt; with Subject: unsubscribe<br>
<div class="Ih2E3d">&gt;<br>
&gt; &nbsp; &nbsp; _______________________________________________<br>
&gt; &nbsp; &nbsp; NZ Borland Developers Group - Delphi mailing list<br>
</div>&gt; &nbsp; &nbsp; Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> &lt;mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>&gt;<br>
<div class="Ih2E3d">&gt; &nbsp; &nbsp; Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
&gt; &nbsp; &nbsp; Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><br>
</div>&gt; &nbsp; &nbsp; &lt;mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>&gt; with Subject: unsubscribe<br>
<div><div></div><div class="Wj3C7c">&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; --<br>
&gt; Kyley Harris<br>
&gt; Harris Software<br>
&gt; +64-21-671-821<br>
&gt; ------------------------------------------------------------------------<br>
&gt;<br>
&gt; _______________________________________________<br>
&gt; NZ Borland Developers Group - Delphi mailing list<br>
&gt; Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a><br>
&gt; Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
&gt; Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">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">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>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Kyley Harris<br>Harris Software<br>+64-21-671-821<br>
</div>