RE: Guarantee Transaction Isolation

From: Al (Al_at_discussions.microsoft.com)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 12:20:01 -0700

I was partly wrong.
What you have will work with SERIALIZABLE isolation, but will not with the default READ COMMITED isolation.

So, you can use either TABLOCKX or HOLDLOCK locking hints. The HOLDLOCK locking hint makes the statment run as if it were at SERIALIZABLE isolation.

"Al" wrote:

> I would guess no.
>
> The first thing to realise is that although you have done a SELECT to get the highest key value, this will NOT stop an INSERT into the Transaction table. I think this would be the case even at SERIALIZABLE isolation. Although this is the highest isolation level, the range lock on the index would NOT prevent an INSERT. However, you can test this with Query Analyzer.
>
> I would use a TABLOCK lock hint in the select, and ensure that I kept my transaction as short as possible.
>
> Al
>
> "Stefan Berglund" wrote:
>
> > Consider the following table and stored procedure:
> >
> > CREATE TABLE ShowTime..Transactions (
> > ShowID SMALLINT NOT NULL,
> > TransactionID INTEGER IDENTITY(1,1) NOT NULL,
> > ShowNumber SMALLINT NOT NULL,
> > ToShowNumber SMALLINT NOT NULL,
> > ClassID SMALLINT NOT NULL,
> > Type TINYINT NOT NULL,
> > Charge DECIMAL(19,4) NOT NULL,
> > Param VARCHAR(50) NOT NULL DEFAULT '',
> > TransactionTime SMALLDATETIME NOT NULL DEFAULT GETDATE(),
> > Scratched BIT NOT NULL DEFAULT 0)
> >
> >
> > CREATE PROCEDURE _ResetTransactionID
> > @ShowID SMALLINT AS
> >
> > SET NOCOUNT ON
> > DECLARE @Seed INTEGER SET @Seed = 0
> > BEGIN TRAN
> > SELECT @Seed=MAX(TransactionID) FROM Transactions WHERE ShowID=@ShowID
> > DBCC CHECKIDENT ('Transactions', RESEED, @Seed)
> > COMMIT TRAN
> > GO
> >
> > Does wrapping this operation in a transaction guarantee in a
> > multi user environment that no other operation can insert into
> > the table (thereby altering the TransactionID) between the two
> > statements inside the transaction? Or do I need additional
> > precautions?
> >
> >
> > ---
> > Stefan Berglund
> >

Relevant Pages

  • Re: Question about SERIALIZE transaction isolation
    ... serializable isolation it should never be found even by a future query ... in the same transaction. ... BEGIN TXN B ... isolation level or fix the SERIALIZABLE isolation level. ...
    (comp.databases.theory)
  • Re: Question about SERIALIZE transaction isolation
    ... database engine. ... This one is about the behaviour of a read-only transaction ... serializable isolation it should never be found even by a future query ... to create a realistic illusion that you have the database for your ...
    (comp.databases.theory)
  • Re: MySQL ResultSet - count rows?
    ... SELECT COUNTapproach will only be guaranteed to work if you execute ... both SELECTs in a single transaction AND use SERIALIZABLE isolation ...
    (comp.lang.java.databases)
  • Re: Question about SERIALIZE transaction isolation
    ... database engine. ... This one is about the behaviour of a read-only transaction ... serializable isolation it should never be found even by a future query ... to create a realistic illusion that you have the database for your ...
    (comp.databases.theory)
  • Re: Guarantee Transaction Isolation
    ... >What you have will work with SERIALIZABLE isolation, but will not with the default READ COMMITED isolation. ... you can use either TABLOCKX or HOLDLOCK locking hints. ... The HOLDLOCK locking hint makes the statment run as if it were at SERIALIZABLE isolation. ...
    (microsoft.public.sqlserver.programming)