[Figures are not included in this sample chapter]

RJDW:MS Transaction Server 2.0



- 3 -

Working with Database Transactions

Most client/server designs incorporate transaction-processing elements to manage
atomic operations and concurrency. This chapter introduces the Microsoft Transaction
Server (MTS) way of managing transactions, and how these methods relate to conventional
RDBMS-based transaction management methods. The preparation section that follows
briefly introduces the topic of transactions.

Some tasks in this chapter assume an understanding of SQL and are implemented
using SQL Server's Transact-SQL language. Although you need SQL Server to perform
the tasks as written in the chapter, you don't have to be familiar with Transact-SQL
to understand them. You can easily modify the SQL source to execute on any ANSI SQL-
compliant RDBMS.

The programming tasks serve as a brief introduction to MTS component transaction
processing. The Visual Basic samples in the chapter illustrate concepts and do not
require you to actually compile and build the components. For details on building
MTS components, refer to Part III, "Microsoft Transaction Server Components."

---

NOTE You must install the Beckwith database samples to complete this chapter's
tasks. Refer to Appendix C, "Beckwith Component Reference," for instructions
on installing the samples.

---

Preparation Understanding Database Transactions A transaction is a collection
of database operations grouped into a logical unit of work that is either completely
executed or completely abandoned. In a sense, transactions are analogous to a contract
between two or more parties. The contract is negotiated--the parties either come
to a consensus and accept the contract, or they don't reach an agreement and reject
the contract. A transaction is an all or nothing proposition--either it completes
totally or it does not complete at all.

Most client/server systems of modest size rely exclusively on the RDBMS-provided
transaction managers to implement transactions. RDBMS Application Programming Interfaces
(APIs) enable applications to incorporate transaction semantics with limited scope.
Typically, these transaction managers enable applications to issue transactions spanning
only the current data context (usually the active database).

Transaction monitors (TMs) are a class of transaction-processing applications
that were originally designed to manage very large numbers of simultaneous transactions
against mainframe database management systems. Compared to conventional client/server
RDBMSs, TMs are more robust and handle more simultaneous transactions. MTS brings
this robustness and scalability to the client/server arena.

A transaction should function as a consistent and reliable unit of work for the
database system and must guarantee the ACID properties when executing in a consistent
database. (ACID stands for atomicity, consistency, isolation, and durability; see
Table 3.1.) An ACID transaction guarantees that the transaction completely
finished the actions on the database (atomicity), that the database was left in consistent
form (consistency), that the transaction acted alone on the database (isolation),
and that the changes made to the data were durable (durability).

Table 3.1.  ACID transaction attributes.

Using SQL Statements to Begin a Transaction

The Transact-SQL statement BEGIN TRANSACTION denotes the beginning
of an explicit transaction. To begin a transaction in a .sql file or when using the
SQL Server SQL Query Tool (shown in Figure 3.1), use the following statement:

BEGIN TRANSACTION

For nested transactions, you can optionally assign a name to the outermost BEGIN
TRANSACTION statement.



Figure 3.1. Explicitly beginning a transaction.

Managing Transactions in MTS Components

During certain actions, components may need to communicate with MTS. Most of this
communication occurs through an object's companion context object; see Chapter 10,
"Understanding Microsoft Transaction Server Components," for details. Every
instance of a component under MTS control has an associated context object. This
context object holds information about the requested object and serves as the object's
communication link to MTS. MTS manages transactions through this context object.

To obtain a reference to this object within a Visual Basic MTS component, call
the GetObjectContext method:

Dim 
objContext 
As ObjectContext


 
  
Set objContext = GetObjectContext()

For details on using the context object in your Visual Basic components, see Chapter
11, "Implementing Components in Visual Basic." Task Committing a Transaction
A COMMIT statement marks a successful completion of a transaction. The statement
contains the processing to guarantee proper updating of the database and indicates
the correct and atomic completion of a transaction. In this task you successfully
complete transactions by using commit.

Using SQL Statements to Commit a Transaction

The Transact-SQL statement COMMIT TRANSACTION designates the successful
end of a transaction. To commit a transaction in a .sql file or when using the SQL
Server SQL Query Tool, use the following statement:

COMMIT TRANSACTION

For nested transactions, you can optionally assign a name to the outermost COMMIT
TRANSACTION statement.

The Beckwith HumanResources database contains employee and department information.
The Department table in Figure 3.2 consists of an identifier and description
for each university department.



Figure 3.2. The Beckwith HR Department table.



Use SQL Server ISQL to insert and commit a new department into the Department
table:

1. Start the ISQL application.



2. Select the HumanResources database from the DB drop-list.



3. Type the following lines in the Query window:

      BEGIN TRANSACTION


 
   INSERT INTO Department VALUES (`MED','Medicine')


 
   COMMIT TRANSACTION

4. Click the Execute Query button in the ISQL window.

The COMMIT TRANSACTION statement completes the transaction and permits
the change to persist in the Human Resources database, as shown in Figure 3.3.

Programmatically Committing Transactions in MTS Components

When a transaction succeeds, the component's context object commits the work and
makes the changes permanent. You must obtain a reference to the context object (refer
to the previous section, "Managing Transactions in MTS Components") and
then call the object's SetComplete method.



Figure 3.3. The Beckwith HR Department table after an INSERT
transaction.



The code fragment in Listing 3.1 uses the SetComplete method to commit a
transaction.

Listing 3.1.  Completing a transaction.

  Dim objContext As ObjectContext


 
  Set objContext = GetObjectContext()


 
  ` Perform work


 
  ` success - complete the transaction


 
  objContext.SetComplete

Task Aborting a Transaction An abort marks the unsuccessful termination of a transaction.
Commonly referred to as a "rollback," this action is necessary for transactions
that fail for some reason. Reasons to abort transactions include invalid or erroneous
operations, concurrency problems and data overwrites, and the failure to meet the
ACID requirements. During an abort the RDBMS removes from the database any changes
initiated by the transaction.

The two transaction-termination operations are mutually exclusive: You cannot
roll back a transaction after it has been committed, and you cannot commit a transaction
after it has been rolled back. This task demonstrates the effect of using abort to
complete a transaction.

Using SQL Statements to Roll Back a Transaction

The Transact-SQL statement ROLLBACK TRANSACTION denotes the unsuccessful
end of a transaction. To rollback a transaction in a .sql file or when using the
SQL Server SQL Query Tool, use the following statement:

ROLLBACK TRANSACTION

For nested transactions, you can optionally assign a name to the outermost ROLLBACK
TRANSACTION statement.

Use SQL Server ISQL to attempt to insert and rollback a new dentistry department
into the Department table:

1. Start the ISQL application.



2. Select the HumanResources database from the DB drop-down list.



3. Type the following lines in the Query window:

      BEGIN TRANSACTION


 
   INSERT INTO Department VALUES (`DENT','Dentistry')


 
   ROLLBACK TRANSACTION

4. Click the Execute Query button in the ISQL window.

The ROLLBACK TRANSACTION statement aborts the transaction and rolls back
any changes made to the Human Resources database. Changes made to the database within
the transaction do not persist. As Figure 3.4 shows, Beckwith College does not have
a dentistry department.



Figure 3.4. The Beckwith HR Department table after an INSERT
and ROLLBACK transaction.

Programmatically Aborting Transactions in Your Application

When a transaction fails, the component's context object aborts the work and rolls
back the changes. You must obtain a reference to the context object (see "Managing
Transactions in MTS Components" earlier in the chapter) and then call the object's
SetAbort method.

The code fragment in Listing 3.2 uses the SetAbort method to abort a
transaction.

Listing 3.2.  Aborting a transaction.

  
Dim objContext 
As ObjectContext


 
  
Set objContext = GetObjectContext()


 
  ` Perform work


 
  ` success - complete the transaction


 
  objContext.SetComplete




  Exit Sub


 
  ` failure - abort transaction


 
  OnError:


 
  objContext.SetAbort

Task Understanding Distributed Transactions The advent of distributed applications
has given rise to a new set of information technology problems. Because of their
distributed nature, these types of applications are particularly vulnerable to hardware
and software problems. Distributed applications depend on reliable servers, clients,
and networks to perform optimally and without error--problems in any of these areas
can easily cause the application to fail.

You can use transactions as a means of providing simple yet reliable transaction
semantics to an application. The capability to perform transactions that can update
data across multiple databases or objects is essential for building robust, scalable,
and efficient distributed applications.

With MTS your applications and components can participate in transactions across
databases, components, and servers. The Microsoft Distributed Transaction Coordinator
(DTC) is the principal component for managing distributed transactions. In this task
you learn how transactions can span beyond a database and how the DTC service manages
and controls distributed applications.

Transactions Beyond the Database

MTS encapsulates the transaction semantics within the components that it hosts.
If you declare components as transactional, MTS associates transactions with the
component's objects. Multiple objects can perform actions that can be composed into
a single atomic transaction.

The component's context object completes the transaction. Components can be distributed
among servers, persist data to multiple databases, and still participate in a single
atomic transaction.

The Beckwith database system must use distributed transactions to consistently
promote prospect objects from the Admissions database to student objects in the BusinessOffice
database. Chapter 18, "Working with the Beckwith Online Sample Application,"
explains how to use transactions to accomplish this task.

The Microsoft Distributed Transaction Coordinator

MTS includes the Microsoft Distributed Transaction Coordinator (MS DTC), which
provides a robust, high-performance, scalable, easy-to-use distributed transaction
service. MS DTC is currently integrated with Microsoft SQL Server and will soon be
part of most major RDBMS products. MS DTC enables remote stored procedure calls to
participate in distributed transactions.

MS DTC uses a two-phase commit protocol to ensure that the outcome of a transaction
is consistent across all components participating in a transaction. Consequently,
changes on multiple servers can be committed as a single unit of work.

You can start the service automatically or start it manually from either the control
panel or Transaction Server Explorer. Chapter 7, "Using Transaction Server Explorer,"
explains how to manage the MS DTC service using Transaction Server Explorer.

The MTS installation asks whether you want the MS DTC service to start automatically
each time you restart the workstation. You can change this setting by using the control
panel services applet:

1. Open the Control Panel.



2. Open the Services applet (Figure 3.5).

Figure 3.5. The Control Panel Services spplet.

3. Find the MS DTC service in the list.



4. Click Startup to view the Service dialog (Figure 3.6).



5. Select the desired Startup Type (either Manual or Automatic). If hosting
MTS is a primary role of the workstation, select Automatic.



6. Click OK to close the Service dialog.



If MS DTC is not running, you can start the service by clicking Start. (You normally
use Transaction Server Explorer to start this service.)

Figure 3.6. The Control Panel Service dialog.

7. Click Close to close the Services applet.

---

NOTE You can fully manage, configure, and monitor MS DTC through Transaction
Server Explorer. See Chapter 7 for details.

---

Workshop Wrap-Up

A transaction is a collection of database operations grouped into a logical unit
of work that is either completely executed or completely terminated. In this chapter
you learned how to use simple transactions in Microsoft SQL Server's Transact-SQL
and how these transactions relate to MTS components. You learned the basic principles
of transaction processing and the ACID requirements for a successful transaction.
Finally, you learned how to manage the MS DTC service from the Control Panel.

Next Steps

• 
  
• See Chapter 4, "Getting Acquainted with COM," for a thorough discussion
  of the Component Object Model (COM)--the foundation of all ActiveX technologies.
  ActiveX and COM are the central technologies required to build and maintain
  MTS applications.
  
  
  
  
• In Part III you build MTS components that use the transactional concepts covered
  in this chapter.
  
  
  
  
• Chapter 18 includes tasks and samples that show you how to use distributed transactions
  in your applications.