Setting the Transaction Isolation Level

 

Setting the Transaction Isolation Level

Each SQL Server connection (SQL session) can have its transaction isolation level set. The setting you assign remains until the connection is closed or until you assign a new setting. One way to assign the transaction isolation level is to add the SQL statement to your stored procedure. For example, to set the transaction isolation level to Repeatable Read, add the following SQL statement to your stored procedure.

image from book

SQL

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
image from book

Another way to set the transaction isolation level is to add a query hint to your SQL statement. For example, the following SQL statement overrides the current session's isolation setting and uses the Read Uncommitted isolation level to perform the query.

image from book

SQL

SELECT * FROM CUSTOMERS WITH (NOLOCK) 
image from book

The transaction isolation level can also be set on the DbTransaction class, which the SqlTransaction class inherits from. Simply pass the desired transaction isolation level to the BeginTransaction method, as shown in the following code.

image from book

Visual Basic

Private Sub Button2_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles Button3.Click    Dim cnSetting As ConnectionStringSettings = _       ConfigurationManager.ConnectionStrings("NorthwindString")    Using cn As New SqlConnection()       cn.ConnectionString = cnSetting.ConnectionString       cn.Open()       Using tran As SqlTransaction = _          cn.BeginTransaction(System.Data.IsolationLevel.Serializable)          Try             'work code here             Using cmd As SqlCommand = cn.CreateCommand()                cmd.Transaction = tran                cmd.CommandText = "SELECT count(*) FROM employees"                Dim count As Integer = CInt(cmd.ExecuteScalar())                MessageBox.Show(count.ToString())             End Using             'if we made it this far, commit             tran.Commit()          Catch xcp As Exception             tran.Rollback()             'cleanup code             MessageBox.Show(xcp.Message)          End Try       End Using    End Using End Sub 
image from book

image from book

C#

private void button2_Click(object sender, EventArgs e) {    ConnectionStringSettings cnSetting =       ConfigurationManager.ConnectionStrings["NorthwindString"];    using (SqlConnection cn = new SqlConnection())    {       cn.ConnectionString = cnSetting.ConnectionString;       cn.Open();       using (SqlTransaction tran =          cn.BeginTransaction(System.Data.IsolationLevel.Serializable))       {          try          {             //work code here             using (SqlCommand cmd = cn.CreateCommand())             {                cmd.Transaction = tran;                cmd.CommandText = "SELECT count(*) FROM employees";                int count = (int)cmd.ExecuteScalar();                MessageBox.Show(count.ToString());             }             //if we made it this far, commit             tran.Commit();          }          catch (Exception xcp)          {             tran.Rollback();             //cleanup code             MessageBox.Show(xcp.Message);          }       }    } } 
image from book

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net