Reporting Services DISABLES DEFAULT Transaction Isolation Level!

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 PIan,
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
>
>|||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...
(version
timeout
level,
required
> them!
> before
on
> level
>|||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...
2
that
> deliberate
> the
though...
wrote
> in
> (version
the
> timeout
> level,
the
> required
the
> on
server
>|||Have you tried it with a fresh install? It may be that this change in
behaviour may be implemented as part of another product or patch which you
may have installed.
I would be interested in seeing any discussion of this behaviour. From my
experience, since SQL Server (unlike Oracle) does not have a
multi-versioning log, data sent out has to be correct at the time it is
dispatched, hence the necessity to delay output until exclusive locks are
released.
From what you are saying, I infer that you say there has been a discussion
on the outputting of results on locked but unmodified resources.
How would this be possible if some rows were modified and others not? Are
you implying that the modified but locked rows show their last values? It
doesn't make sense unless you have multi-versioning logging like in Oracle
(which SQL Server doesn't).
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tsuHbECEHA.628@.TK2MSFTNGP10.phx.gbl...
> 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...
> Reporting
SQL
isolation
> the
> in
connection
> 2
but
> that
a
(remove
> though...
> wrote
Northwind
is
> the
> the
ignoring
> the
databases
> server
isolation
>|||> Have you tried it with a fresh install? It may be that this change in
> behaviour may be implemented as part of another product or patch which you
> may have installed.
Nope, I don't have a fresh install to test on. I agree that the behaviour
change can be from something else, but it is worrying that something outside
a SQL Server patch would change this. That should not happen.

> I would be interested in seeing any discussion of this behaviour. From my
> experience, since SQL Server (unlike Oracle) does not have a
> multi-versioning log, data sent out has to be correct at the time it is
> dispatched, hence the necessity to delay output until exclusive locks are
> released.
Correct. This is what locking and blocking is all about. A side note is that
multi-versioning is planned for SQL Server 2005.

> From what you are saying, I infer that you say there has been a discussion
> on the outputting of results on locked but unmodified resources.
Yes. However, I don't recall any details about the discussion, I just
remember that we've had the discussion.

> How would this be possible if some rows were modified and others not? Are
> you implying that the modified but locked rows show their last values? It
> doesn't make sense unless you have multi-versioning logging like in Oracle
> (which SQL Server doesn't).
No, this is how it should work. Say you have 3 "pseudo-locked" rows
(modified to the same value) and 5 "properly locked" rows (modified to some
other value). Say that a where clause from another connection should return
all these 8 rows. SQL Server will scan the data/index and a pseudo-locked
row will be OK, but as soon as in hits a properly locked row, the query will
block.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ian Posner" <ian.posner.removethisantispamclause@.tdsecurities.com> wrote in
message news:%230gfw2nCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Have you tried it with a fresh install? It may be that this change in
> behaviour may be implemented as part of another product or patch which you
> may have installed.
> I would be interested in seeing any discussion of this behaviour. From my
> experience, since SQL Server (unlike Oracle) does not have a
> multi-versioning log, data sent out has to be correct at the time it is
> dispatched, hence the necessity to delay output until exclusive locks are
> released.
> From what you are saying, I infer that you say there has been a discussion
> on the outputting of results on locked but unmodified resources.
> How would this be possible if some rows were modified and others not? Are
> you implying that the modified but locked rows show their last values? It
> doesn't make sense unless you have multi-versioning logging like in Oracle
> (which SQL Server doesn't).
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23tsuHbECEHA.628@.TK2MSFTNGP10.phx.gbl...
> know
definitely
> by
wrote
> in
> SQL
> isolation
and
wrote
> connection
> but
know
open
> a
> (remove
> Northwind
this
> is
outputting
either
isolation
for
> ignoring
Server
as
> databases
> isolation
>|||Tibor -- Thanks for getting back to me.
Firstly I agree, it is very worrying that a non-service pack/ non-patch
should affect behaviour.
Secondly, my understanding is that you are saying there has been a change in
behaviour so that rows that are modified to the same value (as in my
example) do not have their locks honoured. If this is the case, then
modifying my query to "UPDATE Customers SET companyname = companyname + 'A'"
should prevent the second connection from returning results until the first
connection is committed/rolled back. Can you confirm this on the version you
are working with?
Ian
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esO3jw0CEHA.3064@.tk2msftngp13.phx.gbl...
you
> Nope, I don't have a fresh install to test on. I agree that the behaviour
> change can be from something else, but it is worrying that something
outside
> a SQL Server patch would change this. That should not happen.
>
my
are
> Correct. This is what locking and blocking is all about. A side note is
that
> multi-versioning is planned for SQL Server 2005.
>
discussion
> Yes. However, I don't recall any details about the discussion, I just
> remember that we've had the discussion.
>
Are
It
Oracle
> No, this is how it should work. Say you have 3 "pseudo-locked" rows
> (modified to the same value) and 5 "properly locked" rows (modified to
some
> other value). Say that a where clause from another connection should
return
> all these 8 rows. SQL Server will scan the data/index and a pseudo-locked
> row will be OK, but as soon as in hits a properly locked row, the query
will
> block.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ian Posner" <ian.posner.removethisantispamclause@.tdsecurities.com> wrote
in
> message news:%230gfw2nCEHA.2592@.TK2MSFTNGP12.phx.gbl...
you
my
are
discussion
Are
It
Oracle
> in
after
been
> definitely
> wrote
of
> and
> wrote
surprising,
> know
> open
<ian.posner.removethisantispamclause@.tdsecurities.com>
2000
> this
> outputting
> either
query
outputs
> isolation
> for
> Server
> as
the
>|||Ian,

> Secondly, my understanding is that you are saying there has been a change
in
> behaviour so that rows that are modified to the same value (as in my
> example) do not have their locks honoured.
I think that MS would call it an optimization. The optimization was probably
introduced in SQL2K. I tried your original code and it blocked on 7.0 sp4
but not on SQL2K sp1 or SQL2K sp3.

> this is the case, then
> modifying my query to "UPDATE Customers SET companyname = companyname +
'A'"
> should prevent the second connection from returning results until the
first
> connection is committed/rolled back. Can you confirm this on the version
you
> are working with?
Confirmed. It blocks, as expected, on SQL2K sp1 as well as sp3.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ian Posner" <ian.posner.removethisantispamclause@.tdsecurities.com> wrote in
message news:umDhf$$CEHA.3928@.TK2MSFTNGP09.phx.gbl...
> Tibor -- Thanks for getting back to me.
> Firstly I agree, it is very worrying that a non-service pack/ non-patch
> should affect behaviour.
> Secondly, my understanding is that you are saying there has been a change
in
> behaviour so that rows that are modified to the same value (as in my
> example) do not have their locks honoured. If this is the case, then
> modifying my query to "UPDATE Customers SET companyname = companyname +
'A'"
> should prevent the second connection from returning results until the
first
> connection is committed/rolled back. Can you confirm this on the version
you
> are working with?
> Ian
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:esO3jw0CEHA.3064@.tk2msftngp13.phx.gbl...
> you
behaviour
> outside
> my
is
> are
> that
> discussion
> Are
> It
> Oracle
> some
> return
pseudo-locked
> will
wrote
> in
> you
> my
is
> are
> discussion
> Are
> It
> Oracle
wrote
> after
I
> been
version
> of
sp3a
> surprising,
a
can
> <ian.posner.removethisantispamclause@.tdsecurities.com>
> 2000
> query
> outputs
defined
the
poor
> the
>|||Thanks for your help Tibor. You're right -- although the transaction
isolation level read committed hasn't been disabled, the behaviour has
changed in that updating a column to itself no longer results in another
spid honouring the locks placed on the rows in question. This is definitely
a change in behaviour, but one which poses no risk to data consistency.
Thanks for your help
Regards
Ian
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:udjjFRADEHA.4080@.TK2MSFTNGP09.phx.gbl...
> Ian,
>
change
> in
> I think that MS would call it an optimization. The optimization was
probably
> introduced in SQL2K. I tried your original code and it blocked on 7.0 sp4
> but not on SQL2K sp1 or SQL2K sp3.
>
> 'A'"
> first
> you
> Confirmed. It blocks, as expected, on SQL2K sp1 as well as sp3.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ian Posner" <ian.posner.removethisantispamclause@.tdsecurities.com> wrote
in
> message news:umDhf$$CEHA.3928@.TK2MSFTNGP09.phx.gbl...
change
> in
> 'A'"
> first
> you
> in
in
which
> behaviour
From
> is
locks
is
not?
values?
> pseudo-locked
query
> wrote
in
which
From
> is
locks
not?
values?
> wrote
But
> I
<ian.posner.removethisantispamclause@.tdsecurities.com>
of
> version
> sp3a
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
will
is
> a
> can
behavior
slim,
default:
is
> defined
> the
is
SQL
> poor
reconfigure
>|||And thank you for the update, Ian. :-)
Yes, at first glance, it feel a bit strange, this behaviour. But I guess I
trust the people at MS that this optimization indeed doesn't expose any
integrity problems.
The one thing I can think of, is if you explicitly ask for an XLOCK, and
that is being ignored (because you didn't change anything). As in the
example below. Hopefully, the developer would test the code and use
something like UPDLOCK, though. :-)
BEGIN TRAN
SELECT * FROM authors WITH (XLOCK) WHERE au_lname = 'White'
Use sp_lock and you will see exclusive locks. Open a new connection and
execute:
SELECT * FROM authors
Note that this is not blocked because of these "pseudo locks".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ian Posner" <ian.posner.removethisantispamclause@.tdsecurities.com> wrote in
message news:ugPj3AMEEHA.3412@.TK2MSFTNGP10.phx.gbl...
> Thanks for your help Tibor. You're right -- although the transaction
> isolation level read committed hasn't been disabled, the behaviour has
> changed in that updating a column to itself no longer results in another
> spid honouring the locks placed on the rows in question. This is
definitely
> a change in behaviour, but one which poses no risk to data consistency.
> Thanks for your help
> Regards
> Ian
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:udjjFRADEHA.4080@.TK2MSFTNGP09.phx.gbl...
> change
> probably
sp4
+
version
wrote
> in
non-patch
> change
+
version
wrote
> in
> which
> From
it
> locks
> is
just
> not?
> values?
in
to
should
> query
> in
> which
> From
it
> locks
> not?
> values?
in
behavior
> But
haven't
1)
> <ian.posner.removethisantispamclause@.tdsecurities.com>
installation
> of
committed
2000
> <tibor_please.no.email_karaszi@.hotmail.nomail.com>
my
> will
blocking.
> is
you
> behavior
> slim,
Server
the
> default:
> is
a
has
> is
> SQL
pretty
other
> reconfigure
transaction
>