1 / 10

SQL Server 2005 Engine Optimistic Concurrency

SQL Server 2005 Engine Optimistic Concurrency. Tony Rogerson, SQL Server MVP Independent Consultant http://sqlserverfaq.com tonyrogerson@torver.net. 26 th January 2006 12:45 – 13:15 Lunch n Learn webcast series. Agenda. Isolations Pessimistic Default Isolation – Writers block Readers

gryta
Download Presentation

SQL Server 2005 Engine Optimistic Concurrency

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. SQL Server 2005 EngineOptimistic Concurrency Tony Rogerson, SQL Server MVP Independent Consultant http://sqlserverfaq.com tonyrogerson@torver.net 26th January 2006 12:45 – 13:15 Lunch n Learn webcast series

  2. Agenda • Isolations • Pessimistic • Default Isolation – Writers block Readers • Optimistic • READ_COMMITTED_SNAPSHOT(statement level read consistency) • ALLOW_SNAPSHOT_ISOLATION(transaction level read consistency) • Architecture: Row Versioning • Monitoring

  3. Uses • Anything to reduce locking contention • Near real-time statistics page (call centre, stock ticker) • Anywhere using NOLOCK hint • Extraction from OLTP into DW/MIS • Not an answer for deadlocking but will reduce locking and may help

  4. Demo

  5. Row Versioning Remove by clean up Sess A: UPDATE -> ‘START’ Sess B: XACT ISO – SNAPSHOT BEGIN TRAN SELECT… Mark active Store old row ‘START’ Sess A: UPDATE -> ‘UPD 1’ Sess C: XACT ISO – SNAPSHOT BEGIN TRAN SELECT… Mark active Store old row ‘UPD 1’ Sess A: UPDATE -> ‘UPD 2’

  6. READ_COMMITTED_SNAPSHOT • Statement level isolation (gives value of last committed row at time of query) • Writer no longer blocks reader • Possibly reduced deadlocking because of reduced locking contention

  7. ALLOW_SNAPSHOT_ISOLATION • Transaction level isolation (gives last committed row at start of transaction) • Snapshot of values taken on select and held in version store in tempdb • Stores full row (changed fragment for lob) • Queries require SET TRANSACTION ISOLATION LEVEL SNAPSHOT • Update fails if update on a value changed outside your snapshot

  8. Architecture: Row Versioning • Used by other stuff… • inserted and deleted tables in triggers • MARS (Multiple Active Result Sets) • Index operations using the ONLINE feature • Transaction Isolations • Versions held in tempdb (the version store) • Changed row or changed fragment for LOB stored in tempdb • Background thread runs every minute to remove versions no longer required. • Short lived transactions will not require a disk IO on tempdb

  9. Monitoring • sys.dm_tran_active_snapshot_database_transactions • sys.dm_tran_version_store • sys.dm_tran_current_transaction • sys.dm_tran_transactions_snapshot • sys.dm_tran_top_version_generators • sys.dm_tran_locks

  10. Thanks for ListeningQ & A Tony Rogerson, SQL Server MVP Independent Consultant http://sqlserverfaq.com tonyrogerson@torver.net

More Related