Using Transaction Isolation Levels to Protect Data

[ Team LiB ] Recipe 6.12 Using Transaction Isolation Levels to Protect Data Problem You want to effectively use transaction isolation levels to ensure data consistency for a range of data rows. Solution Set and use isolation levels as shown in the following example.
Nội dung trích xuất từ tài liệu:
Using Transaction Isolation Levels to Protect Data[ Team LiB ]Recipe 6.12 Using Transaction Isolation Levels to Protect DataProblemYou want to effectively use transaction isolation levels to ensure data consistency for arange of data rows.SolutionSet and use isolation levels as shown in the following example.The sample code contains three event handlers:Start Tran Button.Click Opens a Connection and starts a transaction with the specified isolation level: Chaos, ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, or Unspecified. Within the transaction, a DataTable is filled with the Orders table from the Northwind database. The default view of the table is bound to the data grid on the form.Cancel Button.Click Rolls back the transaction, closes the connection, and clears the data grid.Form.Closing Rolls back the transaction and closes the connection.The C# code is shown in Example 6-30.Example 6-30. File: TransactionIsolationLevelsForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Windows.Forms;using System.Data;using System.Data.SqlClient;private SqlConnection conn;private SqlTransaction tran;// . . .private void startButton_Click(object sender, System.EventArgs e){ startButton.Enabled = false; // Get the user-defined isolation level. IsolationLevel il = IsolationLevel.Unspecified; if(chaosRadioButton.Checked) il = IsolationLevel.Chaos; else if(readCommittedRadioButton.Checked) il = IsolationLevel.ReadCommitted; else if(readUncommittedRadioButton.Checked) il = IsolationLevel.ReadUncommitted; else if(repeatableReadRadioButton.Checked) il = IsolationLevel.RepeatableRead; else if(serializableRadioButton.Checked) il = IsolationLevel.Serializable; else if(unspecifiedRadioButton.Checked) il = IsolationLevel.Unspecified; // Open a connection. conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]); conn.Open( ); try { // Start a transaction. tran = conn.BeginTransaction(il); } catch(Exception ex) { // Could not start the transaction. Close the connection. conn.Close( ); MessageBox.Show(ex.Message,Transaction Isolation Levels, MessageBoxButtons.OK, MessageBoxIcon.Error); startButton.Enabled = true; return; } String sqlText = SELECT * FROM Orders; // Create a command using the transaction. SqlCommand cmd = new SqlCommand(sqlText, conn, tran); // Create a DataAdapter to retrieve all Orders. SqlDataAdapter da = new SqlDataAdapter(cmd); // Define a CommandBuilder for the DataAdapter. SqlCommandBuilder cb = new SqlCommandBuilder(da); // Fill table with Orders. DataTable dt = new DataTable( ); da.Fill(dt); // Bind the default view of the table to the grid. dataGrid.DataSource = dt.DefaultView; cancelButton.Enabled = true; dataGrid.ReadOnly = false;}private void cancelButton_Click(object sender, System.EventArgs e){ cancelButton.Enabled = false; dataGrid.ReadOnly = true; // Roll back the transaction and close the connection. tran.Rollback( ); conn.Close( ); // Unbind the grid. dataGrid.DataSource = null; startButton.Enabled = true;}private void UsingLockingHintsForPessimisticLockingForm_Closing( object sender, System.ComponentModel.CancelEventArgs e){ // Roll back the transaction and close the connection. tran.Rollback( ); conn.Close( );}DiscussionThe isolation level specifies the transaction locking behavior for a connection. Itdetermines what changes made to data within a transaction are visible outside of thetransaction while the transaction is uncommitted.Concurrency violations occur when multiple users or processes attempt to modify thesame data in a database at the same time without locking. Table 6-16 describesconcurrency problems. Table 6-16. Concurrency problems Condition Description Two or more transactions select the same row and subsequentlyLost Update update that row. Data is lost because the transactions are unaware of each other and overwrite each others updates. A second transaction selects a row that has been updated, but notUncommitted committed, by another transaction. The first transaction makesDependency (Dirty more changes to the data or rolls back the changes already madeRead) resulting in the second transaction having invalid data.Inconsistent Analysis A second transaction reads different data each time that the same(Nonrepeatable row is read. Another transaction has changed and committed theRead) data between the reads. An insert or delete is performed for a row belonging to a range of rows being read by a transaction. The rows selected by thePhantom Read transaction are missing the inserted rows and still contain the deleted rows that no longer exist.Locks ensure transactional integrity and maintain database consistency by controllinghow resources can be accessed by concurrent transactions. A lock is an object indicatingthat a user has a dependency on a resource. It prevents other users from performingoperations that would adversely affect the locked resources. Locks are acquired andreleased by user actions; they are managed internally by database software. Table 6-17lists and describes resource lock modes used by ADO.NET. Table 6-17. Resource lock modes Lock Descripti ...