Thanks Neven, <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.. </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'm still not familiar with the in'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't rollback. </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"><<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>></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 '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<br>
<div class="Ih2E3d"><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">neven@mwk.co.nz</a><br>
</div><div><div></div><div class="Wj3C7c">> <mailto:<a href="mailto:neven@mwk.co.nz">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<br>
</div></div>> > Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> <mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><br>
<div class="Ih2E3d">> > 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><br>
</div>> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>> with Subject: unsubscribe<br>
<div class="Ih2E3d">><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list<br>
</div>> Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> <mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><br>
<div class="Ih2E3d">> 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><br>
</div>> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>> with Subject: unsubscribe<br>
<div><div></div><div class="Wj3C7c">><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">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>
<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>