Transaction%20Isolation%20Levels

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

3
Agenda

• Deadlocks
• Defined
• Preventing
• Transactions best practices

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

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.

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

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

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

9
SQL Server Locking Mechanism Described

• SQL Server decides lock type and scope
• User decides lock duration and there by isolation
  level of transaction

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.

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

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

13
Isolation Levels

• Read Committed
• Read Uncommitted
• Repeatable Read
• Serializable
• ??????? ?????? ????? ?"? ??????
• Set transaction isolation level

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

15
Read Uncommitted

• Uncommitted data can be read
• Exclusive lock held for the entire duration of
  the transaction.
• Shared lock held momentarily
• Prevents nothing

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

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.

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

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

20
Snapshot Isolation

• SQL Server 2000 implemented the ANSI standard
• Based on locking
• Pessimistic
• Can hurt concurrency
• Writes block readers

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

22
Snapshot reads

• transaction reads version of value corresponding
  to its start time

23
Snapshot writes
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

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

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

27
DeadLocks

• Defined
• Preventing

28
Types - Cycle DeadLock

A
B
A ???? ????? ????? ?? 2 ??? ???? ??? ?????? ?? B
B ???? ????? ????? ?? 1 ??? ???? ??? ?????? ?? A
1
2
B ????? Exclusive ?? 2
A ????? Exclusive ?? 1
29
Types - Conversion DeadLock
A ???? ???? ????? EXCLUCIVE ?? 1 ??? ???? ???
???? Shared ???? B
A
B
B ???? ???? ????? EXCLUCIVE ?? 1 ??? ???? ???
???? Shared ???? A
1
? A ? B ?? ????? Shared ?? 1
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)

31
Preventing

• Preventing Cycle DeadLoacks
• Using resources in the same order
• Preventing Conversion DeadLocks
• Using the Updlock hint
• Only one update lock can exist on a resource
• General recommendation
• Keep transactions as short as posible

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