Chapter 20: 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 must 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 may 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.

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, 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. See “Selecting the transaction mode and isolation level” for more information.

View this book as PDF