[DUG] MSSQL Question
Neven MacEwan
neven at mwk.co.nz
Tue Dec 2 08:08:41 NZDT 2008
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 = @AVALUE+ at ACOUNT
> OUTPUT deleted.KEYVALUE INTO @RES(ID)
> WHERE KEYNAME = @TableName
> if @@ROWCOUNT = 0
> begin
> SET @AVALUE = 1
> INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES
> (@TableName, at 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 <neven at mwk.co.nz
> <mailto:neven at mwk.co.nz>> 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 <neven at mwk.co.nz
> <mailto:neven at mwk.co.nz>
> > <mailto:neven at mwk.co.nz <mailto:neven at mwk.co.nz>>> 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 at delphi.org.nz <mailto:delphi at delphi.org.nz>
> <mailto:delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>>
> > > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > > Unsubscribe: send an email to delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz>
> > <mailto:delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz>> with Subject: unsubscribe
> > >
> > >
> > >
> >
> > _______________________________________________
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> <mailto:delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>>
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz>
> > <mailto:delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz>> with Subject: unsubscribe
> >
> >
> >
> >
> > --
> > Kyley Harris
> > Harris Software
> > +64-21-671-821
> >
> ------------------------------------------------------------------------
> >
> > _______________________________________________
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz
> <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
>
>
>
> --
> Kyley Harris
> Harris Software
> +64-21-671-821
> ------------------------------------------------------------------------
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject: unsubscribe
More information about the Delphi
mailing list