[DUG] MSSQL Question
Kyley Harris
kyleyharris at gmail.com
Tue Dec 2 10:29:59 NZDT 2008
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 <neven at mwk.co.nz> 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 = @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
>
> _______________________________________________
> 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
>
--
Kyley Harris
Harris Software
+64-21-671-821
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081202/bff6a579/attachment-0001.html
More information about the Delphi
mailing list