[DUG] MSSQL Question
Neven MacEwan
neven at mwk.co.nz
Tue Dec 2 12:11:06 NZDT 2008
Kyley
> does MSSQL just block?
I would expect that how it behaves depends on the isolation level and
what you are doing, I could be wrong here (and I'm sure Paul will
correct me :-)
But the more severe the isolation level, the more severe the locks and
how long they persist for
From MSDN
SERIALIZABLE
Specifies the following:
* Statements cannot read data that has been modified but not yet
committed by other transactions.
* No other transactions can modify data that has been read by
the current transaction until the current transaction completes.
* Other transactions cannot insert new rows with key values that
would fall in the range of keys read by any statements in the
current transaction until the current transaction completes.
Range locks are placed in the range of key values that match the
search conditions of each statement executed in a transaction. This
blocks other transactions from updating or inserting any rows that
would qualify for any of the statements executed by the current
transaction. This means that if any of the statements in a
transaction are executed a second time, they will read the same set
of rows. The range locks are held until the transaction completes.
This is the most restrictive of the isolation levels because it
locks entire ranges of keys and holds the locks until the
transaction completes. Because concurrency is lower, use this option
only when necessary. This option has the same effect as setting
HOLDLOCK on all tables in all SELECT statements in a transaction.
so if you opened GENGEN with
BEGIN TRANSACTION
SELECT * FROM GEN_IDTABLE
you would put an exclusive lock on the table until finished
Neven
>
> 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
> <mailto: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
> <mailto: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>
> > <mailto: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>>
> > > <mailto: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>>
> > <mailto: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>>
> > > <mailto: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>>
> > <mailto: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>>
> > > <mailto: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> <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
>
>
>
>
> --
> 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