https www free barcode generator net upc a Designing Transactions and Transaction Isolation in .NET framework

Drawer UPC-A in .NET framework Designing Transactions and Transaction Isolation

6

7. Close all open query windows except one, and in that query window, type and execute the following SQL statements to clean up after this exercise:

USE master; GO DROP DATABASE TestDB;

1. What is the difference between the READPAST and READUNCOMMITTED table locking hints 2. Which transaction isolation level cannot be specified as a table locking hint 3. What is the advantage of locking larger resources, such as tables, instead of rows 4. What is the main advantage of locking smaller resources, such as rows, instead of tables or pages Quick Check Answers 1. Both the READPAST and READUNCOMMITTED table locking hints will prevent SELECT (and also UPDATE/DELETE for READPAST) statements from being blocked by resources locked exclusively by other transactions. The difference is that READUNCOMMITTED will return the dirty values for locked resources, while READPAST will simply skip them. (That is, it will not return them at all). 2. The snapshot isolation level cannot be specified as a table locking hint. 3. The advantage of locking larger resources is that it will reduce the work required by the SQL Server lock manager to allocate locks (because far fewer locks are allocated), and it will also reduce the memory used to maintain locks. 4. The main advantage of locking smaller resources is that it greatly reduces the risk of blocking and deadlocks.

In this scenario, you are a database developer in your organization. An order entry application contains various elements that list the database contents. During peak usage, the lists frequently take longer than usual to display, even though they don t contain a lot of data. You have noticed that these delays are caused by blocking. The order entry application also contains several reports that display aggregated sales information. When these reports are doublechecked by auditors, they are sometimes found to contain inconsistent data. The values displayed at one place in a report might not be consistent with data in other places of the report.

The application currently uses the default transaction isolation level and does not use locking hints. Design a solution for both problems by implementing the appropriate transaction isolation level.

Practice 1 Create a simple table with one column and no primary key. Insert two rows into the table. Start a transaction (without committing it), and update one of the rows in the table. In another connection, try to read the unaffected row, both rows, and the affected row, and watch the result. Do this for all different isolation levels (including read committed snapshot), and observe the differences in behavior.

Create a simple table with one integer column, and declare it as the table s primary key. Within a user-defined transaction, insert the values 1, 1, and 2 into the table. Check whether any rows were persisted into the table (or if they were rolled back). Experiment with using the SET XACT_ABORT ON setting as well as using a try/catch block around the transaction. Also, always issue a SELECT XACT_STATE(); query at the end of each batch to see the state of the transaction.

Lock Compatibility http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx. Locking Hints http://msdn2.microsoft.com/en-us/library/ms189857.aspx Table Hint (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms187373.aspx. Using Snapshot Isolation http://msdn2.microsoft.com/en-us/library/tcbchxcb.aspx The Rational Guide to SQL Server 2005 Service Broker by Roger Wolter (Rational Press, 2006)

6

Summary

The choice of transaction isolation level affects both query results and performance because of blocking and row versioning. Use the least restrictive transaction isolation level. When a more restrictive transaction isolation level is required, consider applying table locking hints rather than specifying transaction isolation level on the session level using the SET TRANSACTION ISOLATION LEVEL statement. Keep transactions short. Open the transaction as late as possible, and close it as early as possible. Design transactions to minimize deadlocks. Consider alternate solutions to locking by using the @@ROWCOUNT function and the OUTPUT clause. When not rolling back a transaction in a try/catch block, always verify that the transaction is not uncommittable by querying the XACT_STATE() function.