[DUG] MSSQL Question
Kyley Harris
kyleyharris at gmail.com
Tue Dec 2 10:40:13 NZDT 2008
I have a feeling one of my main issues here is isolation level. I have
tried to configure a Generic connection setup for running in my middle
layer. Good for most, but in some cases possibly going to cause issues. I
may need to allow for different server tasks to decide the correct isolation
level for their task.. ie readonly isolation, update isolation, or snapshot
if running a report.
All the work is done by the middlelayer.. most of the work is ReadOnly, and
then when committing data its all done in object and quickly thrown into the
database, so typically as I said before, and update transaction is 100ms or
less.. the longest transactions are the reports, our longest report execute
time so far is about 6-8 seconds and thats all read only transaction.. I
thought Snapshot isolation is best for reporting with concurrent data.. Hmm
in interbase it versions off data without locking other transactions.. does
MSSQL just block?
This is why I like StarTrek.. "Computer.. make it go!"
On Tue, Dec 2, 2008 at 10:29 AM, Kyley Harris <kyleyharris at gmail.com> wrote:
> 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
>
--
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/e367b92a/attachment.html
More information about the Delphi
mailing list