Transaction Isolation Levels

1. Transaction Isolation Levels Forum .NET Meeting ● Nov 16 2006 Random Logic l Forum .NET l 2006

2. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

3. Agenda • Deadlocks • Defined • Preventing • Transactions best practices Random Logic l Forum .NET l 2006

4. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

5. Transactions and Locking • Transactions • Define a transaction ? • Transactions must be ACID • Atomic – One unit of work. All operations within a transaction must succeed. • Consistent – Transactions should move the DB from one consistent state to another. • Isolated – Prevent concurrency issues. Implemented in SQL Server by locking. • Durable – Changes performed in a transaction should be stored on hard disk. Random Logic l Forum .NET l 2006

6. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

7. Concurrency problems • Dirty read • Read uncommitted Data • Non Repeatable read • Data changes between read and read / update operations in the same transaction • Phantoms • Range operation does not affect new row inserted to DB Random Logic l Forum .NET l 2006

8. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

9. SQL Server Locking Mechanism Described • SQL Server decides lock type and scope • User decides lock duration and there by isolation level of transaction Random Logic l Forum .NET l 2006

10. Locking Scope • SQL Server can issue locks on several levels • Row • Data page • Table • (Other level exist but do not affect our discussion) • Locking scope is determined by the server. • SQL server will prefer granular locks but will escalate locking level based on available memory resources. Random Logic l Forum .NET l 2006

11. Lock Types • Shared • Used for select operations • Enable other sessions to perform select operations but prevent updates • Exclusive • Used for DML operations • Prevents other users from accessing the resource • Update • Preliminary stage for exclusive lock. Used by the server when filtering the records to be modified • Prevents other update locks • A solution to the cycle deadlock problem • Lock type can be determined by the user using hints Random Logic l Forum .NET l 2006

12. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

13. Isolation Levels • Read Committed • Read Uncommitted • Repeatable Read • Serializable • מדיניות הנעילה נקבעת ע"י המשתמש • Set transaction isolation level … Random Logic l Forum .NET l 2006

14. Read Committed • Only committed data can be read • Exclusive lock held for the entire duration of the transaction. • Shared lock held momentarily • Prevents Dirty Reads Random Logic l Forum .NET l 2006

15. Read Uncommitted • Uncommitted data can be read • Exclusive lock held for the entire duration of the transaction. • Shared lock held momentarily • Prevents nothing Random Logic l Forum .NET l 2006

16. Repeatable read • Data that has been read in the transaction scope can not be changed by other transactions • Exclusive lock held for the duration of the transaction. • Shared lock held for the duration of the transaction. • Prevents Dirty Reads • and Non Repeatable reads Random Logic l Forum .NET l 2006

17. Serializable • New data will not be inserted into DB if a transaction performs a range operation which should include the new data • Exclusive lock held for the duration of the transaction. • Shared lock held for the duration of the transaction. • Holds range locks • Prevents Dirty Reads, Non Repeatable reads and phantoms. Random Logic l Forum .NET l 2006

18. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

19. Agenda • Transactions and Locking • Why do we need transactions to be isolated ? • Concurrency problems • SQL Server locking mechanism described • What resources can be locked? • Locking types • Isolation levels • New Isolation Levels in SQL Server 2005 • Snapshot Isolation Random Logic l Forum .NET l 2006

20. Snapshot Isolation • SQL Server 2000 implemented the ANSI standard • Based on locking • Pessimistic • Can hurt concurrency • Writes block readers Random Logic l Forum .NET l 2006

21. Snapshot Isolation • Oracle implemented non ANSI solution • Based on versioning • User gets a view of the database as of start of query or transaction • Optimistic • No lock on read operations but Possible update conflicts • Better concurrency but must maintain version history and handle update conflicts Random Logic l Forum .NET l 2006

22. Account Report Report Transaction 1-3 5.82 1 Read 1-3 1 Read 1-9 1-9 1.18 Versioned Total 7.00 1-3:1 = 5.82 1-9:1 = 1.18 Account 1-3:2 = 6.91 1-9:2 = 3.45 Report reflects account Values values in database 2 Write 1-3 2 Write 1-9 when report transaction started Post GL Transaction Snapshot reads • transaction reads version of value corresponding to its start time Random Logic l Forum .NET l 2006

23. Transaction 1 Read 1-3 Write 1-3 Versioned serialization would 1-3:1 = 6.91 write fails Account have deferred read 1-3:2 = 7.24 Values to here Write 1-3 transaction committed Transaction 2 Snapshot writes Random Logic l Forum .NET l 2006

24. Snapshot isolation types • SQL Server 2005 provides two styles of snapshot isolation • transaction-level snapshot • consistent as of beginning of transaction • won't see changes others commit during transaction • most like serializable in oracle • statement-level snapshot • Read Committed with snapshot isolation • Each statement sees only changes committed before the start of the statement • will see changes others commit during transaction • most like read committed in oracle Random Logic l Forum .NET l 2006

25. Usage guidelines • Enables application porting from Oracle to SQL Server 2005 • Trade off between cost of managing versions and rolling back transactions due to update conflicts and cost of blocking • Handle performance issues steaming from concurrent read and write operations • Replace select statements using readuncommitted or nolock hints Random Logic l Forum .NET l 2006

26. Usage guidelines • Use snapshot isolation when application needs uniform, consistent view of database over multiple select statements • Reporting on live data based on several select statements • Create consistent value lists for GUI Random Logic l Forum .NET l 2006

27. DeadLocks • Defined • Preventing Random Logic l Forum .NET l 2006

28. Types - Cycle DeadLock A B A מנסה לרכוש נעילה על 2 אבל נכשל בשל הנעילה של B B מנסה לרכוש נעילה על 1 אבל נכשל בשל הנעילה של A 1 2 Bמחזיק Exclusive על 2 Aמחזיק Exclusive על 1 Random Logic l Forum .NET l 2006

29. Types - Conversion DeadLock Aמנסה לקבל נעילת EXCLUCIVE על 1 אבל נכשל בשל קיום Shared עבור B A B Bמנסה לקבל נעילת EXCLUCIVE על 1 אבל נכשל בשל קיום Shared עבור A 1 ל A ו B יש נעילת Shared על 1 Random Logic l Forum .NET l 2006

30. Handling deadlocks • SQL Server sacrifices one of the transactions (Error 1205) • SQL Server will choose to kill the transaction • Set DeadLock_Priority can be used to choose the process to be killed • Can be set to low, normal, high, (-10 to 10) Random Logic l Forum .NET l 2006

31. Preventing • PreventingCycle DeadLoacks • Using resources in the same order • PreventingConversion DeadLocks • Using theUpdlock hint • Only one update lock can exist on a resource • General recommendation • Keep transactions as short as posible Random Logic l Forum .NET l 2006

32. Transactions best practices • Keep transactions as short as possible • Open a transaction at the last possible point in time • Close transactions as early as possible. • Make all necessary data available before starting the transaction. • Get user input outside of the transaction Random Logic l Forum .NET l 2006