honor that transaction how to design and code your transactions vineet gupta evangelist – database...
DESCRIPTION
Scenario: Bank Funds Transfer Classical Savings Checking Withdraw from savings Deposit into checking Begin SQL Transaction Commit SQL Transaction Database Single MachineTRANSCRIPT
Honor that TransactionHonor that TransactionHow to Design and Code Your How to Design and Code Your TransactionsTransactions
Vineet GuptaVineet GuptaEvangelist – Database and IntegrationEvangelist – Database and IntegrationMicrosoft Corp.Microsoft Corp.
http://spaces.msn.com/members/vineetgupta/http://spaces.msn.com/members/vineetgupta/
Why Transactions?Why Transactions?Correctness (programming model)Correctness (programming model)ACID propertiesACID properties
AtomicityAtomicityAll changes happen or nothing happensAll changes happen or nothing happens
ConsistencyConsistencyData is transformed from one correct state to anotherData is transformed from one correct state to another
IsolationIsolationConcurrent updaters are prevented from interfering with Concurrent updaters are prevented from interfering with one anotherone another
DurabilityDurabilityCommitted changes remain permanent despite failuresCommitted changes remain permanent despite failures
Scenario: Bank Funds TransferScenario: Bank Funds TransferClassicalClassical
Savings
Checking
Withdraw from savings
Deposit into checking
Begin SQL Transaction
Commit SQL Transaction
Database
Single Machine
Client-ServerClient-Server
Savings
Checking
Withdraw from savings
Deposit into checking
Begin SQL Transaction
Commit SQL TransactionDatabase
Machine 1 Machine 2
Three-Tier ComponentsThree-Tier Components
Savings
Checking
Database
Withdraw
Deposit
Transfer
TM
Begin Tx
Commit Tx
Log
RM DistributionRM Distribution
Savings
Checking
Withdraw
Deposit
Transfer
TM
Begin Tx
Commit Tx
Log
Roles in Transaction SystemsRoles in Transaction Systems
Transaction ManagerTransaction Manager•Transaction demarcation, participants, and stateTransaction demarcation, participants, and state•Prepare result is made durablePrepare result is made durable•Can Abort the transactionCan Abort the transaction
Resource ManagerResource Manager•Controls changes to resourcesControls changes to resources•Uses a (durable) log for recoveryUses a (durable) log for recovery•Can Abort the transactionCan Abort the transaction
Client ApplicationClient Application•Begins the transaction,Begins the transaction,•Makes changes to resources, Makes changes to resources, •Can Commit or Abort the transactionCan Commit or Abort the transaction
Resource ManagerResource Manager
Resource ManagementResource ManagementACID Transaction Design RequirementsACID Transaction Design Requirements
Atomicity Consistency Atomicity Consistency IsolationIsolation DurabilityDurability
Isolation:Isolation: A transaction either A transaction either Sees data in the state it was in Sees data in the state it was in beforebefore another concurrent transaction modified it, another concurrent transaction modified it, Or it sees the data Or it sees the data afterafter the second the second transaction has completed, transaction has completed, But it But it does notdoes not see an see an intermediateintermediate state state
Isolation LevelsIsolation LevelsPerfect Isolation of Transactions Leads Perfect Isolation of Transactions Leads to Resource Contentionto Resource ContentionWe actually use We actually use Degrees of IsolationDegrees of Isolation
Common NameCommon Name
DegreeDegree
A.K.A.A.K.A.
Lost Updates?Lost Updates?
Dirty Reads?Dirty Reads?
Unrepeatable Reads?Unrepeatable Reads?
Phantoms?Phantoms?
00
ChaosChaos
YesYes
YesYes
YesYes
YesYes
11
Read Read UncommittedUncommitted
BrowseBrowse
YesYes
NoNo
YesYes
YesYes
22
Read Read CommittedCommitted
Cursor Cursor StabilityStability
NoNo
NoNo
YesYes
YesYes
33
SerializableSerializable
IsolatedIsolated
NoNo
NoNo
NoNo
NoNo
2.9992.999
RepeatableRepeatableReadRead
NoNo
NoNo
NoNo
YesYes
SQL-2000 lockingSQL-2000 locking
Row-1Row-1
Tran2 (Select)Tran2 (Select)Tran1 (Update)Tran1 (Update)
X-LockX-Lock S-LockS-Lock BlockedBlockedRow-1Row-1
Microsoft® SQL Server™ 2005 Microsoft® SQL Server™ 2005 Isolation Level ExtensionsIsolation Level Extensions
Read-Committed Snapshot IsolationRead-Committed Snapshot IsolationNew flavor of read committed (non-locking)New flavor of read committed (non-locking)Statement-level Snapshot IsolationStatement-level Snapshot IsolationSee most recent committed value of data as of the See most recent committed value of data as of the start of the start of the statementstatement
Snapshot IsolationSnapshot IsolationNew isolation levelNew isolation levelTransaction-level Snapshot IsolationTransaction-level Snapshot IsolationSee most recent committed value of data as of the See most recent committed value of data as of the start of the start of the transactiontransaction
SQL Server 2005 Isolation Levels SQL Server 2005 Isolation Levels
Isolation LevelsIsolation Levels Dirty Dirty ReadRead
Non-Non-Repeatable Repeatable
ReadReadPhantomsPhantoms Update Update
ConflictConflictConcurrency Concurrency
ControlControl
Read Read UncommittedUncommitted YesYes YesYes YesYes NoNo
Read Read CommittedCommitted
1 Locking1 Locking22 Snapshot Snapshot
NoNoNoNo
YesYesYesYes
YesYesYesYes
NoNoNoNo
PessimisticPessimisticOptimisticOptimistic
Repeatable Repeatable ReadRead NoNo NoNo YesYes NoNo PessimisticPessimistic
SnapshotSnapshot NoNo NoNo NoNo Yes Yes OptimisticOptimistic
SerializableSerializable NoNo NoNo NoNo NoNo PessimisticPessimistic
Possible AnomaliesPossible Anomalies
Read-Committed SnapshotRead-Committed Snapshot
New “flavor” of read committedNew “flavor” of read committedRequires a database-level settingRequires a database-level settingReaders see committed values as of Readers see committed values as of beginning of statementbeginning of statement
Writers do not block ReadersWriters do not block ReadersReaders do not block WritersReaders do not block WritersWriters Writers dodo block Writers block Writers
Can greatly reduce locking / deadlocking Can greatly reduce locking / deadlocking without changing applicationswithout changing applications
Snapshot IsolationSnapshot IsolationRepresents a new isolation levelRepresents a new isolation levelTransitionally consistent database as of Transitionally consistent database as of the beginning of the transactionthe beginning of the transaction
Requires session-level settingRequires session-level settingReaders do not lock data Readers do not lock data Reduces deadlocksReduces deadlocks
But at a cost of write-write conflictsBut at a cost of write-write conflictsSnapshot Isolation state is ON by default for Snapshot Isolation state is ON by default for mastermaster and and msdbmsdb
Isolation Levels: In SummaryIsolation Levels: In Summary
READ UNCOMMITTED (Level 0)READ UNCOMMITTED (Level 0)““Dirty Reads” – An option ONLY for readersDirty Reads” – An option ONLY for readersAny data (even that which is in-flight/locked) can Any data (even that which is in-flight/locked) can be viewedbe viewed
READ COMMITTED (Level 1 – Default)READ COMMITTED (Level 1 – Default)Only committed changes are visibleOnly committed changes are visibleData in an intermediate state cannot be accessedData in an intermediate state cannot be accessed
READ COMMITTED SNAPSHOT (RCSI)READ COMMITTED SNAPSHOT (RCSI)Statement-level read consistencyStatement-level read consistencyNew non-blocking, non-locking (ex. SCH_S), New non-blocking, non-locking (ex. SCH_S), version-based Level 1version-based Level 1
Isolation Levels: In Summary (cont’d)Isolation Levels: In Summary (cont’d)
REPEATABLE READS (Level 2)REPEATABLE READS (Level 2)All reads are consistent for the life of a transactionAll reads are consistent for the life of a transactionShared locks are NOT released after the data is Shared locks are NOT released after the data is processedprocessedDoes not protect entire set (i.e. phantoms may occur)Does not protect entire set (i.e. phantoms may occur)
SERIALIZEABLE (Level 3)SERIALIZEABLE (Level 3)All reads are consistent for the life of a transactionAll reads are consistent for the life of a transactionAvoids phantoms – no new recordsAvoids phantoms – no new records
Snapshot Isolation – 2005Snapshot Isolation – 2005 Transaction-Level consistency using snapshot Transaction-Level consistency using snapshot New non-blocking, non-locking, version-based New non-blocking, non-locking, version-based transactionstransactions
Transaction ManagerTransaction Manager
A Brief HistoryA Brief History
MTS 1.012/1996
DTC 1.04/1996
COM+ 1.012/1999
COM+ 1.58/2001
System.EnterpriseServices2/2002
WS-Transaction8/2002
MTS 2.012/1997
1996 1997 1998 1999 2000 2001 20021980 1990
IMS1973
CICS~1968
Encompass TandemACMS Digital
Mid 80’s
Tuxedo1984
Tandem TMF????
Encina1993
JTS12/1999
OMG OTS 1.25/2001
OASIS BTS5/2002
JTA4/1999
J2EE12/1999
BTS4/2000
1970
TIP 7/1998
OMG OTS 1.111/1997
XA (spec) 1991
Local TransactionsLocal Transactions
DBMSDBMS ApplicationApplication
ActivityActivity
ActivityActivity
ActivityActivity
ADOADOConnConn
Client 1Client 1
Client 2Client 2
Client NClient N
Transaction Transaction ManagerManager
LockLockManagerManager
DataData
VB VB ObjectObject
ADOADOConnConn
VB VB ObjectObject
ADOADOConnConn
VB VB ObjectObject
Distributed transactionDistributed transaction
Computer 3Computer 3
RM3 - ORACLERM3 - ORACLE
Participating DTCParticipating DTC
Computer 2Computer 2
RM2 -SQL ServerRM2 -SQL Server
Participating DTCParticipating DTC
Computer 1Computer 1
The Coordinating DTC The Coordinating DTC
RM1 - RM1 - MSMQMSMQDataData
DataData
Your Tx COM+ ApplicationYour Tx COM+ Application
QueueQueue
RM ProxyRM Proxy
RM ProxyRM ProxyRM ProxyRM Proxy
Two-Phase CommitTwo-Phase Commit
TransactionManager
ResourceManager
Prepare
Commit
Prepare and Force write “Prepared” record to RM log
Prepared
Force write “Commit” record to TM log
Lazy write “Committed” record to RM log and Release locks
CommittedLazy write “Committed”
record to TM log
Two Phase Commit – FailureTwo Phase Commit – Failure
TransactionManager
ResourceManager
Prepare
Commit
Prepare & Write “Prepared” record to
RM logPrepared
Write “Commit” record to TM log
Write “Committed” record to RM log &
Release locksCommitted
Write “Committed” record to TM log
Abo
rt o
n Fa
ilure
Com
mit
Abo
rt o
nFa
ilure
Indo
ubt
on F
ailu
reC
omm
it
Will
Abo
rtW
illC
omm
it
TodayTodayMS-DTCMS-DTC
APIAPIOLE TransactionsOLE Transactions
DTC ProxyDTC ProxyCOM InterfacesCOM Interfaces
App/TM, TM/RM, etcApp/TM, TM/RM, etcLocal or Remote TMLocal or Remote TM
API and configAPI and config
ProtocolsProtocolsOLE TransactionsOLE TransactionsXA, TIP, LU6.2XA, TIP, LU6.2
Resource ManagersResource ManagersSQL Server, MSMQSQL Server, MSMQDB2, Oracle, …DB2, Oracle, …
TodayTodayCOM+ TransactionsCOM+ Transactions
High Level Programming Model supporting High Level Programming Model supporting Declarative transaction modelDeclarative transaction modelProvides execution environment and full Provides execution environment and full support of all transaction features (e.g. support of all transaction features (e.g. commit coordination, isolation level, timeout, commit coordination, isolation level, timeout, etc.)etc.)Support for Compensating Resource Support for Compensating Resource Managers (CRM)Managers (CRM)Evolution of MTS (think MTS 3.0)Evolution of MTS (think MTS 3.0)Building Block for .Net Transaction ModelBuilding Block for .Net Transaction Model
TodayTodayTransaction ExampleTransaction Example
Transaction automatically createdTransaction automatically createdConnections automatically enlistedConnections automatically enlistedOutcome automatically coordinatedOutcome automatically coordinated
Root
Sub2Required or Requires New
RootClient
Sub1Sub1 & Sub 2 = Required orSupported
TransactionTransaction
TodayTodayTransactions in .NetTransactions in .Net
System.EnterpriseServices namespace System.EnterpriseServices namespace provides the programming modelprovides the programming modelAll COM+ features exposed via All COM+ features exposed via attributesattributesAll .Net language features availableAll .Net language features availableProvides full integration with the .Net Provides full integration with the .Net runtime integrationruntime integrationFull interop with existing COM+ Full interop with existing COM+ componentscomponents
TodayTodaySystem.EnterpriseServices ExampleSystem.EnterpriseServices Exampleusing System;using System;using System.Data.SqlClient;using System.Data.SqlClient;using System.EnterpriseServices;using System.EnterpriseServices;[assembly : ApplicationName("TxDemo")][assembly : ApplicationName("TxDemo")] [Transaction(TransactionOption.RequiresNew, [Transaction(TransactionOption.RequiresNew,
Isolation = TransactionIsolationLevel.ReadCommitted, Isolation = TransactionIsolationLevel.ReadCommitted, Timeout = 60)]Timeout = 60)]
public class Account : ServicedComponentpublic class Account : ServicedComponent{{ [AutoComplete(true)][AutoComplete(true)] public void Credit()public void Credit() {{ // do some work// do some work }}}}
TomorrowTomorrow
New ArchitectureNew Architecture
System.Transaction
Lightweight Transaction Manager
(LTM)
DTC
Resource Manager
(SQL)
EnterpriseServices
Cross App DomainCross Computer
Lightweight Transaction Manager (LTM)Lightweight Transaction Manager (LTM)
Common Starting Point for TransactionsCommon Starting Point for TransactionsRemove any actual and perceived initial penalties for Remove any actual and perceived initial penalties for useuse
LTM is a full-fledged transaction manager for volatile LTM is a full-fledged transaction manager for volatile resourcesresources
Fast Transaction creationFast Transaction creationMinimum performance overhead when using volatile resource in Minimum performance overhead when using volatile resource in the same app-domainthe same app-domain
Promotable Single Phase Enlistment (PSPE) support to Promotable Single Phase Enlistment (PSPE) support to remove overhead when using a single resource managerremove overhead when using a single resource manager
In this case, the RM manages the transactionIn this case, the RM manages the transactionSingle log operationSingle log operation
Supported by SQL 2005Supported by SQL 2005Pay as you go – Dynamic PromotionsPay as you go – Dynamic Promotions
Application Usage ExampleApplication Usage ExampleUnify database and collection error handling:Unify database and collection error handling:
ImportsImports System.TransactionSystem.Transaction DimDim activeMembers activeMembers AsAs TransactedHashTable() TransactedHashTable()
Public SubPublic Sub AddMembership AddMembership ( (ByValByVal MemberName MemberName As StringAs String, _, _ ByValByVal Organization Organization As StringAs String))
UsingUsing ts ts As NewAs New TransactionScope() TransactionScope()
activeMembers.Add (MemberName, Organization)activeMembers.Add (MemberName, Organization) … … sqlCommand.ExecuteNonQuery (… add member insert…)sqlCommand.ExecuteNonQuery (… add member insert…) sqlCommand.ExecuteNonQuery (… add organization insert…)sqlCommand.ExecuteNonQuery (… add organization insert…) … … AppBizLogic = new AppBizLogicAppBizLogic = new AppBizLogic AppBizLogic.UpdateState(… state …)AppBizLogic.UpdateState(… state …) … … ts.Consistent = ts.Consistent = TrueTrue End UsingEnd Using
End SubEnd Sub
ArchitectureArchitecture
System.Transaction
Lightweight Transaction Manager
(LTM)
DTC
Resource Manager
(SQL)
EnterpriseServices
Cross App DomainCross Computer
IndigoService
Indigo TransactionIndigo TransactionService ModelService Model
Major Differences from Major Differences from COM+/EnterpriseServicesCOM+/EnterpriseServices
Transaction context does not flow by Transaction context does not flow by defaultdefaultTransaction flow is separate from Transaction flow is separate from transaction usagetransaction usageTransaction context is set at method Transaction context is set at method invocation, and not at object creation timeinvocation, and not at object creation time
Beyond IndigoBeyond IndigoTransacted Collection ClassesTransacted Collection ClassesLonghorn IntegrationLonghorn Integration
Kernel Transaction ManagerKernel Transaction ManagerTransacted File SystemTransacted File SystemTransacted RegistryTransacted Registry
Questions?Questions?
Further ReadingFurther ReadingPrinciples of Transaction ProcessingPrinciples of Transaction Processing
Bernstein and NewcomerBernstein and NewcomerTransactional COM+Transactional COM+
Tim EwaldTim EwaldSQL Server Architecture and InternalsSQL Server Architecture and Internals
Ken HendersonKen HendersonSQL Server 2005 for DevelopersSQL Server 2005 for Developers
Neils Burglend and Bob BeauchmenNeils Burglend and Bob BeauchmenProgramming IndigoProgramming Indigo
David PallmanDavid Pallman
Your FeedbackYour Feedbackis Important!is Important!Please Fill Out the Please Fill Out the feedback formfeedback form
© 2005 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.