Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 16, 2000

Transaction Isolation Level

By Alexander Chigrik


In this article I want to tell you about Transaction Isolation Level in SQL Server 6.5 and SQL Server 7.0, what kind of Transaction Isolation Level exists, and how you can set the appropriate Transaction Isolation Level.

There are four isolation levels:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • SQL Server 6.5 supports all of these Transaction Isolation Levels, but has only three different behaviors, because in SQL Server 6.5 REPEATABLE READ and SERIALIZABLE are synonyms. It because SQL Server 6.5 supports only page locking (there is no full support of row locking as in SQL Server 7.0) and if REPEATABLE READ isolation level was set, then another transaction cannot insert the row before the first transaction was finished, because page will be locked. So there are no phantoms in SQL Server 6.5, if REPEATABLE READ isolation level was set.

    SQL Server 7.0 supports all of these Transaction Isolation Levels and can separate REPEATABLE READ and SERIALIZABLE. Let me to describe each isolation level.

    READ UNCOMMITTED

    When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level.

    It ensures only that a physically corrupt data will not be read.

    READ COMMITTED

    This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.

    REPEATABLE READ

    When it's used, then dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.

    This is the definition of nonrepeatable read from SQL Server Books Online:

    nonrepeatable read
    When a transaction reads the same row more than one time, and between the
    two (or more) reads, a separate transaction modifies that row. Because the
    row was modified between reads within the same transaction, each read
    produces different values, which introduces inconsistency.
    

    SERIALIZABLE

    Most restrictive isolation level. When it's used, then phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction is complete.

    This is the definition of phantom from SQL Server Books Online:

    phantom
    Phantom behavior occurs when a transaction attempts to select a row that
    does not exist and a second transaction inserts the row before the first
    transaction finishes. If the row is inserted, the row appears as a phantom
    to the first transaction, inconsistently appearing and disappearing.
    

    You can set the appropriate isolation level for an entire SQL Server session with the SET TRANSACTION ISOLATION LEVEL statement. This is the syntax from SQL Server Books Online:

    SET TRANSACTION ISOLATION LEVEL
        {
            READ COMMITTED
            | READ UNCOMMITTED
            | REPEATABLE READ
            | SERIALIZABLE
        }
    

    You can use DBCC USEROPTIONS command to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO
    DBCC USEROPTIONS
    GO
    

    These are the results:

    Set Option                     Value
    ------------------------------ ------------------------------------
    textsize                       64512
    language                       us_english
    dateformat                     mdy
    datefirst                      7
    isolation level                read uncommitted
    


    » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM