SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Has anyone seen a situation where running the following set command:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

does not work? In other words, this command should turn off locking for the connection it is set for. But has anyone ever seen a case where locking was not turned off? And if so, what would be the reason?

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Setting this will change the properties for a connection but not for other users.

Did you see locking due to this connection or some other connection?

If this connection (for which the properties are set) is reading some data then other users may see a locking due to this connection. This is because, althought the connection can read uncomitted data, still it needs shared locks on the data being read.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

It's enough that another connection will place a lock on the table/s in question. You should be able to read them anyway (dirty reads maybe) and locks will server the other connection as needed. Isn't it how it supposed to be working?

Bambola.

Perhaps I didn't clarify my question. Did you experience locking and were not able to query data in the connection in which the property was set?

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Specifically, we have a SP that when runs at the same time, one SP blocks the other SP. When we added SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to the SP, it didn't fix the problem, as we expected.

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Will it be possible for you to post the code and the table structures for the tables involved in the query?

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Have you tried using NOLOCK instead of read uncommitted? I should be the same, shouldn't it...

Bambola.

I have found this article refering to SQL Server 6.5. I do not suppose you are using 6.5, but it might give you an idea about how to fix the problem.

http://support.microsoft.com/defaul...port/kb/articles/Q171/3/22.asp&NoWebContent=1

Bambola.

I have tried the example of the article above with both serializable and read committed transaction level.
Using SQL server 2000.

In one window I ran


use pubs
go

CREATE TABLE testing (f1 int NOT NULL , f2 varchar (24) NOT NULL )
GO

create procedure testproc as
set transaction isolation level serializable -- or read uncommitted
select * from testing
go

BEGIN TRANSACTION
insert into testing values (123456, '123456')
go

in another


use pubs
go

set transaction isolation level read uncommitted
go

exec testproc
go
but without results since lock were held.

in a third window


select * from testing with (nolock)
and I could see the uncommitted row!

Bambola.

The code is too long to publish (over 1500 lines written by a report developer). I haven't tried it with no lock, but will give it a try.

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com