Transact-SQL User's Guide

Home

Chapter 18 Transactions: Maintaining
Data Consistency and Recovery

Transact-SQL User's Guide

Chapter 18 Transactions: Maintaining Data Consistency and Recovery

How transactions work

Adaptive Server automatically manages all data modification commands, including single-step change requests, as transactions. By default, each insert, update, and delete statement is considered a single transaction.

However, consider the following scenario: Lee needs to make a series of data retrievals and modifications to the authors, titles, and titleauthors tables. As she is doing so, Lil begins to update the titles table. Lil's updates could cause inconsistent results with the work that Lee is doing. To prevent this from happening, Lee can group her statements into a single transaction, which locks Lil out of the portions of the tables that Lee is working on. This allows Lee to complete her work based on accurate data. After she completes her table updates, Lil's updates can take place.

You can use the following commands to create transactions:

begin transaction - marks the beginning of the transaction block. The syntax is:
```
begin {transaction | tran} [transaction_name] 
```
transaction_name is the name assigned to the transaction. It must conform to the rules for identifiers. Use transaction names only on the outermost pair of nested begin/commit or begin/rollback statements.
save transaction - marks a savepoint within a transaction:
```
save {transaction | tran} savepoint_name 
```
savepoint_name is the name assigned to the savepoint. It must conform to the rules for identifiers.

commit - commits the entire transaction:

commit [transaction | tran | work] 
[transaction_name]

rollback - rolls a transaction back to a savepoint or to the beginning of a transaction:
```
rollback [transaction | tran | work] 
[transaction_name | savepoint_name] 
```

For example, user Lee sets out to change the royalty split for two authors of The Gourmet Microwave. Since the database would be inconsistent between the two updates, they must be grouped into a transaction, as shown in the following example:

begin transaction royalty_change 
 
update titleauthor 
set royaltyper = 65 
from titleauthor, titles 
where royaltyper = 75 
and titleauthor.title_id = titles.title_id 
and title = "The Gourmet Microwave" 
 
update titleauthor 
set royaltyper = 35 
from titleauthor, titles 
where royaltyper = 25 
and titleauthor.title_id = titles.title_id 
and title = "The Gourmet Microwave" 
 
save transaction percentchanged
 
/* After updating the royaltyper entries for 
** the two authors, insert the savepoint 
** percentchanged, then determine how a 10% 
** increase in the book's price would affect
** the authors' royalty earnings. */
 
update titles 
set price = price * 1.1 
where title = "The Gourmet Microwave" 
 
select (price * total_sales) * royaltyper 
from titles, titleauthor 
where title = "The Gourmet Microwave" 
and titles.title_id = titleauthor.title_id
 
/* The transaction is rolled back to the savepoint 
** with the rollback transaction command. */ 
 
rollback transaction percentchanged
 
commit transaction

Transactions allow Adaptive Server to guarantee:

Consistency - simultaneous queries and change requests cannot collide with each other, and users never see or operate on data that is partially through a change.
Recovery - in case of system failure, database recovery is complete and automatic.

To support SQL standards-compliant transactions, Adaptive Server allows you to select the mode and isolation level for your transactions. Applications that require SQL standards-compliant transactions should set those options at the beginning of every session. Transaction modes and isolation levels are described later in this chapter. See "Selecting the transaction mode and isolation level" for more information.

Transactions and consistency

In a multiuser environment, Adaptive Server must prevent simultaneous queries and data modification requests from interfering with each other. This is important because if the data being processed by a query can be changed by another user's update, the results of the query may be ambiguous.

Adaptive Server automatically sets the appropriate level of locking for each transaction. You can make shared locks more restrictive on a query-by-query basis by including the holdlock keyword in a select statement.

Transactions and recovery

A transaction is both a unit of work and a unit of recovery. The fact that Adaptive Server handles single-step change requests as transactions means that the database can be recovered completely in case of failure.

Adaptive Server's recovery time is measured in minutes and seconds. You can specify the maximum acceptable recovery time.

The SQL commands related to recovery and backup are discussed in "Backup and recovery of transactions".

Grouping large numbers of Transact-SQL commands into one long-running transaction may affect recovery time. If Adaptive Server fails before the transaction commits, recovery takes longer, because Adaptive Server must undo the transaction.

If you are using a remote database with Component Integration Services, there are a few differences in the way transactions are handled. See the Component Integration Services User's Guide for more information.

If you have purchased and installed Adaptive Server DTM features, transactions that update data in multiple servers can also benefit from transactional consistency. See Using Adaptive Server Distributed Transaction Features for more information.


	Expand Search