Module 8 Designing a Transaction and Concurrency Strategy

1. Module 8 Designing a Transaction and Concurrency Strategy

2. Module Overview • Guidelines for Defining Transactions • Defining Isolation Levels • Guidelines for Designing a Resilient Transaction Strategy

3. Lesson 1: Guidelines for Defining Transactions • Guidelines for Creating Transactions • Considerations for Using Nested Transactions • Demonstration: How to Create a Nested Transaction

4. Guidelines for Creating Transactions ü Manage transactions across multiple platforms ü Handle the errors in a transaction by using TRY…CATCH ü Use transaction savepoints ü Perform a transaction in: • Explicit Mode • Autocommit Mode • Implicit Mode

5. Considerations for Using Nested Transactions Committing inner transactions is ignored by the SQL Server Database Engine Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION The Transaction_name should not refer to the inner transactions of a set of named nested transactions The @@TRANCOUNT function records the current transaction nesting level ü ü ü ü

6. Demonstration: How To Create a Nested Transaction • In this demonstration, you will see how to: • Create a nested transaction

7. Lesson 2: Defining Isolation Levels Isolation Levels in the Database Engine Comparing Read Committed Isolation with Snapshot Isolation Selecting an Isolation Level for a Database Considerations for Offloading Concurrent Activities

8. Isolation Levels in the Database Engine READ UNCOMMITTED: Can read data that has been modified by other transactions but not committed READ COMMITTED: Cannot read data that has been modified but not committed by other transactions REPEATABLE READ: No other transactions can modify data that has been read by the current transaction until the current transaction completes SNAPSHOT: Data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction SERIALIZABLE: Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until it is complete

9. Comparing Read Committed Isolation with Snapshot Isolation When to Use Read Committed Isolation • Reader/writer blocking occurs to the point that concurrency benefits outweigh increased overhead of creating and managing row versions • An application requires absolute accuracy for long-running aggregations or queries where data values must be consistent to the point in time that a query starts When to Use Snapshot Isolation • Optimistic concurrency control is desired • Probability is low that a transaction would have to be rolled back because of an update conflict • An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency Use read committed isolation when: Use snapshot isolation when:

10. Selecting an Isolation Level for a Database Database Isolation Level Determine the minimum isolation level • Use alternatives to restrictive isolation levels, such as: • Redesigning Transact-SQL logic and tables • Redesigning how applications access data • Overriding the default locking strategy Use row versioning isolation levels Set the Connection Reset property to True in the connections string, for connection pools

11. Considerations for Offloading Concurrent Activities Reschedule activities Scale out the system Create a data warehouse ü ü ü • Offloading concurrent activities helps: • Avoid contention and blocking problems • Improve system performance Methods for Offloading Concurrent Activities

12. Guidelines for Designing an Object Access Strategy Guidelines for Transaction Rollback Guidelines for Using Optimizer Hints Guidelines for Using Locking Hint Functionalities Guidelines for Monitoring Transactions Guidelines for Troubleshooting Orphaned Transactions Demonstration: How to Use Locking Hints Discussion: Reducing Deadlock Errors Lesson 3: Guidelines for Designing a Resilient Transaction Strategy

13. Guidelines for Designing an Object Access Strategy 1 2 3 1 2 3 Communicate the object access order and enforce compliance Design an object access order based on usage 2 2 1 1 3 3 Manage conflicts when accessing objects Discourage ad hoc queries

14. Guidelines for Transaction Rollback Minimize errors Pre-validate data Monitor for rollbacks Define an error-handling strategy Define a rollback strategy

15. Guidelines for Using Optimizer Hints Query Hints The query optimizer enforces a join strategy between two tables Join Hints Query hints enforce query optimizer to execute the select statement as a whole Overrides the default behavior of the query optimizer Table Hints

16. Guidelines for Using Locking Hint Functionalities Locking hint functionalities of SQL Server 2008 include: • XLOCK at row level • READPAST • READCOMMITTEDLOCK Using locking hints, you can implement the following functionalities: • Provide finer locking control • Reduce deadlocking • Lower the isolation level

17. Guidelines for Monitoring Transactions sys.dm_tran_database_transactions ü DBCC OPENTRAN ü Split large transactions into smaller ones ü Schedule large transactions to run at off-peak hours ü Use the following options to monitor transactions: Use the following strategies to reduce the impact of large transactions:

18. Guidelines for Troubleshooting Orphaned Transactions Identify the orphaned processes using sp_who ü Ignore the orphaned processes or kill them ü Check for any improper procedures or general faults ü Check whether the Inter-Process Communication (IPC)session is still active ü

19. Demonstration: How To Use Locking Hints • In this demonstration, you will see how to: • Create a locking hint

20. Discussion: Reducing Deadlock Errors • What are good practices to prevent deadlock errors? • How would you use object access strategy to handle deadlock errors?

21. Lab 8: Designing a Transaction and Concurrency Strategy Exercise 1: Determining the Database Isolation Level Exercise 2: Determining the Order of Object Access Exercise 3: Designing Transactions Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd Logon Information Estimated time: 60 minutes

22. Lab Scenario You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp2008 sample database in SQL Server 2008. The main goals of the HR VASE project are as follows: • Provide managers with current and historical information about employee vacation and sick leave. • Grant view rights to individual employees to view their vacation and sick leave balances. • Provide permission to selected employees in the HR department to view and update the vacation and sick leave details of employees. • Grant the HR manager with the view and update rights to all the data. The management team has a requirement to generate performance metric for the sales staff. For this purpose, you are required to add a new function to the QuantamCorp HR VASE database that will import information from the CRM database. The management then will use statistical data to generate the performance metric report.

23. Lab Review • Describe another way to offload the services usage registration process. • If you remove the requirement for simultaneous phone number assignment, what solution would you propose?

24. Module Review and Takeaways Review Questions Real-world Issues and Scenarios Best Practices