Transaction Isolation keeps changing (my mind that is). Trying to work out what is best. I was trying snapshot, so that it mimics interbase. I may well just leave it on read/committed. <div><br></div><div>This is one of those subject where I use Prayer as a means of making it work :)</div>
<div><br></div><div>I also realized that the insert was the micro risk.. didn't know serialized would protect it. I'll try that and see.</div><div><br></div><div>Thanks for the advise<br><br><div class="gmail_quote">
On Tue, Dec 2, 2008 at 8:08 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>
Looks pretty robust (explicit locks), I've never used the output<br>
pseudotable structure (interesting, learn something new),<br>
If you set the TRANSACTION ISOLATION to SERIALIZED could you drop the<br>
explicit locks? and that would also mean that your insert was<br>
protected? There may be a SLIM chance, that if you have a very low TRAN<br>
ISO and you called GENGEN almost simultaneously WITH the same<br>
unititialized @TableName<br>
that the @@Rowcount for the second call would be incorrect (as there<br>
would be a race to insert)<br>
<br>
What is your TRANSACTION ISOLATION?<br>
<br>
Neven<br>
<div><div></div><div class="Wj3C7c"><br>
<br>
<br>
> Where I am mainly using Sequences, not tied to a specific field is<br>
> where many entities in our application<br>
> all use the same number sequence to be attached a unique reference,<br>
> There is a single point of entry to type in that ref# which will open<br>
> anything relevant, ie an invoice, a statement a client, a debtor etc.<br>
> when talking to customers on the phone, it lets the client access<br>
> relevant info very quickly. I don't tend to use a database to its full<br>
> capacity as a database as oracle and MSSql etc would let you.. its a<br>
> hybrid between the middletier OO layer, and the database.<br>
><br>
> The actual stored proc I've come up with is as follows... I'm just<br>
> waiting to find the flaws.. :D i think 99.999% it will work great..<br>
> all my primary keys are GUIDs.<br>
><br>
> CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int<br>
><br>
> AS<br>
><br>
> SET NOCOUNT ON<br>
> DECLARE @AVALUE Int<br>
> BEGIN TRAN<br>
><br>
> DECLARE @Res TABLE ( ID int )<br>
><br>
> BEGIN<br>
> UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK)<br>
> SET KEYVALUE = @AVALUE+@ACOUNT<br>
> OUTPUT deleted.KEYVALUE INTO @RES(ID)<br>
> WHERE KEYNAME = @TableName<br>
> if @@ROWCOUNT = 0<br>
> begin<br>
> SET @AVALUE = 1<br>
> INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES<br>
> (@TableName,@ACount+1);<br>
> end else<br>
> begin<br>
> SELECT @AVALUE=ID from @Res<br>
> end<br>
> END<br>
><br>
> COMMIT TRAN<br>
> RETURN @AVALUE<br>
><br>
><br>
><br>
> On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan <<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a><br>
</div></div><div class="Ih2E3d">> <mailto:<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>>> wrote:<br>
><br>
> Kyley<br>
><br>
> my experience is with postgreqsl, it has sequences (I assume from its<br>
> oracleness), which get tied to a column via the serial type.<br>
><br>
> I'm puzzled as to where you would use a Sequence/Generator in a non-pk<br>
> situation, unless you<br>
> are talking about your 'documentno' column, which as shown can be done<br>
> via a stored proc<br>
><br>
> I wonder is mssql eventually will allow functions to update tables (or<br>
> code a primative sequence generation)<br>
> which would allow this<br>
><br>
> Neven<br>
><br>
> > The main difference is that a Generator is more like an Oracle<br>
> Sequence,<br>
> > It is table independant, which allows you to use the same sequence<br>
> > accross more than one table,<br>
> > and you can also have more than one per table. I find the<br>
> identity is<br>
> > only useful if it is to be a primary key, not useful as a data<br>
> element.<br>
> ><br>
> > On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan <<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a><br>
> <mailto:<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> <mailto:<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>>>> wrote:<br>
> ><br>
> > John<br>
> ><br>
> > Really interbase generators are more akin to the mssql<br>
> identity column<br>
> > type, the problem with mssql identity columns WAS<br>
> > with the @@Identity variable which was per connection (and hence<br>
> > triggers that inserted other identity columns not return the<br>
> identity<br>
> > you would expect), this has been 'fixed' with the<br>
> > SCOPE_IDENTITY() function<br>
> ><br>
> > Stangely identity columns and generators behave the same<br>
> way, ie they<br>
> > are both not rolled back by a trans<br>
> ><br>
> > PostgeSQL, uses a combo of both a column type of 'serial',<br>
> which auto<br>
> > generates a 'sequence'<br>
> ><br>
> > Of course I could be spouting BS.<br>
> ><br>
> > Neven<br>
> > //<br>
> > > Whats others opinion on the merits of trggers etc as discussed<br>
> > here vs the<br>
> > > Firebird/Interbase method of using generators?<br>
> > ><br>
> > > I have often wondered if this is an area where<br>
> > Firebird/Interbase has a less<br>
> > > convenient but more transparent mechanism - where you have<br>
> to fire a<br>
> > > generator to get a new key value before you start putting data<br>
> > in a new<br>
> > > record.<br>
> > ><br>
> > > And the generator never rolls back even if the transaction<br>
> does<br>
> > - avoiding<br>
> > > the lock problem. Mostly in<br>
> > > my experience this is fine, except some times when the ID<br>
> > numbers (eg batch<br>
> > > numbers) are supposed to be strictly sequential and then you<br>
> > have to get the<br>
> > > next value from a SQL query, and make sure no-one else is<br>
> > running another of<br>
> > > the same batch at the same time. (In those situations the<br>
> main<br>
> > thing that<br>
> > > protects the database is that only one staff member ever runs<br>
> > this batch<br>
> > > operation - I bet thats how a lot of databases run in<br>
> practice)<br>
> > ><br>
> > ><br>
> > > John<br>
> > ><br>
> > > _______________________________________________<br>
> > > NZ Borland Developers Group - Delphi mailing list<br>
> > > 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></div>> <mailto:<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>
> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>><br>
> > <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><br>
> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>>> with Subject: unsubscribe<br>
> > ><br>
> > ><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> <mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><br>
</div>> <mailto:<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>
> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>><br>
> > <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><br>
</div><div><div></div><div class="Wj3C7c">> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>>> with Subject: unsubscribe<br>
> ><br>
> ><br>
> ><br>
> ><br>
> > --<br>
> > Kyley Harris<br>
> > Harris Software<br>
> > +64-21-671-821<br>
> ><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> <mailto:<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><br>
> <mailto:<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> <mailto:<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><br>
> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>> with Subject: unsubscribe<br>
><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>