Transcript Why Transactions?

Honor that Transaction
How to Design and Code Your
Transactions
Vineet Gupta
Evangelist – Database and Integration
Microsoft Corp.
http://spaces.msn.com/members/vineetgupta/
Why Transactions?
Correctness (programming model)
ACID properties
Atomicity
All changes happen or nothing happens
Consistency
Data is transformed from one correct state to another
Isolation
Concurrent updaters are prevented from interfering with
one another
Durability
Committed changes remain permanent despite failures
Scenario: Bank Funds Transfer
Classical
Single Machine
Begin SQL Transaction
Withdraw from savings
Savings
Deposit into checking
Commit SQL Transaction
Database
Checking
Client-Server
Machine 1
Begin SQL Transaction
Withdraw from savings
Deposit into checking
Commit SQL Transaction
Machine 2
Savings
Database
Checking
Three-Tier Components
TM
Log
Withdraw
Begin Tx
Savings
Transfer
Commit Tx
Database
Checking
Deposit
RM Distribution
TM
Log
Withdraw
Savings
Deposit
Checking
Begin Tx
Transfer
Commit Tx
Roles in Transaction Systems
Client Application
•Begins the transaction,
•Makes changes to resources,
•Can Commit or Abort the transaction
Transaction Manager
•Transaction demarcation, participants, and state
•Prepare result is made durable
•Can Abort the transaction
Resource Manager
•Controls changes to resources
•Uses a (durable) log for recovery
•Can Abort the transaction
Resource Manager
Resource Management
ACID Transaction Design Requirements
Atomicity Consistency Isolation
Durability
Isolation: A transaction either
Sees data in the state it was in before
another concurrent transaction modified it,
Or it sees the data after the second
transaction has completed,
But it does not see an intermediate state
Isolation Levels
Perfect Isolation of Transactions Leads
to Resource Contention
We actually use Degrees of Isolation
Degree
0
1
2
2.999
3
Common Name
Chaos
Read
Uncommitted
Read
Committed
Cursor
Stability
Repeatable
Read
Serializable
A.K.A.
Browse
Isolated
Lost Updates?
Yes
No
No
No
No
Dirty Reads?
Yes
Yes
No
No
No
Unrepeatable Reads?
Yes
Yes
Yes
No
No
Phantoms?
Yes
Yes
Yes
Yes
No
SQL-2000 locking
Tran2 (Select)
Tran1 (Update)
X-Lock
Row-1
S-Lock Blocked
Microsoft® SQL Server™ 2005
Isolation Level Extensions
Read-Committed Snapshot Isolation
New flavor of read committed (non-locking)
Statement-level Snapshot Isolation
See most recent committed value of data as of the
start of the statement
Snapshot Isolation
New isolation level
Transaction-level Snapshot Isolation
See most recent committed value of data as of the
start of the transaction
SQL Server 2005 Isolation Levels
Possible Anomalies
Isolation
Levels
Dirty
Read
NonRepeatable
Read
Phantoms
Update
Conflict
Yes
Yes
Yes
No
No
No
Yes
Yes
Yes
Yes
No
No
Pessimistic
Optimistic
Repeatable
Read
No
No
Yes
No
Pessimistic
Snapshot
No
No
No
Yes
Optimistic
Serializable
No
No
No
No
Pessimistic
Read
Uncommitted
Concurrency
Control
Read
Committed
1 Locking
2 Snapshot
Read-Committed Snapshot
New “flavor” of read committed
Requires a database-level setting
Readers see committed values as of
beginning of statement
Writers do not block Readers
Readers do not block Writers
Writers do block Writers
Can greatly reduce locking / deadlocking
without changing applications
Snapshot Isolation
Represents a new isolation level
Transitionally consistent database as of
the beginning of the transaction
Requires session-level setting
Readers do not lock data
Reduces deadlocks
But at a cost of write-write conflicts
Snapshot Isolation state is ON by default for
master and msdb
Isolation Levels: In Summary
READ UNCOMMITTED (Level 0)
“Dirty Reads” – An option ONLY for readers
Any data (even that which is in-flight/locked) can
be viewed
READ COMMITTED (Level 1 – Default)
Only committed changes are visible
Data in an intermediate state cannot be accessed
READ COMMITTED SNAPSHOT (RCSI)
Statement-level read consistency
New non-blocking, non-locking (ex. SCH_S),
version-based Level 1
Isolation Levels: In Summary (cont’d)
REPEATABLE READS (Level 2)
All reads are consistent for the life of a transaction
Shared locks are NOT released after the data is
processed
Does not protect entire set (i.e. phantoms may occur)
SERIALIZEABLE (Level 3)
All reads are consistent for the life of a transaction
Avoids phantoms – no new records
Snapshot Isolation – 2005
Transaction-Level consistency using snapshot
New non-blocking, non-locking, version-based
transactions
Transaction Manager
A Brief History
DTC 1.0
4/1996
MTS 2.0
12/1997
COM+ 1.5
8/2001
MTS 1.0
12/1996
COM+ 1.0
12/1999
TIP
7/1998
1970
1980
CICS
~1968
1990
Tuxedo
1984
IMS
1973
Encina
1993
1996
1997
System.EnterpriseServices
2/2002
BTS
4/2000
1999
2000
OMG OTS 1.1
11/1997
XA (spec)
1991
Encompass Tandem
ACMS Digital
Mid 80’s
1998
2001
2002
OMG OTS 1.2
5/2001
JTA
4/1999
Tandem TMF
????
WS-Transaction
8/2002
JTS
12/1999
J2EE
12/1999
OASIS BTS
5/2002
Local Transactions
Client 1
VB
Object
ADO
Conn
Lock
Manager
Transaction
Manager
Activity
Client 2
ADO
Conn
VB
Object
Activity
Client N
Data
ADO
Conn
VB
Object
Activity
DBMS
Application
Distributed transaction
Computer 2
RM2 -SQL Server
Computer 1
Data
RM1 - MSMQ
Your Tx COM+ Application
RM Proxy
Queue
RM Proxy
Participating DTC
RM Proxy
Computer 3
The Coordinating DTC
RM3 - ORACLE
Data
Participating DTC
Two-Phase Commit
Transaction
Manager
Resource
Manager
Prepare
Prepared
Force write “Commit”
record to TM log
Lazy write “Committed”
record to TM log
Prepare and Force write
“Prepared” record to RM log
Commit
Committed
Lazy write “Committed” record
to RM log and Release locks
Write “Committed”
record to TM log
Committed
Will
Abort
Will
Commit
Commit
Commit
Indoubt
on Failure
Prepared
Write “Commit”
record to TM log
Resource
Manager
Abort on
Failure
Prepare
Commit
Transaction
Manager
Abort on Failure
Two Phase Commit – Failure
Prepare & Write
“Prepared” record to
RM log
Write “Committed”
record to RM log &
Release locks
Today
MS-DTC
API
Protocols
OLE Transactions
DTC Proxy
COM Interfaces
App/TM, TM/RM, etc
Local or Remote TM
API and config
OLE Transactions
XA, TIP, LU6.2
Resource Managers
SQL Server, MSMQ
DB2, Oracle, …
Today
COM+ Transactions
High Level Programming Model supporting
Declarative transaction model
Provides execution environment and full
support of all transaction features (e.g.
commit coordination, isolation level, timeout,
etc.)
Support for Compensating Resource
Managers (CRM)
Evolution of MTS (think MTS 3.0)
Building Block for .Net Transaction Model
Today
Transaction Example
Transaction automatically created
Connections automatically enlisted
Outcome automatically coordinated
Transaction
Sub1
Root
Client
Sub1 & Sub 2 =
Required or
Supported
Root
Required or
Requires New
Sub2
Today
Transactions in .Net
System.EnterpriseServices namespace
provides the programming model
All COM+ features exposed via
attributes
All .Net language features available
Provides full integration with the .Net
runtime integration
Full interop with existing COM+
components
Today
System.EnterpriseServices Example
using System;
using System.Data.SqlClient;
using System.EnterpriseServices;
[assembly : ApplicationName("TxDemo")]
[Transaction(TransactionOption.RequiresNew,
Isolation = TransactionIsolationLevel.ReadCommitted,
Timeout = 60)]
public class Account : ServicedComponent
{
[AutoComplete(true)]
public void Credit()
{
// do some work
}
}
Tomorrow
New Architecture
Enterprise
Services
System.Transaction
Lightweight Transaction
Manager
(LTM)
Resource
Manager
(SQL)
Cross App Domain
Cross Computer
DTC
Lightweight Transaction Manager (LTM)
Common Starting Point for Transactions
Remove any actual and perceived initial penalties for
use
LTM is a full-fledged transaction manager for volatile
resources
Fast Transaction creation
Minimum performance overhead when using volatile resource
in the same app-domain
Promotable Single Phase Enlistment (PSPE) support to
remove overhead when using a single resource manager
In this case, the RM manages the transaction
Single log operation
Supported by SQL 2005
Pay as you go – Dynamic Promotions
Application Usage Example
Unify database and collection error handling:
Imports System.Transaction
Dim activeMembers As TransactedHashTable()
Public Sub AddMembership (ByVal MemberName As String, _
ByVal Organization As String)
Using ts As New TransactionScope()
activeMembers.Add (MemberName, Organization)
…
sqlCommand.ExecuteNonQuery (… add member insert…)
sqlCommand.ExecuteNonQuery (… add organization insert…)
…
AppBizLogic = new AppBizLogic
AppBizLogic.UpdateState(… state …)
…
ts.Consistent = True
End Using
End Sub
Architecture
Enterprise
Services
Indigo
Service
System.Transaction
Lightweight Transaction
Manager
(LTM)
Resource
Manager
(SQL)
Cross App Domain
Cross Computer
DTC
Indigo Transaction
Service Model
Major Differences from
COM+/EnterpriseServices
Transaction context does not flow by
default
Transaction flow is separate from
transaction usage
Transaction context is set at method
invocation, and not at object creation time
Beyond Indigo
Transacted Collection Classes
Longhorn Integration
Kernel Transaction Manager
Transacted File System
Transacted Registry
Questions?
Further Reading
Principles of Transaction Processing
Bernstein and Newcomer
Transactional COM+
Tim Ewald
SQL Server Architecture and Internals
Ken Henderson
SQL Server 2005 for Developers
Neils Burglend and Bob Beauchmen
Programming Indigo
David Pallman
Your Feedback
is Important!
Please Fill Out the
feedback form
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.