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:

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:

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.