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