Transcript SQL Server 2008 Transactions @ MS DevDays 2008

Best Practices for
Optimizing Transactional
Code in SQL Server 2008
Svetlin Nakov
Director Training and Consulting Activities
National Academy for Software Development (NASD)
http://academy.devbg.org
лектор:
Борислава Палева
Agenda
 ACID Transactions and Transaction Modes
in SQL Server
 Understanding Isolation Levels
 Concurrency Problems
 Locking vs. Versioning
 Allowing Snapshot Isolation in SQL Server
 SQL Server vs. Oracle Transactions
3
Transactions in SQL
Server 2008
Database Transactions
 Transactions are a sequence of modifications in
the database executed as a single unit of work:
 Either all of them execute successfully
 Or none of the them
 Example:
 A bank transfer from one account into another
(withdrawal + deposit)
 If either the withdrawal or the deposit fails the
whole operation is cancelled
5
ACID Properties of Transactions
 Atomicity
 Either all modifications are performed, or none
 Consistency
 When completed, transactions leave all data and
all related structures in a consistent state
 Isolation
 Transactions don't see other's transaction's
uncompleted work (intermediate state)
 Durability
 Transactions persist despite of system failure
6
ACID Transactions in SQL Server
 Handled automatically across databases on
the same instance
 Handled through MSDTC automatically or
explicitly across instances
 Controlled by Transaction Mode of the session
 Can be defined explicitly, at connection level
and at server level
 Requires error handling logic
 Programming errors do not cause transaction
rollback unless XACT_ABORT setting on
7
Transaction Mode
 Autocommit transactions (default)
 Statement level implicit transaction
 Each statement commits as a single unit
 Explicit transactions (user-defined)
 BEGIN TRANSATION
 COMMIT / ROLLBACK TRANSACTION
 Implicit transactions
 Session Level Setting
SET IMPLICIT_TRANSACTIONS ON
8
Concurrency Problems
and Isolation
Concurrency Problems
 SQL Server transaction isolation solves four
major concurrency problems
 Dirty read occur when a reader reads
uncommitted data
 Unrepeatable read occurs when existing row
change within a transaction
 Lost updates occur when two writers modify
the same piece of state
 Phantoms occur when new rows are added and
appear within a transaction
10
Locking Strategies
 Optimistic concurrency
 Locks are not used – readers never wait
 Conflicts are possible
 Can be resolved before commit
 High concurrency – scale well
 Pessimistic concurrency
 Use exclusive and shared locks
 Transactions wait for each other
 Low concurrency – does not scale
11
Isolation Levels and Locking
 ANSI Isolation Levels
 Level 0 – Read Uncommitted
 Level 1 – Read Committed
 Level 2 – Repeatable Reads
 Level 3 – Serializable
 Default isolation level in both SQL Server
2005/2008 is ANSI Level 1, Read Committed
 In implementation this default level uses
locking
 Pessimistic concurrency
12
Isolation Levels and Concurrency in
SQL Server 2008
Isolation Level
Dirty NonrepeatLost
Phantom Concurrency
Reads able Reads Updates
Reads
Model
Read uncommitted
Yes
Yes
Yes
Yes
Pessimistic
Read committed
snapshot
No
Yes
Yes
Yes
Optimistic*
Read committed
No
Yes
Yes
Yes
Pessimistic
Repeatable read
No
No
No
Yes
Pessimistic
Snapshot
No
No
No**
No
Optimistic*
Serializable
No
No
No
No
Pessimistic
* Optimistic concurrency uses row versioning instead of row locking
** Lost updates in snapshot isolation level are prevented by conflict
detection instead of locking
13
Concurrency Problems
Live Demo
Transactions Isolation:
Locking vs. Versioning
Basic Locking Types
 SQL Server insures isolation by means of
locking
 Write locks are exclusive locks, read locks
(shared locks) allow other readers
 A well-formed transaction acquires the correct
lock type prior to using state
 A two-phased transaction holds all locks until
all locks have been acquired
 Isolation levels determine how long locks are
held
16
Traditional Transactions
 SQL Server accomplishes isolation via
locking
 Writers wait on read locks
 Both readers and writers wait on write
locks
 Variety of isolation levels supported
 How long locks are held
 What is locked (data and/or metadata)
17
Transactions and Consistency
 SQL Server cannot guarantee statement-level
consistency by locking
 Even at serializable transaction level
 Rows are locked as they are read
 Rows can be changed after the execution of the
reading statement begin
 Read-consistency not guaranteed when multiple
statements read multiple tables
 e.g. data warehouse unload jobs
 Readers cannot read old values when data is
being updated
18
Transaction Isolation & Versioning
 SQL Server 2008 can also use row versioning
 Isolation accomplished by combination of locking
and versioning
 Write locks block other writers
 Write locks do not block readers
 Readers do not lock by default
 Readers do not block writers by default
 Known as snapshot isolation
 Old rows kept in tempdb for reading
 Eases conversion from versioning databases
19
Snapshot Reads
 Snapshot always reads coherent data
 No read lock needed even though data being
changed
 Version of each value maintained as long as
needed
 Transaction reads version of value
corresponding to its start time
20
Snapshot Writes
 Writes store the old version of changed
rows in tempdb
 This takes time and consume storage but
allows better concurrency
 Snapshot Transactions are ACID
 Failures may be deferred
 Write fails if version later than that when
transaction started
 Serialization isolation holds off transaction
until safe to proceed
21
Snapshot Isolation Types
 Two styles of snapshot isolation
 Statement-level snapshot – like read
committed
 Consistent as of last statement
 See changes others commit during
transaction
 Transaction-level snapshot – like serializable
 Consistent as of beginning of transaction
 Don't see changes others commit during
transaction
 Provides mandatory conflict detection
22
Using Snapshot Isolation
 Must be explicitly enabled at database level
 Set ALLOW_SNAPSHOT_ISOLATION option ON
 master and msdb are snapshot enabled by
default
 Transaction isolation level must be SNAPSHOT
USE master
GO
ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON
GO
...
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
23
Using Statement-Level Snapshot
Isolation
 Must be explicitly enabled at database level
 Set READ_COMMITTED_SNAPSHOT option ON
 Transaction isolation level must be READ
COMMITTED
USE master
GO
ALTER DATABASE pubs SET READ_COMMITTED_SNAPSHOT ON
GO
...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
24
Snapshot vs. Serializable
Isolation Levels
Live Demo
Versioning Drawbacks
 Versioning is not without cost
 Takes up space in tempdb
 Versions kept regardless of usage
 Space usage must be planned
 Up to twice as much I/O for updates
 Readers must read through saved versions
 More versions, slower reads
 Conflict detection can cause updates to roll back
Msg 3960, Level 16, State 1, Line 1. Cannot use
snapshot isolation to access table 'authors' in
database 'pubs'. Snapshot transaction aborted due to
update conflict. Retry transaction.
26
Snapshot Isolation And Locking
 You can lock explicitly during snapshot
transaction if needed
 Use hint READCOMMITTEDLOCK
 Permits combination of versioning and
locking
 Equivalent of "SELECT FOR UPDATE"
 FOR UPDATE supported only in DECLARE
CURSOR in SQL Server
27
SQL Server vs. Oracle Transactions
 Oracle uses optimistic concurrency by default
 Uses the REDO log (transaction log) to implement
row versioning (save changes only)
 Read locks (pessimistic concurrency) can be
acquired explicitly only (SELECT FOR UPDATE)
SQL Server Isolation
Level
Read uncommitted
Oracle Isolation
Level
–
SQL Server
Oracle
Concurrency Concurrency
Pessimistic
Read committed snapshot Read committed
Optimistic
Read committed
Pessimistic
–
–
Optimistic
–
Repeatable read
(manual locks)
Pessimistic
Pessimistic
Snapshot
Serializable
Optimistic
Optimistic
Serializable
(manual locks)
Pessimistic
–
28
Summary
 Transaction Isolation with Locking (Pessimistic
concurrency)
 Data read as of exact point in time or locked
 Four transaction isolation levels
 READ UNCOMMITTED, READ COMMITTED,
REPEATABLE READ, SERIALIZABLE
 Accomplished by adjustable lock type/duration
 But...
 No statement level read consistency and multi
table read consistency within transaction
 No way to read old value of row being updated
29
Summary
 Transaction Isolation with Versioning
(Optimistic concurrency)
 Data read as of beginning of transaction
 Two transaction isolation levels
 SNAPSHOT and READ_COMMITTED_SNAPSHOT
 Statement level and multi-statement read
consistency
 But...
 Extra versions of data kept (extra I/O)
 Update conflicts possible - rollback if
snapshot is used
30
Optimizing Transactions Code in SQL
Server 2008
Questions?
31