What's the use of IsolationLevel in SqlTransaction if I never get to read volatile data from one transaction in another?

I'm studying data access in .NET Framework 4 for a Microsoft Certification and I've been doing a lot of tests.

This time I want to understand the purpose of IsolationLevel in the SqlTransaction class. As I understand, this option allows me to decide whether or not, volatile data in one transaction is accessible by another one. That is, for instance: I create a data row in Transaction 1 and, before commiting it, I can read the data in Transaction 2.

The fact is that I can't get to read volatile data from one in another.

• If I create 1 connection with 2 transactions I get an exception because SQL Server does not support parallel transactions
• If I create 2 connections with 1 transaction each, no matter the value of IsolationLevel, the program stops when I try to access data in connection 2 while connection 1 is in transaction. As connection 1 leaves the transaction, the program continues.

So.. What's the use of IsolationLevel?

PS: I'm not talking about distrib开发者_如何学JAVAuted transactions here, just the simple SqlTransaction.

---

You need 2 connections, 2 transactions, on 2 threads.

thread 1:

using (SqlConnection conn = GetSqlConnection())
{
  using (SqlTransaction trn = conn.BeginTransaction())
  {
    -- insert row into table here, then wait
  }
}

thread 2:

using (SqlConnection conn = GetSqlConnection())
{
  using (SqlTransaction trn = conn.BeginTransaction(IsolationLevel.<desiredlevel>))
  {
    -- wait for thread 1 to insert then read row inserted by thread 1 
  }
}

Under ReadUncommited the second thread will read the row. Under Snapshot (if Snapshot is enabled in the database) it will not see it, but it won't block. Under all other isolation levels it will block until the thread 1 commits or rolls back.

---

protected void Button3_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(Conn_String);
    conn.Open();

    SqlTransaction trans = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

    SqlCommand cmd = new SqlCommand("update tbl_user_master set page_state=1", conn);

    cmd.Transaction = trans;
    cmd.ExecuteNonQuery();
    call(trans, conn);

    trans.Commit();

}

public void call(SqlTransaction trans, SqlConnection conn)
{

    SqlCommand cmd = new SqlCommand("update tbl_user_master set page_state=0", conn);
    cmd.Transaction = trans;
    cmd.ExecuteNonQuery();

}

This is used in case you have multiple functions with 1 SQL transaction