1 / 32

Transaction Isolation Levels

Transaction Isolation Levels. Forum .NET Meeting ● Nov 16 2006. 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

Download Presentation

Transaction Isolation Levels

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related