SQL Server Performance

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Discussion in 'Performance Tuning for DBAs' started by bradmcgehee, Aug 5, 2003.

  1. bradmcgehee New Member

    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
  2. gaurav_bindlish New Member

    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
  3. bambola New Member

    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.
  4. gaurav_bindlish New Member

    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
  5. bradmcgehee New Member

    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
  6. gaurav_bindlish New Member

    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
  7. bambola New Member

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

    Bambola.
  8. bambola New Member

  9. bambola New Member

    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.


  10. bradmcgehee New Member

    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

Share This Page