Lesson 2:Managing SQL Server Transactions | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Almost all Transact-SQL statements can run in a transaction. If the transaction does not generate any errors when it is being executed, all of the modifications in the transaction become a permanent part of the database. All transactions that include data modifications will either reach a new point of consistency and become committed or will be rolled back to the original state of consistency. Transactions are not left in an intermediate state if the database is not consistent. There are several different types of transactions. Each transaction must exhibit specific characteristics to qualify as a transaction. In this lesson, you will learn about these characteristics and about the types of transactions supported in SQL Server. You will also learn how to use Transact-SQL to start and end a transaction.

After this lesson, you will be able to:

Identify the characteristics of a transaction.
Define the various types of transactions.
Use Transact-SQL to start and stop a transaction.

Estimated lesson time: 35 minutes

Overview of SQL Server Transactions

Atomicity. A transaction must be an atomic unit of work (either all of its data modifications are performed, or none of them is performed).
Consistency. When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications in order to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly linked lists, must be correct at the end of the transaction.
Isolation. Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. 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. This situation is referred to as serializability, because it results in the capability to reload the starting data and replay a series of transactions in order to end up with the data in the same state it was in after the original transactions were performed.
Durability. After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

SQL Server provides locking facilities that preserve transaction isolation. SQL Server also provides facilities that ensure transaction durability. Even if the server hardware, operating system, or SQL Server itself fails, SQL Server uses the transaction logs, upon restart, to automatically roll back any uncompleted transactions to the point of the system failure. In addition, SQL Server provides transaction management features that enforce transaction atomicity and consistency. After a transaction has started, it must be successfully completed or SQL Server will undo all of the data modifications made since the transaction started.

Applications control transactions mainly by specifying when a transaction starts and ends. You can specify this information by using either Transact-SQL statements or database API functions. The system must also be capable of correctly handling errors that terminate a transaction before it completes.

Transactions are managed at the connection level. When a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction until the transaction ends.

Specifying Transaction Boundaries

You can identify when SQL Server transactions start and end by using Transact-SQL statements or by using API functions and methods:

Transact-SQL statements. Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command-prompt utility.
API functions and methods. Database APIs such as ODBC, OLE DB, and ADO contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server application.

Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not use the ODBC API functions to start a transaction and then use the Transact-SQL COMMIT statement to complete the transaction. This action would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.

Transact-SQL Statements Allowed in Transactions

You can use all Transact-SQL statements in a transaction except the following statements:


ALTER DATABASE	LOAD DATABASE
BACKUP LOG	LOAD TRANSACTION
CREATE DATABASE	RECONFIGURE
DISK INIT	RESTORE DATABASE
DROP DATABASE	RESTORE LOG
DUMP TRANSACTION	UPDATE STATISTICS

Also, you cannot use sp_dboption to set database options or use any system procedures that modify the master database inside user-defined transactions.

Coding Efficient Transactions

You must keep transactions as short as possible. When a transaction is started, a DBMS must hold many resources to the end of the transaction to protect the ACID properties of the transaction. If data is modified, the modified rows must be protected with exclusive locks that prevent any other transaction from reading the rows, and exclusive locks must be held until the transaction is committed or rolled back. Depending on transaction isolation level settings, SELECT statements may acquire locks that must be held until the transaction is committed or rolled back. Especially in systems that have many users, transactions must be kept as short as possible to reduce locking contention for resources between concurrent connections. Long-running, inefficient transactions might not be a problem with a small number of users, but they are intolerable in a system that has thousands of users.

You should use the following guidelines to code efficient transactions:

Do not require input from users during a transaction.
Do not open a transaction while browsing through data (if at all possible).
Keep the transaction as short as possible.
Make intelligent use of lower transaction isolation levels.
Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options.
Access the least amount of data possible while in a transaction.

Avoiding Concurrency Problems

To prevent concurrency problems, manage implicit transactions carefully. When using implicit transactions, the next Transact-SQL statement after COMMIT or ROLLBACK automatically starts a new transaction. This situation can cause a new transaction to be opened while the application browses through data, or even when it requires input from the user. After completing the last transaction required to protect data modifications, turn off implicit transactions until a transaction is once again required to protect data modifications. This process enables SQL Server to use autocommit mode while the application is browsing data and is getting input from the user.

Errors During Transaction Processing

If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, the connection breaks and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior by using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. SET XACT_ABORT does not affect compile errors (such as syntax errors).

The programmer has the responsibility of coding the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs.

Types of Transactions

SQL Server supports three types of transactions: explicit, autocommit, and implicit.

Explicit Transactions

An explicit transaction is one in which you explicitly define both the start and the end of the transaction. Explicit transactions were also called user-defined or user-specified transactions in earlier versions of SQL Server.

DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions:

BEGIN TRANSACTION. Marks the starting point of an explicit transaction for a connection
COMMIT TRANSACTION or COMMIT WORK. Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed.
ROLLBACK TRANSACTION or ROLLBACK WORK. Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.

In the following transaction, the ROLLBACK TRANSACTION statement rolls back any changes made by the UPDATE statement:

 BEGIN TRANSACTION GO USE Northwind GO UPDATE Customers SET ContactName = 'Hanna Moos' WHERE CustomerID = 'BLAUS' GO ROLLBACK TRANSACTION GO

If a COMMIT TRANSACTION statement had been used in this example, rather than a ROLLBACK TRANSACTION statement, the update would have been made to the database.

You can also use explicit transactions in the OLE DB, ADO, and ODBC APIs. For more information about using explicit transactions with these APIs, refer to SQL Server Books Online.

Explicit transaction mode lasts only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode that it was in before the explicit transaction was started (either implicit or autocommit mode).

Autocommit Transactions

Autocommit mode is the default transaction management mode of SQL Server. Every Transact-SQL statement is committed or rolled back when it is completed. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction or until implicit transaction mode is set to ON. When the explicit transaction is committed or rolled back or when implicit transaction mode is turned off, SQL Server returns to autocommit mode.

Compile and Run-Time Errors

In autocommit mode, it sometimes appears as if SQL Server has rolled back an entire batch instead of just one SQL statement. This situation happens only if the error encountered is a compile error, not a run-time error. A compile error prevents SQL Server from building an execution plan, so nothing in the batch is executed. Although it appears that all the statements before the one that generated the error were rolled back, the error prevented anything in the batch from being executed.

In the following example, none of the INSERT statements in the third batch are executed because of a compile error in the third INSERT statement. The first two INSERT statements are rolled back because of the error, and no data is added to the TestBatch table:

 USE Pubs GO CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3)) GO INSERT INTO TestBatch VALUES (1, 'aaa') INSERT INTO TestBatch VALUES (2, 'bbb') INSERT INTO TestBatch VALUSE (3, 'ccc')  /* Syntax error */ GO SELECT * FROM TestBatch   /* Returns no rows */ GO

In the next example, the third INSERT statement generates a run-time, duplicate primary key error. The first two INSERT statements are successful and committed, so the values are added to the TestBatch table:

 USE Pubs GO CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3)) GO INSERT INTO TestBatch VALUES (1, 'aaa') INSERT INTO TestBatch VALUES (2, 'bbb') INSERT INTO TestBatch VALUES (1, 'ccc')  /* Duplicate key error */ GO SELECT * FROM TestBatch   /* Returns rows 1 and 2 */ GO

SQL Server uses delayed name resolution, in which object names are not resolved until execution time. In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error (by referring to a table that does not exist):

 USE Pubs GO CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3)) GO INSERT INTO TestBatch VALUES (1, 'aaa') INSERT INTO TestBatch VALUES (2, 'bbb') INSERT INTO TestBch VALUES (3, 'ccc')  /* Table name error */ GO SELECT * FROM TestBatch   /* Returns rows 1 and 2 */ GO

Implicit Transactions

When a connection is operating in implicit transaction mode, SQL Server automatically starts a new transaction after the current transaction is committed or rolled back. You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. Implicit transaction mode generates a continuous chain of transactions.

After implicit transaction mode has been set to ON for a connection, SQL Server automatically starts a transaction when it first executes any of the following statements:


ALTER TABLE	INSERT
CREATE	OPEN
DELETE	REVOKE
DROP	SELECT
FETCH	TRUNCATE TABLE
GRANT	UPDATE

The transaction remains in effect until you issue a COMMIT or ROLLBACK statement. After the first transaction is committed or rolled back, SQL Server automatically starts a new transaction the next time any of these statements is executed by the connection. SQL Server keeps generating a chain of implicit transactions until implicit transaction mode is turned off.

Implicit transaction mode is set either by using the Transact-SQL SET statement or by using database API functions and methods.

Transact-SQL Implicit Transactions

DB-Library applications and Transact-SQL scripts can use the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement to start implicit transaction mode. You should use the SET IMPLICIT_TRANSACTIONS OFF statement at the end of the batch to turn implicit transaction mode off. Use the COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to end each transaction.

The following statement first creates the ImplicitTran table, then starts implicit transaction mode, then runs two transactions, and then turns off implicit transaction mode:

 USE Pubs GO CREATE TABLE ImplicitTran       (       Cola INT PRIMARY KEY,       Colb CHAR(3) NOT NULL       ) GO SET IMPLICIT_TRANSACTIONS ON GO /* First implicit transaction started  by an INSERT statement */ INSERT INTO ImplicitTran VALUES (1, 'aaa') GO INSERT INTO ImplicitTran VALUES (2, 'bbb') GO /* Commit first transaction */ COMMIT TRANSACTION GO /* Second implicit transaction started  by an INSERT statement */ INSERT INTO ImplicitTran VALUES (3, 'ccc') GO SELECT * FROM ImplicitTran GO /* Commit second transaction */ COMMIT TRANSACTION GO SET IMPLICIT_TRANSACTIONS OFF GO

API Implicit Transactions

You can use the ODBC and OLE DB APIs to set implicit transactions. Refer to SQL Server Books Online for more information. ADO does not support implicit transactions. ADO applications use either autocommit mode or explicit trans-actions.

Distributed Transactions

Distributed transactions span two or more servers known as resource managers. The management of the transaction must be coordinated among the resource managers by a server component called a transaction manager. SQL Server can operate as a resource manager in distributed transactions coordinated by transaction managers such as the Microsoft Distributed Transaction Coordinator (MS DTC), or by other transaction managers that support the X/Open XA specification for Distributed Transaction Processing.

A transaction within a single SQL Server that spans two or more databases is actually a distributed transaction. SQL Server, however, manages the distributed transaction internally. To the user, it operates as a local transaction.

At the application, a distributed transaction is managed in much the same way as a local transaction. At the end of the transaction, the application requests the transaction to be either committed or rolled back. A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure might result in some resource managers successfully committing while others are rolling back the transaction. You can achieve this goal by managing the commit process in two phases:

Prepare phase. When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the transaction. Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. As each resource manager completes the prepare phase, it returns success or failure of the prepare phase to the transaction manager.
Commit phase. If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. The resource managers can then complete the commit. If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the application. If any resource manager reports a failure to prepare, the transaction manager sends a ROLLBACK command to each resource manager and indicates the failure of the commit to the application.

SQL Server applications can manage distributed transactions either through Transact-SQL or through the database API.

Transact-SQL Distributed Transactions

The distributed transactions started in Transact-SQL have a relatively simple structure:

A Transact-SQL script or application connection executes a Transact-SQL statement that starts a distributed transaction.
The SQL Server instance executing the statement becomes the controlling server in the transaction.
The script or application then executes either distributed queries against linked servers or remote stored procedures against remote servers.
As distributed queries and remote procedure calls are made, the controlling server automatically calls MS DTC to enlist the linked and remote servers in the distributed transaction.
When the script or application issues either a COMMIT or ROLLBACK statement, the controlling SQL Server calls MS DTC to manage the two-phase commit process or to notify the linked and remote servers to roll back their transactions.

Required Transact-SQL Statements

The Transact-SQL statements controlling the distributed transactions are few because SQL Server and MS DTC do most of the work internally. The only Transact-SQL statements required in the Transact-SQL script or application are those required to perform the following tasks:

Starting a distributed transaction
Performing distributed queries against linked servers or executing remote procedure calls against remote servers
Calling the standard Transact-SQL COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to complete the transaction

For any Transact-SQL distributed transaction, the SQL Server processing the Transact-SQL script or connection automatically calls MS DTC to coordinate the transaction's commit or rollback.

MS DTC Distributed Transactions

Applications written by using OLE DB, ODBC, ADO, or DB-Library can use Transact-SQL distributed transactions by issuing Transact-SQL statements to start and stop Transact-SQL distributed transactions. OLE DB and ODBC, however, also contain support at the API level for managing distributed transactions. OLE DB and ODBC applications can use these API functions to manage distributed transactions that include other COM resource managers that support MS DTC transactions other than SQL Server. They can also use the API functions to gain more control over the boundaries of a distributed transaction that includes several SQL Servers. The distributed transactions started in Transact-SQL have a relatively simple structure. The Transact-SQL statements controlling the distributed transactions are few because SQL Server and MS DTC do most of the work internally.

Exercise 2: Implementing Explicit Transactions

In this exercise, you will use Transact-SQL statements to perform explicit, autocommit, and implicit transactions. You will be performing these transactions in the BookShopDB database. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.

To perform an explicit transaction

Open Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter the following Transact-SQL code:

 BEGIN TRANSACTION GO USE BookShopDB GO UPDATE Authors SET Description =   'English author whose novels are highly regarded   for humor, irony, and depiction of English life.' WHERE LastName = 'Austen' GO COMMIT TRANSACTION GO

In this statement, you are first using the BEGIN TRANSACTION statement to begin the transaction. You are then updating the Authors table in the BookShopDB database. Finally, you are committing the transaction by using the COMMIT TRANSACTION statement.

Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that one row has been affected by the transaction.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 SELECT * FROM Authors

The result set is displayed in the Grids tab of the Results pane. Notice that the row for Jane Austen now contains the description specified in the transaction.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 UPDATE Authors SET Description = 'N/A' WHERE LastName = 'Austen'

A message appears in the Messages tab of the Results pane, stating that one row has been affected by the transaction.

To perform an autocommit transaction

In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE BookShopDB GO CREATE TABLE TestTable (Col1 INT PRIMARY KEY, Col2 CHAR(3)) GO INSERT INTO TestTable VALUES (101, 'ABC') INSERT INTO TestTable VALUES (102, 'DEF') INSERT INTO TestTable VALUSE (103, 'GHI') GO SELECT * FROM TestTable GO

In this statement, you are defining four autocommit transactions. The first transaction identifies the database (BookShopDB) to be used. In the second transaction, you are creating the TestTable table. In the third transaction, you are adding values to the table, and in the fourth transaction, you are performing a SELECT query against the table.

Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that incorrect syntax (VALUSE) has been used.

Click the Grids tab.

Notice that Col1 and Col2 are displayed but that no values have been added. When the script was executed, the table was created and the SELECT query was executed. However, because the third transaction included a syntax error, none of the values were inserted in the table.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 DROP TABLE TestTable

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 USE BookShopDB GO CREATE TABLE TestTable (Col1 INT PRIMARY KEY, Col2 CHAR(3)) GO INSERT INTO TestTable VALUES (101, 'ABC') INSERT INTO TestTable VALUES (102, 'DEF') INSERT INTO TestTable VALUES (103, 'GHI') GO SELECT * FROM TestTable GO

The result set is now correctly displayed in the Grids tab of the Results pane.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 DROP TABLE TestTable

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

To perform an implicit transaction

In the Editor pane of the Query window, enter the following Transact-SQL code:

 SET IMPLICIT_TRANSACTIONS ON GO USE BookShopDB CREATE TABLE TestTable (Col1 INT PRIMARY KEY, Col2 CHAR(3)) GO COMMIT TRANSACTION GO INSERT INTO TestTable VALUES (101, 'ABC') INSERT INTO TestTable VALUES (102, 'DEF') INSERT INTO TestTable VALUES (103, 'GHI') GO COMMIT TRANSACTION GO SELECT * FROM TestTable GO COMMIT TRANSACTION GO SET IMPLICIT_TRANSACTIONS OFF GO

In this statement, you are first setting implicit transactions to ON. The first implicit transaction creates TestTable in the BookShopDB database. The next transaction inserts values into the table, and the final transaction performs a SELECT query against the table. Once all three implicit transactions have been executed, implicit transactions are set to OFF.

Execute the Transact-SQL statement.

The result set is displayed in the Grids tab of the Results pane.

In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 DROP TABLE TestTable

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

Lesson Summary

A transaction is a sequence of operations performed as a single, logical unit of work. To qualify as a transaction, a logical unit of work must exhibit four properties, called the ACID properties (atomicity, consistency, isolation, and durability). You can identify when SQL Server transactions start and end with Transact-SQL statements or with API functions and methods. SQL Server supports three types of transactions: explicit, autocommit, and implicit. An explicit transaction is one in which you explicitly define both the start and the end of the transaction. An autocommit transaction is the default transaction management mode of SQL Server. Every Transact-SQL statement is committed or rolled back when it is completed. When a connection is operating in implicit transaction mode, SQL Server automatically starts a new transaction after the current transaction is committed or rolled back. Distributed transactions span two or more servers known as resource managers. The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager.