none
using "no lock" inside a transaction with read repeatable isolation level RRS feed

  • Question

  • Hi,

    If we instantiate a SqlTransaction in ADO.NET with isolationlevel = RepeatableRead:

    _Transaction = _Connection.BeginTransaction(IsolationLevel.RepeatableRead);

    And then we execute SQL that uses the (nolock) hint:

    select * from table with (nolock);

    Do we expect the isolation level of the query to go to ReadUncommited due to the nolock?

    What about if we call a stored proc from ADO.NET that has "set isolation level" in it. E.g. "set isolation level READ UNCOMMITTED"? 

    Finally, is there a way to view the isolation level using SQl Server Profiler - want to see isolation level for statements/stored procs executed against our DB?

    Thanks

    JP



    • Edited by Secret Sqrl Wednesday, October 7, 2015 1:38 PM
    Wednesday, October 7, 2015 1:37 PM

Answers

  • Hi,

    If we instantiate a SqlTransaction in ADO.NET with isolationlevel = RepeatableRead:

    _Transaction = _Connection.BeginTransaction(IsolationLevel.RepeatableRead);

    And then we execute SQL that uses the (nolock) hint:

    select * from table with (nolock);

    Do we expect the isolation level of the query to go to ReadUncommited due to the nolock?

    What about if we call a stored proc from ADO.NET that has "set isolation level" in it. E.g. "set isolation level READ UNCOMMITTED"? 

    Finally, is there a way to view the isolation level using SQl Server Profiler - want to see isolation level for statements/stored procs executed against our DB?

    Thanks

    JP


    I think this page explains well about the SqlServer Isolation levels. WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database.

    >>is there a way to view the isolation level using SQl Server Profiler - want to see isolation level for statements/stored procs executed against our DB?

    How to find Isolation level using T-SQL, http://stackoverflow.com/questions/1038113/how-to-find-current-transaction-level

    I hope it helps.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 9, 2015 3:08 AM
    Moderator