Module 11 Creating Highly Concurrent SQL Server ® 2008 R2 Applications

1. Module 11 Creating Highly Concurrent SQL Server® 2008 R2 Applications

2. Module Overview • Introduction to Transactions • Introduction to Locks • Management of Locking • Transaction Isolation Levels

3. Lesson 1: Introduction to Transactions • What are Transactions? • Auto Commit Transactions • Explicit Transactions • Implicit Transactions • Transaction Recovery • Considerations for using Transactions • Demonstration 1A: Transactions

4. What are Transactions? A transaction is an atomic unit of work A transaction leaves data in a consistent state A transaction is isolated from other concurrent transactions A transaction is durable

5. Auto Commit Transactions • Default transaction mode • Every TSQL statement is committed or rolled back when it has completed. Committed if successful; Rolled back if error • Compile errors result in entire batch not being executed • Run time errors may allow part of the batch to commit • Database engine operates in autocommit until an explicit transaction is started. • XACT_ABORT setting ON converts statement terminating errors into batch terminating errors; compile errors not affected by XACT_ABORT ON SETXACT_ABORTON;

6. Explicit Transactions A transaction in which start and end of transaction is explicitly declared • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION • SAVE TRANSACTION • Transaction Log – Use WITH MARK to specify transaction marked in log BEGINTRANSACTIONFundsTransfer; EXECBanking.DebitAccount'100','account1'; EXECBanking.CreditAccount'100','account2'; COMMITTRANSACTION;

7. Implicit Transactions • Setting implicit transaction mode on • An implicit transaction starts when one of the following statements is executed and the statement is not part of an existing transaction • Transaction must be explicitly completed with COMMIT or ROLLBACK TRANSACTION SETIMPLICIT_TRANSACTIONS ON;

8. Transaction Recovery TransactionRecovery Action Required 1 None 2 Roll forward 3 Roll back 4 Roll forward 5 Roll back Checkpoint System Failure

9. Considerations for using Transactions • Keep transactions as short as possible • Do not require user input Do not browse data Access the least amount of data possible Do not open the transaction before it is required • Try to access resources in the same order • Accessing resources in the same order within transactions can help avoid deadlocks • This is not always possible • Considerations for nested transactions • Allowedby syntax but true nesting not supportedUse @@trancount to determine nesting levelWhen a nested transaction rolls back, it rolls back the outer transaction as well

10. Demonstration 1A: Transactions In this demonstration you will see • how transactions work • how blocking affects other users Note that blocking is discussed further in the next lesson.

11. Lesson 2: Introduction to Locks • Methods of Concurrency Control • What are Locks? • Blocking vs. Locking • What Concurrency Problems are Prevented by Locking • Lockable Resources • Types of Locks • Lock Compatibility

12. Methods of Concurrency Control Two main concurrency control types: • Pessimistic -Locks data when data is read in preparation for update -Other users are blocked until lock is released -Use where a high contention for data exists • Optimistic -Locks data when an update is performed -Error received if data is changed since initial read -Use where a low contention for data exists

13. What are Locks? • Mechanism to sync access by multiple users to the same data at the same time • Two main types of lock: Read locks – Allow others to read but not writeWrite locks – Stop others from reading or writing • Locks prevent update conflicts Locking ensures that transactions are serialized Locking is automatic Locks enable concurrent use of data

14. Blocking vs. Locking These two terms are often confused: • Locking - The action of taking and potentially holding locks - Used to implement concurrency control • Blocking -Normal occurrence for systems using locking - One process needs to wait for another process to release locked resources - Only a problem if it lasts too long

15. What Concurrency Problems are Prevented by Locking? Without locking mechanisms, the following problems can occur: • Lost updates • Uncommitted dependency (dirty read) • Inconsistent analysis (non-repeatable read) • Phantom reads • Missing and double reads caused by row updates

16. Lockable Resources SQL Server can lock these resources:

17. Types of Locks

18. Lock Compatibility • Not all locks are compatible with other locks. As a simple (but incomplete) example: • Refer to Books Online for a complete list

19. Lesson 3: Management of Locking • Locking Timeout • Lock Escalation • What are Deadlocks? • Locking-related Table Hints • Methods to View Locking Information • Demonstration 3A: Viewing Locking Information

20. Locking Timeout • How long should you wait for a lock to be released? • SET LOCK_TIMEOUT specifies number of milliseconds to wait • -1 (default) waits forever • When timeout expires, error is returned and statement rolled back • Not used often as most applications include query timeouts • READPAST locking hint – available but rarely used SETLOCK_TIMEOUT 5000;

21. Lock Escalation • Large numbers of rows are often processed • This brings a need for large numbers of locks • Acquiring and releasing a large number of locks can have a significant impact on processing performance and memory availability • SQL Server will escalate from row locks to the table level as needed • For partitioned tables, it can escalate to the partition level Lock escalation converts many fine-grain locks to fewer coarse-grain locks

22. What are Deadlocks? • Occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. -Task T1 has a lock on resource R1 (arrow from R1 to T1) and has requested a lock on resource R2 (arrow from T1 to R2). -Task T2 has a lock on resource R2 (arrow from R2 to T2) and has requested a lock on resource R1 (arrow from T2 to R1). -Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists. SQL Server automatically detects this situation and raises an error 1205 Task 1 Resource 1 Task 2 Resource 2

23. Locking-related Table Hints

24. Methods to View Locking Information Activity Monitor Dynamic Management Views SQL Server Profiler Reliability and Performance Monitor

25. Demonstration 3A: Viewing Locking Information In this demonstration, you will see how to: • View lock information using Activity Monitor • Use dynamic management views to view lock info

26. Lesson 4: Transaction Isolation Levels • SQL Server Transaction Isolation Levels • Read Committed Snapshot • Isolation-related Table Hints

27. SQL Server Transaction Isolation Levels • Transaction Isolation Level can be set at the session level separately for each transaction

28. Read Committed Snapshot • SNAPSHOT isolation level is useful but typically requires modifications to the application • In particular many reporting applications could benefit from it • Read Committed Snapshot is a database option that requires no modifications to the application • Statements that use Read Committed are automatically promoted to use Read Committed Snapshot instead • Locks are only held for the duration of the statement, not the duration of the transaction ALTERDATABASE Sales SETALLOW_SNAPSHOT_ISOLATIONON; ALTERDATABASE Sales SETREAD_COMMITTED_SNAPSHOTON;

29. Isolation-related Table Hints

30. Lab 11: Creating Highly Concurrent SQL Server Applications • Exercise 1: Detecting Deadlocks • Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits) Logon information Estimated time: 45minutes

31. Lab Scenario In this lab, you will perform basic investigation of a deadlock situation. You are trying to determine an appropriate transaction isolation level for a new application. If you have time, you will investigate the trade-off between concurrency and consistency.

32. Lab Review • What transaction isolation levels does SQL Server offer? • How does blocking differ from locking?

33. Module Review and Takeaways • Review Questions • Best Practices