Transcript Read_Dirty_to_Mex

Read Dirty to Me:
SQL Server Isolation Levels
Wendy Pastrick
Arrow IT Consulting
About me
SQL Server DBA since 2000
 PASS Women In Technology
 PASS Board of Directors
 SQL Server MVP

@wendy_dance
 Wendyverse.blogspot.com

Agenda
Transaction Isolation
 Dirty Reads and Other Phenomena
 Isolation Levels
 Management Views
 Lock Manager
 Transaction Control
 The Bottom Line

Transaction Isolation
The level at which a transaction is
prepared to accept inconsistent data
 Concurrency
 Consistency

Concurrency
Consistency
Who Cares?
Just “Set It” and “Forget It”
 Locking & Blocking

Dirty Reads and…
Dirty Read
◦ Cheating on a Test
…Other Phenomena
Non-Repeatable Read
◦ It was there
a second ago!
…Other Phenomena
Phantom Read
◦ Now, where did THAT come from?
Isolation Levels
Transaction Based
 Defines the degree to which one
transaction must be isolated from
resource or data modifications made by
other transactions
 Area of Impact

◦ Transaction (Query)
◦ Database
Half Full or Half Empty?
Isolation Levels

Read UnCommitted
◦ The Dirty Read
◦ Okay sometimes
 Data that does not change often
Concurrency
Consistency
Isolation Levels

Read Committed
◦
◦
◦
◦
Pessimistic
Default SQL Server Isolation Level
Rows only locked when being accessed
Demo Time!
Concurrency
Consistency
Isolation Levels

Repeatable Read
◦ Pessimistic
◦ Rows locked as they are read
◦ Rows remain locked until transaction
completes
◦ Phantom Rows possible
◦ Demo Time!
Concurrency
Consistency
Isolation Levels

Serializable
◦ Key Range Locks
◦ Pessimistic
◦ All data locked until Transaction completes
 Locking & Blocking
◦ Only one transaction at a time
◦ Marbles in a bag
Concurrency
Consistency
Serializable
Tran #1: Change all White to RED
Tran #2: Change all RED to White
Who Wins?
Isolation Levels

Snapshot Isolation (2005+)
◦
◦
◦
◦
Row Versioning
Uses Tempdb
Optimistic
Possible to have two transactions update the
EXACT SAME DATA
 But wait, there’s Conflict Detection!
◦ Marbles in a bag again
Concurrency
Consistency
Snapshot
Tran #1: Change all RED to White
Tran #2: Change all White to RED
Who Wins?
Snapshot
Tran #1: Change all RED to White
Tran #2: Change all White to RED
Merge Results:
Isolation Levels

Read Committed Snapshot Isolation
(2005+)
◦ Row Versioning
◦ Optimistic
Concurrency
Consistency
Management Views

sys.dm_trans_locks
◦ View locks set by isolation

sys.dm_tran_version_store
◦ Shows versions in the ‘store’

sys.dm_os_waiting_tasks
◦ Join with sys.dm_trans_locks to see blocking
Lock Manager
Resource
Description
RID
A row identifier used to lock a single row within a heap.
KEY
A row lock within an index used to protect key ranges in serializable transactions.
PAGE
An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENT
A contiguous group of eight pages, such as data or index pages.
HoBT
A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered
index.
TABLE
The entire table, including all data and indexes.
FILE
A database file.
APPLICATION
An application-specified resource.
METADATA
Metadata locks.
ALLOCATION_UNIT
An allocation unit.
DATABASE
The entire database.
Transaction Control

SET TRANSACTION ISOLATION LEVEL

Table Hints override
◦ With NO LOCK – not as good as it sounds
The Bottom Line

Transaction Processing
◦ CLOSE Transactions

Choose Wisely
◦ Concurrency vs. Consistency

Hidden Impact
◦ Query Plans
◦ Indexing Choices
Many MANY Thanks
MSDN
 Kendra Little
 Craig Freedman
 Michael J Swart
 Grant Fritchey
 Itzik Ben-Gan
http://wendyverse.blogspot.com/p/presentat
ions-links.html

Cool Links
(NOLOCK) for YESFUN by Kendra Little
 Itzik Ben-Gan
 Michael J Swart
 …and MORE!

Wendyverse.blogspot.com
Contact me
[email protected]
 @wendy_dance on Twitter
 wendyverse.blogspot.com
