Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!
From: Ian Posner (ian.posner.removethisantispamclause_at_tdsecurities.com)
Date: 03/12/04
- Next message: Jaxon: "Re: Page Splits \ sec"
- Previous message: Jacco Schalkwijk: "Re: Query problem"
- In reply to: Tibor Karaszi: "Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!"
- Next in thread: Tibor Karaszi: "Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!"
- Reply: Tibor Karaszi: "Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Mar 2004 14:10:31 -0000
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
> >
> >
> >
>
>
- Next message: Jaxon: "Re: Page Splits \ sec"
- Previous message: Jacco Schalkwijk: "Re: Query problem"
- In reply to: Tibor Karaszi: "Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!"
- Next in thread: Tibor Karaszi: "Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!"
- Reply: Tibor Karaszi: "Re: Reporting Services DISABLES DEFAULT Transaction Isolation Level!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
