Honor that Transaction How to Design and Code Your Transactions

1. Honor that TransactionHow to Design and Code Your Transactions Vineet Gupta Evangelist – Database and Integration Microsoft Corp. http://spaces.msn.com/members/vineetgupta/

2. 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

3. Scenario: Bank Funds TransferClassical Single Machine Begin SQL Transaction Withdraw from savings Database Savings Deposit into checking Checking Commit SQL Transaction

4. Client-Server Machine 1 Machine 2 Begin SQL Transaction Database Savings Withdraw from savings Deposit into checking Commit SQL Transaction Checking

5. Three-Tier Components TM Log Withdraw Transfer Database Begin Tx Savings Commit Tx Checking Deposit

6. RM Distribution TM Log Withdraw Savings Transfer Begin Tx Commit Tx Deposit Checking

7. 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 Roles in Transaction Systems

8. Resource Manager

9. 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

10. 0 1 2 2.999 3 Degree Common Name Chaos Read Uncommitted Read Committed Repeatable Read Serializable A.K.A. Browse Cursor Stability 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 Isolation Levels • Perfect Isolation of Transactions Leads to Resource Contention • We actually use Degrees of Isolation

11. Tran2 (Select) Tran1 (Update) SQL-2000 locking S-Lock Blocked Row-1 Row-1 X-Lock

12. 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

13. SQL Server 2005 Isolation Levels Possible Anomalies

14. 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

15. 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

16. 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

17. 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

18. Transaction Manager

19. A Brief History DTC 1.0 4/1996 MTS 2.0 12/1997 COM+ 1.5 8/2001 WS-Transaction 8/2002 MTS 1.0 12/1996 COM+ 1.0 12/1999 System.EnterpriseServices 2/2002 TIP 7/1998 BTS 4/2000 1970 1980 1990 1996 1997 1998 1999 2000 2001 2002 CICS ~1968 Tuxedo 1984 Encina 1993 OMG OTS 1.1 11/1997 OMG OTS 1.2 5/2001 IMS 1973 XA (spec) 1991 JTA 4/1999 OASIS BTS 5/2002 Encompass Tandem ACMS Digital Mid 80’s Tandem TMF ???? JTS 12/1999 J2EE 12/1999

20. VB Object VB Object ADO Conn VB Object ADO Conn ADO Conn Local Transactions Application DBMS Activity Lock Manager Transaction Manager Client 1 Activity Client 2 Data Activity Client N

21. Computer 2 RM2 -SQL Server Computer 1 RM1 - MSMQ Participating DTC Computer 3 The Coordinating DTC RM3 - ORACLE Participating DTC Distributed transaction Data Your Tx COM+ Application RM Proxy Queue RM Proxy RM Proxy Data

22. Two-Phase Commit Resource Manager Transaction Manager Prepare Prepare and Force write “Prepared” record to RM log Prepared Force write “Commit” record to TM log Commit Lazy write “Committed” record to RM log and Release locks Committed Lazy write “Committed” record to TM log

23. Two Phase Commit – Failure Resource Manager Transaction Manager Abort on Failure Prepare Abort on Failure Prepare & Write “Prepared” record to RM log Prepared Will Abort Write “Commit” record to TM log Indoubt on Failure Commit Will Commit Write “Committed” record to RM log & Release locks Commit Committed Commit Write “Committed” record to TM log

24. API OLE Transactions DTC Proxy COM Interfaces App/TM, TM/RM, etc Local or Remote TM API and config Protocols OLE Transactions XA, TIP, LU6.2 Resource Managers SQL Server, MSMQ DB2, Oracle, … TodayMS-DTC

25. TodayCOM+ 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

26. Transaction Sub1 Sub1 & Sub 2 = Required or Supported Root Client Root Required or Requires New Sub2 TodayTransaction Example • Transaction automatically created • Connections automatically enlisted • Outcome automatically coordinated

27. TodayTransactions 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

28. TodaySystem.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 } }

29. Tomorrow

30. New Architecture Enterprise Services System.Transaction Lightweight Transaction Manager (LTM) Resource Manager (SQL) Cross App Domain Cross Computer DTC

31. 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

32. Application Usage Example Unify database and collection error handling: ImportsSystem.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

33. Architecture Enterprise Services Indigo Service System.Transaction Lightweight Transaction Manager (LTM) Resource Manager (SQL) Cross App Domain Cross Computer DTC

34. Indigo TransactionService 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

35. Beyond Indigo • Transacted Collection Classes • Longhorn Integration • Kernel Transaction Manager • Transacted File System • Transacted Registry

36. Questions?

37. 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

38. Your Feedbackis Important! Please Fill Out the feedback form

39. © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.