Using Transaction Isolation Levels to Protect Data

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

81
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ 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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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. 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 constants using 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( ); } Discussion The isolation level specifies the transaction locking behavior for a connection. It
determines what changes made to data within a transaction are visible outside of the transaction while the transaction is uncommitted. Concurrency violations occur when multiple users or processes attempt to modify the same data in a database at the same time without locking. Table 6-16 describes concurrency problems. Table 6-16. Concurrency problems Condition Description Two or more transactions select the same row and subsequently Lost Update update that row. Data is lost because the transactions are unaware of each other and overwrite each other's updates. A second transaction selects a row that has been updated, but not Uncommitted committed, by another transaction. The first transaction makes Dependency (Dirty more changes to the data or rolls back the changes already made Read) 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 the Read) 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 the Phantom 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 controlling how resources can be accessed by concurrent transactions. A lock is an object indicating that a user has a dependency on a resource. It prevents other users from performing operations that would adversely affect the locked resources. Locks are acquired and released by user actions; they are managed internally by database software. Table 6-17 lists and describes resource lock modes used by ADO.NET. Table 6-17. Resource lock modes Lock Description mode Concurrent transactions can read the locked resource. Concurrent Shared transactions cannot modify the locked resource while the lock is held. Prevents both read and modify access to a resource by concurrent Exclusive transactions.
Isolation level defines the degree to which one transaction must be isolated from other transactions. A higher isolation level increases data correctness but decreases concurrent access to data. Table 6-18 describes the different isolations levels supported by ADO.NET. The first four levels are listed in order of increasing isolation. Table 6-18. IsolationLevel enumeration Name Description No shared locks are issued. Exclusive locks are not honored.A dirty ReadUncommitted read is possible. Shared locks are held while data is being read by the transaction.Dirty reads are not possible. Nonrepeatable reads or ReadCommitted phantom rows can still occur because data can be changed prior to being committed. Shared locks are placed on all data used by the query. Other users RepeatableRead are prevented from updating the data.Nonrepeatable reads are prevented, but phantom reads are still possible. A range lock—covering individual records and the ranges between them—is placed on the data preventing other users from updating Serializable or inserting rows until the transaction is complete.Phantom reads are prevented. Pending changes from more highly isolated transactions cannot be Chaos overwritten.This isolation level is not supported by SQL Server. A different isolation level than the one specified is being used, but Unspecified that level cannot be determined. In ADO.NET, the isolation level can be set by creating the transaction using an overload of the BeginTransaction( ) method of the Command or by setting the IsolationLevel property of an existing Transaction object. The default isolation level is ReadCommitted. Parallel transactions are not supported, so the isolation level applies to the entire transaction. It can be changed programmatically at any time. If the isolation level is changed within a transaction, the new level applies to all statements remaining in the transaction. [ Team LiB ]