transaction isolation levels
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
Random Logic l Forum .NET l 2006 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
Snapshot reads
transaction reads version of value corresponding to its start time
Account Report
1-3 5.821-9 1.18Total 7.00
Report reflects accountvalues in databasewhen report transactionstarted
Report Transaction
Post GL Transaction
1 Read 1-3
2 Write 1-3 2 Write 1-9
1-3:1 = 5.82
1-3:2 = 6.91 1-9:2 = 3.45
1-9:1 = 1.18
1 Read 1-9
VersionedAccountValues
Random Logic l Forum .NET l 2006 23
Snapshot writes
Transaction 1
Transaction 2
Write 1-3
1-3:2 = 7.24
VersionedAccountValues
Write 1-3
write fails
Read 1-3
1-3:1 = 6.91 serialization wouldhave deferred readto here
transaction committed
Random Logic l Forum .NET l 2006 24
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
Snapshot isolation types
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
1 2B מחזיקExclusive על
2A מחזיקExclusive על
1
B אבל 1 מנסה לרכוש נעילה על Aנכשל בשל הנעילה של
A אבל 2 מנסה לרכוש נעילה על Bנכשל בשל הנעילה של
Random Logic l Forum .NET l 2006 29
Types - Conversion DeadLock
A B
11 על Shared יש נעילת B ו Aל
A מנסה לקבל נעילת
EXCLUCIVE אבל 1 על Sharedנכשל בשל קיום
Bעבור B מנסה לקבל נעילת
EXCLUCIVE אבל 1 על Sharedנכשל בשל קיום
Aעבור
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 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
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