Hi Neven,<div><br></div><div>Currenlty I am using a stored proc that accesses a table in the form of </div><div><br></div><div>(</div><div>SequenceName varchar(100);</div><div>KeyValue Int</div><div>)</div><div><br></div><div>
I'm using update locks etc..</div><div><br></div><div>my worry is that </div>Transaction A asks for "ClientNO" 1-3 keys<br><div>Transaction B asks for "ClientNO" 4-6 keys</div><div><br></div><div>A Rolls Back</div>
<div><br></div><div>Transaction C or d or e in the future may get 4-6 taken by B again because of A's Rollback.</div><div><br></div><div>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..</div>
<div><br></div><div>in interbase and Oracle.. its very easy..</div><div><br></div><div>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.<br>
<br><div class="gmail_quote">On Mon, Dec 1, 2008 at 11:41 AM, 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>
Why not just use a stored proc as a 'generator' or a high/low sequence,<br>
I take it that you want to know the surrogate PK Value?<br>
Must they be sequential?<br>
<br>
Neven<br>
<div><div></div><div class="Wj3C7c">> 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 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 and do<br>
> all the commits to the Sequence generation table..<br>
><br>
> any thoughts comments appreciated.<br>
</div></div>> ------------------------------------------------------------------------<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>
</blockquote></div><br><br clear="all"><br>-- <br>Kyley Harris<br>Harris Software<br>+64-21-671-821<br>
</div>