Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 16:29:15 +0100

You find my details below. I don't know why you see this behavior after
installing RS specifically, while I see it without such install. But I know
that the behavior of SQL Server to not block on data which haven't been
actually changed has been discussed in the MVP group, so it is definitely by
design.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
 Dec 17 2002 14:22:05
 Copyright (c) 1988-2003 Microsoft Corporation
 Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1) 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ian Posner" <ian.posner.removethisantispamclause@tdsecurities.com> wrote in
message news:O8EMMvDCEHA.3784@TK2MSFTNGP10.phx.gbl...
> What version are you running? The fact is that the installation of
Reporting
> Services CHANGES THE BEHAVIOUR OF THE SAME TEST on the same version of SQL
> Server when run pre- and post- Reporting Services install.
>
> It's like the Reporting Services team removed the read committed isolation
> level to make their product run with no locks being honoured.
>
> I've repeated the same test twice, reinstalling SQL Server 2000 sp3a and
the
> results can be recreated.
>
> Ian
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:%23%23KryYBCEHA.2556@TK2MSFTNGP12.phx.gbl...
> > Ian,
> >
> > I have not installed reporting services on my machine, and my connection
2
> > does not get blocked either. I agree that this is a bit surprising, but
> > there are some optimizations in SQL Server where SQL Server will know
that
> > data hasn't been modified and hence doesn't enforce blocking.
> >
> > One could question whether this is expected behavior, but it is a
> deliberate
> > optimization in SQL Server. If you wish to call it a bug, you can open a
> > case with MS and see if they will consider changing the behavior (remove
> the
> > optimization). To be honest, I think that your chances are slim,
though...
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Ian Posner" <ian.posner.removethisantispamclause@tdsecurities.com>
wrote
> in
> > message news:%23RA4Jo3BEHA.3024@tk2msftngp13.phx.gbl...
> > > I've installed Reporting Services Evaluation to a SQL Server 2000
> (version
> > > 8.00.760). After installation, I open two connections to the Northwind
> > > database using 2 instances of OSQL:
> > >
> > > On connection 1:
> > >
> > > 1> BEGIN TRAN
> > > 2> UPDATE Customers SET companyname = companyname
> > > 3>GO
> > >
> > > On connection 2:
> > > 1>SET TRANSACTION ISOLATION LEVEL READ COMMITTED <The default: this is
> > > optional and can be omitted>
> > > 2>SELECT * FROM Customers
> > > 3>GO
> > >
> > > The CORRECT behaviour should be for Connection 2 to pause outputting
the
> > > query results until the transaction opened on Connection 1 is either
> > > committed or rolled back, or for the query to timeout (if a query
> timeout
> > > has been set).
> > >
> > > However AFTER INSTALLING THE REPORTING SERVICES, the query outputs
> > > immediately as one would expect from the READ UNCOMMITTED isolation
> level,
> > > even with the READ COMMITTED isolation level explicitly defined for
the
> > > connection. Issuing an sp_lock shows the Customers table has the
> required
> > > exclusive locks -- it's just that the secondary connection is ignoring
> > them!
> > >
> > > This is not the same behaviour as occurs on the very same SQL Server
> > before
> > > Reporting Services are installed.
> > >
> > > Is this by design or by accident? If by design, it's pretty poor as
the
> > > implications for the consistency of data obtained from other databases
> on
> > > the same server is compromised. Is it possible to reconfigure the
server
> > > back to the default isolation level? Surely the transaction isolation
> > level
> > > used by reporting services should be defined by the user?
> > >
> > > Would someone from Microsoft please comment?
> > >
> > > Thanks
> > >
> > > Ian P
> > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!
    ... The fact is that the installation of Reporting ... Server when run pre- and post- Reporting Services install. ... It's like the Reporting Services team removed the read committed isolation ... I've repeated the same test twice, reinstalling SQL Server 2000 sp3a and the ...
    (microsoft.public.sqlserver.server)
  • Reporting services installation
    ... I am experiencing this error while installing Reporting Services on a Win ... SQL Server 2000 has already SP3a installed and it's working successfully. ... So, after executing this command, the setup starts and tells me that VS 2003 ...
    (microsoft.public.sqlserver.server)
  • Re: Installing SQL Server Reporting Services
    ... SQL Server is at SP3a and default instance as well as default IIS. ... I'm assuming we're installing Reporting Services to ...
    (microsoft.public.windows.server.sbs)
  • Installing VS .NET 2003
    ... On my clinet machine i am installing VS.NET 2003 (framework 1.1). ... The SQL server is on a different machine with SQL Reporting Services installed on it. ...
    (microsoft.public.vsnet.general)
  • Re: SBS Monitoring reinstall fails
    ... -- SharePoint instance on SQL Server 2000 SP4 ... Microsoft Data Engine. ... Rerun Setup, and retry installing ...
    (microsoft.public.windows.server.sbs)