locked
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; RRS feed

  • Question

  • What does the following do? When should it be used? What are the PROs and CONs?

    SET

     

    TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    Friday, July 16, 2010 6:47 PM

Answers

  • It allows so called dirty reads i.e. reading data that is currently modified but the changes are not comitted.

    PRO: You can even read even inconsistent data and you are not locked.

    CON: The data you may be inconsistent

    >  When should it be used

    IMO never except for debugging. If you have locking problems try to optimise your transactions.

    Ralf

    • Proposed as answer by Naomi N Friday, July 16, 2010 9:51 PM
    • Marked as answer by ASP808 Friday, July 16, 2010 9:59 PM
    Friday, July 16, 2010 7:07 PM

All replies


  • Hi,

    Pro/Con wise it allows you to get the data back sometimes more quickly but you do this at the risk of getting incorrect data.

    As a general rule I would say do not use it, unless you have a specific reason for doing so.

    Cant really say it better than:

    http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx

    READ UNCOMMITTED

    Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.


    Jon
    • Proposed as answer by Naomi N Friday, July 16, 2010 9:51 PM
    Friday, July 16, 2010 7:03 PM
  • It allows so called dirty reads i.e. reading data that is currently modified but the changes are not comitted.

    PRO: You can even read even inconsistent data and you are not locked.

    CON: The data you may be inconsistent

    >  When should it be used

    IMO never except for debugging. If you have locking problems try to optimise your transactions.

    Ralf

    • Proposed as answer by Naomi N Friday, July 16, 2010 9:51 PM
    • Marked as answer by ASP808 Friday, July 16, 2010 9:59 PM
    Friday, July 16, 2010 7:07 PM