DAT 459 SQL Server And Distributed Transactions Architecture

DAT 459 SQL Server And Distributed Transactions – Architecture And Internals Gert E. R. Drapers Software Architect Microsoft Corporation

Agenda “Distributed” Transactions 101 Architecture Overview Client Initiated Distributed Transactions Server Initiated Distributed Transactions XA and JDBC integration Oracle integration Monitoring Distributed Transactions

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

Roles Transaction System 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 under the scope of a transaction Uses a (durable) log for recovery Can Abort the transaction

Isolation Goal: Maximize concurrency without unwanted interactions Higher isolation provides better consistency Lower isolation provides better concurrency Concurrency anomalies Lost Updates (can overwrite uncommitted changes) Dirty Reads (can read uncommitted changes) Unrepeatable Reads (changing commits) Phantoms (insert/delete anomalies) DBMS defines its isolation rules Read (shared) locks and write (exclusive) locks Lock granularity: File, page, record, …

Degrees of isolation Whether or not these problems occur depends on a transaction’s degree 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 Dirty Reads? Yes No No No Unrepeatable Reads? Yes Yes No No Phantoms? Yes Yes No

Two-Phase Commit (2 PC) The two-phase commit protocol ensures that all transaction participants agree to either Commit the transaction and make its effects permanent Rollback the transaction and to undo its effects It provides the “Atomicity” property of “ACID” It is only needed when two or more parties participate in a transaction Two-phase commit must work despite process, system, or communication failures

Two-Phase Commit Transaction Manager Dispenses new transactions to clients Tracks the resource managers participating in the transaction Coordinates the transaction outcome Transaction Manager Resource Manager A subsystem that allows updates to transaction protected data SQL Server File System Resource Managers SQL Server Resource Manager

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

Write “Committed” record to TM log Committed Will Abort Will 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

MS-DTC Distributed Transaction Coordinator Windows NT Service Two Phase Commit (2 PC) and Recovery Infrastructure COM Interfaces (a. k. a. , DTC proxy) Used for App TM, TM RM communication Local TM, remote TM, TM failover Supported Resource Managers (RM) SQL Server, MSMQ, Biz. Talk, COM+ CRM, … Oracle, DB 2, …

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

Local Transactions All updates within a single DBMS Or other “resource manager” (RM) DBMS provides transaction demarcation Begin (start), end (commit), abort (rollback) DBMS provides transaction manager DBMS provide lock manager

Distributed Transactions may span multiple RMs Databases, Queues, etc. Distributed Transaction Manager Provides common transaction demarcation Coordinates commit/abort across RMs 2 Phase Commit (2 PC) Protocol RMs are “prepared” to either commit or abort RMs are notified of final outcome RM recovery Resource Managers Need to participate in 2 PC (Atomicity) Provide Isolation of its resources Provide Durability of its resources

A Distributed Transaction RM 2 -SQL Server RM 1 - MSMQ Data Your Tx COM+ Application RM Proxy Queue RM Proxy The Coordinating DTC Participating DTC Computer 2 RM Proxy RM 3 - ORACLE Computer 1 Data Participating DTC Computer 3

SQL Server And Dist Tx Client initiated distributed transaction Existing DTC transaction is passed to SQL Server using data access API ODBC SQL_ATTR_ENLIST_IN_DTC connection attribute OLE-DB ITransaction. Join: : Join. Transaction ADO Only implicit via MTS/COM+ System. Data. Sql. Client v 1. 0 only implicit via System. Enterprise. Services v 1. 1 Sql. Connection. Enlist. Distributed. Transaction DB-Library – dbenlisttrans MTS, COM+, System. Enterprise. Services

OLE Transactions Used by application programmers and resource manager developers ITransaction. Dispenser is used to initiate transactions ITransaction is used to commit or abort transactions

SQL Server And Dist. Tx… Server initiated BEGIN DISTRIBUTED TRANSACTION Linked Server call Extended Stored Procedures srv_getdtcaxct Application MS DTC SQL Server NT NT

MS-DTC SQL Server Settings sp_configure “remote proc trans”, {0|1} Server-wide option for automatically starting MS DTC transactions for server to server RPCs SET REMOTE_PROC_TRANSACTIONS ON|OFF Session-wide option for automatically starting MS DTC transactions for server to server RPCs sp_configure “remote conn timeout”, <n> Timeout on server to server RPCs before automatically dropping the connection Connection will only be closed if there is no MS DTC transaction active for the connection

Transact-SQL Explicit BEGIN DISTRIBUTED TRANSACTION This Transact-SQL call explicitly initiates an MS-DTC transaction If a stored procedure calls a remote stored procedure, both stored procedures are protected by the MS-DTC transaction BEGIN DISTRIBUTED TRANSACTION INSERT INTO ACCOUNTS VALUES (100, 20) EXEC RMTBRANCH. ACCOUNTS. DBO. DEPOSIT 100, 20 COMMIT TRANSACTION

Transact-SQL Implicit SQL Server can implicitly initiate an MS-DTC transaction when one is needed This is controlled using two SQL Server options Session-level option SET REMOTE_PROC_TRANSACTIONS ON Server-level option sp_configure “remote proc trans”, 1

X-Database Transactions Cross-database transactions on the same SQL Server Operations between two or more database result in a internal distributed transaction Does not require DTC

Client Two-Phase Commit DB-Library-based Should no longer be used Does not scale

XA Transactions can be coordinated by an XA compliant TP Monitor: Tuxedo, Top. End, Encina, etc. SQL Server can act Application as an XA compliant resource manager TP Monitor Other XA compliant XA databases can be MS DTC Oracle updated in the same OLE Tx Sybase transaction SQL Server NT Informix NT or UNIX

JDBC XA Integration Microsoft JDBC driver can enlist SQL Server in an Java originated JTA transaction By default not enabled, need to run INSTJDBC. SQL script to register xp_jdbc_open, xp_jdbc_close, xp_jdbc_start, xp_jdbc_end, xp_jdbc_prepare, xp_jdbc_commit, xp_jdbc_rollback, xp_jdbc_forget, xp_jdbc_recover Located in: C: Program FilesMicrosoft SQL Server 2000 Driver for JDBCSQLServer JTA Relies on XA switch library

Oracle Integration Oracle 7. x relies on MTXOCI. DLL Oracle 8 i, 9 i and 10 g should be using the Oracle provided DTC integration plumbing, named “Oracle Services for MTS” No need to use MTXOCI 8. DLL http: //otn. oracle. com/tech/windows/ora_m ts/htdocs/oramtsfov 9 i. html

Oracle Integration…

Transaction Monitoring SQL Server Trace/Profiler DTCTransaction event class (19) Event. Sub. Class, Binary. Data, Text. Data SQLTransaction event class (50) Event. Sub. Class, Transaction. ID, Object. Name, Text. Data Performance Monitor Distributed Transaction Coordinator Aborted Transactions/sec Active Transactions Maximum Committed Transactions/sec Force Aborted Transactions Force Committed Transactions In Doubt Transactions Response Time – Average Response Time – Maximum Response Time – Minimum Transactions/sec

Transaction Monitoring… DTC Trace %windir%system 32msdtctrace msdtcvtr. bat Requires TRACEFMT. EXE (Platform SDK) Component Services A. k. a. , COM+ Explorer MTS Spy/COM+ Spy Platform SDK

MS-DTC Log Sizing Rules: Log Record ~144 bytes (depends on number of RM’s involved in transaction) MS-DTC uses a circular transaction log Default Log Size is 4 MB DTC start rejecting new transactions when the current log space used is larger than the total Log Size / 8 How many concurrent transactions? 4 MB / 144 bytes = 30000 tx 30000 / 8 = ~ 3640 concurrent tx

Troubleshooting MS-DTC Setup (reinstall) Q 250987 – HOWTO: Reinstall MS DTC for a Nonclustered Windows NT 4. 0 Server Q 279786 – HOWTO: Reinstall MS DTC for a Nonclustered Windows 2000 Server Communication DTCPING (see Q 306843) Firewall Q 306843 HOWTO: Troubleshoot MS DTC Firewall Issues

SQL Server Resource. Mgr. ID HKEY_LOCAL_MACHINESOFTWARE MicrosoftMSSQLServer REG_SZ Resource. Mgr. ID GUID that UNIQUELY identifies this SQL Server instance Reason for SQL Server SYSPREP or images based installs to fail Needs to be duplicated on cluster nodes, otherwise transactions can not recover!

Information Q 306843 HOWTO: Troubleshoot MS DTC Firewall Issues Q 311846 INFO: Names and IP Addresses an MS DTC Client Must Have Q 260882 HOWTO: Set Up a Local MS DTC to Point to a Remote MS DTC Q 258926 INFO: Log Files Used by Distributed Transaction Coordinator Q 314966 MS DTC Leaks Handles If There Is Long String in TIP Transaction (Windows 2000 post service pack 2 hotfix rollup 19) Q 248291 How to Troubleshoot MSDTC Event ID 4209

Information… Q 286765 INFO: Two-Phase Commit is Not Supported over TCP/IP for DB 2 OLEDB Q 246038 HOWTO: Avoid COM/MTS Object Activation Delay on MSCS Q 243204 MSDTC Disaster Recovery Techniques Q 240038 INFO: The "msdtc -remove" Command Shows Errors Q 215520 INFO: COM+ and MTS Always Use SERIALIZABLE Transaction Isolation Q 295027 INF: Limited Support for Savepoint in Distributed Transactions Q 303287 BUG: DTC Transactions Fail When SQL Runs in Lightweight Pooling Q 195761 INF: FAQs – SQL Server 7. 0 – Failove. R

Please fill out a session evaluation on Comm. Net Q 1: Overall satisfaction with the session Q 2: Usefulness of the information Q 3: Presenter’s knowledge of the subject Q 4: Presenter’s presentation skills Q 5: Effectiveness of the presentation

Discussion E-Mail: gertd@microsoft. com http: //SQLDev. Net

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