Controlling Transactions

As you has become an intermediate-level SQL and database user. If required, you could build a database with its associated tables, each of which would contain several fields of different data types. Using proper design techniques, you could leverage the information contained within this database into a powerful application.

Objectives

If you are a casual user of SQL who occasionally needs to retrieve data from a database. However, if you intend to (or are currently required to) develop a professional application using any type of relational database, the advanced topics covered over the next four days--transaction control, security, embedded SQL programming, and database procedures--will help you a great deal. We begin with transaction control:

• The basics of transaction control

• How to finalize and or cancel a transaction

• Some of the differences between Sybase and Oracle transactions

NOTE: We used both Personal Oracle7 and Sybase's SQL Server to generate today's examples. Please see the documentation for your specific SQL implementation for any minor differences in syntax.

Transaction Control

Transaction control, or transaction management, refers to the capability of a relational database management system to perform database transactions. Transactions are units of work that must be done in a logical order and successfully as a group or not at all. The term unit of work means that a transaction has a beginning and an end. If anything goes wrong during the transaction, the entire unit of work can be canceled if desired. If everything looks good, the entire unit of work can be saved to the database.

In the coming months or years you will probably be implementing applications for multiple users to use across a network. Client/server environments are designed specifically for this purpose. Traditionally, a server (in this case, a database server) supports multiple network connections to it. As often happens with technology, this newfound flexibility adds a new degree of complexity to the environment.

The Banking Application

You are employed by First Federal Financial Bank to set up an application that handles checking account transactions that consist of debits and credits to customers' checking accounts. You have set up a nice database, which has been tested and verified to work correctly. After calling up your application, you verify that when you take $20 out of the account, $20 actually disappears from the database. When you add $50.25 to the checking account, this deposit shows up as expected. You proudly announce to your bosses that the system is ready to go, and several computers are set up in a local branch to begin work.

Within minutes, you notice a situation that you did not anticipate: As one teller is depositing a check, another teller is withdrawing money from the same account. Within minutes, many depositors' balances are incorrect because multiple users are updating tables simultaneously. Unfortunately, these multiple updates are overwriting each other. Shortly thereafter, your application is pulled offline for an overhaul. We will work through this problem with a database called CHECKING. Within this database are two tables’ customers and Balances.

Assume now that your application program performs a SELECT operation and retrieves the following data for Bill Turner:

INPUT:

As you can see, the information you retrieved earlier could be invalid if the update occurred during the middle of your SELECT. If your application fired off a letter to be sent to Mr. Bill Turner, the address it used would be wrong. Obviously, if the letter has already been sent, you won't be able to change the address. However, if you had used a transaction, this data change could have been detected, and all your other operations could have been rolled back.

Beginning a Transaction

Transactions are quite simple to implement. You will examine the syntax used to perform transactions using the Oracle RDBMS SQL syntax as well as the Sybase SQL Server SQL syntax.

All database systems that support transactions must have a way to explicitly tell the system that a transaction is beginning. (Remember that a transaction is a logical grouping of work that has a beginning and an end.) Using Personal Oracle7, the syntax looks like this:

SYNTAX:

SET TRANSACTION {READ ONLY | USE ROLLBACK SEGMENT segment}

The SQL standard specifies that each database's SQL implementation must support statement-level read consistency; that is, data must stay consistent while one statement is executing. However, in many situations data must remain valid across a single unit of work, not just within a single statement. Oracle enables the user to specify when the transaction will begin by using the SET TRANSACTION statement. If you wanted to examine Bill Turner's information and make sure that the data was not changed, you could do the following:

INPUT:

SQL> SET TRANSACTION READ ONLY;

SQL> SELECT * FROM CUSTOMERS WHERE NAME = 'Bill Turner'; ---Do Other Operations-

SQL> COMMIT;

We discuss the COMMIT statement later today. The SET TRANSACTION READ ONLY option enables you to effectively lock a set of records until the transaction ends. You can use the READ ONLY option with the following commands:

SELECT

LOCK TABLE

SET ROLE

ALTER SESSION

ALTER SYSTEM

The option USE ROLLBACK SEGMENT tells Oracle which database segment to use for rollback storage space. This option is an Oracle extension to standard SQL syntax. Consult your Oracle documentation for more information on using segments to maintain your database.

For now, don't worry about the ROLLBACK TRANSACTION and COMMIT TRANSACTION statements. The important aspect of this example is the nested transaction--or a transaction within a transaction.

Notice that the original transaction (new-account) begins on line 1. After the first insert, you check to make sure the INSERT was executed properly. Another transaction begins on line 5. This transaction within a transaction is termed a nested transaction.

Other databases support the AUTOCOMMIT option. This option can be used with the SET command. For example:

SET AUTOCOMMIT [ON | OFF]

By default, the SET AUTOCOMMIT ON command is executed at startup. It tells SQL to automatically commit all statements you execute. If you do not want these commands to be automatically executed, set the AUTOCOMMIT option to off:

SET AUTOCOMMIT OFF

NOTE:Check your database system's documentation to determine how you would begin a transaction.

Finishing a Transaction

The Oracle syntax to end a transaction is as follows:

SYNTAX:

COMMIT [WORK] [ COMMENT 'text' | FORCE 'text' [, integer] ] ;

Here is the same command using Sybase syntax:

SYNTAX:

COMMIT (TRANSACTION | TRAN | WORK) (TRANSACTION-NAME)

The COMMIT command saves all changes made during a transaction. Executing a COMMIT statement before beginning a transaction ensures that no errors were made and no previous transactions are left hanging.

The following example verifies that the COMMIT command can be used by itself without receiving an error back from the database system.

INPUT:

SQL> COMMIT;

SQL> SET TRANSACTION READ ONLY;

SQL> SELECT * FROM CUSTOMERS WHERE NAME = 'Bill Turner'; --Do Other Operations-

A Sybase SQL use of the COMMIT statement would look like this:

INPUT:

The preceding statements accomplish the same thing as they do using the Oracle7 syntax. However, by putting the COMMIT command soon after the transaction begins, you ensure that the new transaction will execute correctly.

NOTE:The COMMIT WORK command performs the same operation as the COMMIT command (or Sybase's COMMIT TRANSACTION command). It is provided simply to comply with ANSI SQL syntax.

Remember that every COMMIT command must correspond with a previously executed SET TRANSACTION or BEGIN TRANSACTION command. Note the errors you receive with the following statements:

Oracle SQL:

INPUT:

SQL> INSERT INTO BALANCES values (18765.42, 19073.06, 8);

SQL> COMMIT WORK;

Sybase SQL:

INPUT:

insert into BALANCES values (18765.42, 19073.06, 8)

commit work

Canceling the Transaction

While a transaction is in progress, some type of error checking is usually performed to determine whether it is executing successfully. You can undo your transaction even after successful completion by issuing the ROLLBACK statement, but it must be issued before a COMMIT. The ROLLBACK statement must be executed from within a transaction. The ROLLBACK statement rolls the transaction back to its beginning; in other words, the state of the database is returned to what it was at the transaction's beginning. The syntax for this command using Oracle7 is the following:

SYNTAX:

ROLLBACK [WORK] [ TO [SAVEPOINT] savepoint | FORCE 'text' ]

As you can see, this command makes use of a transaction savepoint. We discuss this technique later today.

Sybase Transact-SQL's ROLLBACK statement looks very similar to the COMMIT command:

An Oracle SQL sequence of commands might look like this:

INPUT:

SQL> SET TRANSACTION;

SQL> ROLLBACK;

SQL> SELECT * FROM CUSTOMERS;

As you can see, the new record was not added because the ROLLBACK statement rolled the insert back.

Suppose you are writing an application for a graphical user interface, such as Microsoft Windows. You have a dialog box that queries a database and allows the user to change values. If the user chooses OK, the database saves the changes. If the user chooses Cancel, the changes are canceled. Obviously, this situation gives you an opportunity to use a transaction.

SQL> SET TRANSACTION;

The dialog box allows the user to change the current account balance, so you need to store this value back to the database. When the user selects OK, the update will run.

INPUT:

SQL> UPDATE BALANCES SET CURR-BAL = 'new-value' WHERE ACCOUNT-ID = 6;

SQL> COMMIT;

When the user selects Cancel, the ROLLBACK statement is issued.

INPUT:

SQL> ROLLBACK;

When the dialog box is loaded using Sybase SQL, these SQL statements are executed:

The ROLLBACK statement cancels the entire transaction. When you are nesting transactions, the ROLLBACK statement completely cancels all the transactions, rolling them back to the beginning of the outermost transaction.

If no transaction is currently active, issuing the ROLLBACK statement or the COMMIT command has no effect on the database system. (Think of them as dead commands with no purpose.)

After the COMMIT statement has been executed, all actions with the transaction are executed. At this point it is too late to roll back the transaction.

Using Transaction Savepoints

Rolling back a transaction cancels the entire transaction. But suppose you want to "semicommit" your transaction midway through its statements. Both Sybase and Oracle SQL allow you to save the transaction with a savepoint. From that point on, if a ROLLBACK is issued, the transaction is rolled back to the savepoint. All statements that were executed up to the point of the savepoint are saved. The syntax for creating a savepoint using Oracle SQL is as follows:

SYNTAX:

SAVEPOINT savepoint-name;

Sybase SQL Server's syntax to create a savepoint is the following:

SYNTAX:

save transaction savepoint-name

This following example uses Oracle SQL syntax.

INPUT:

SQL> SET TRANSACTION;

SQL> UPDATE BALANCES SET CURR-BAL = 25000 WHERE ACCOUNT-ID = 5;

SQL> SAVEPOINT save-it;

SQL> DELETE FROM BALANCES WHERE ACCOUNT-ID = 5;

SQL> ROLLBACK TO SAVEPOINT save-it;

SQL> COMMIT;

SQL> SELECT * FROM BALANCES;

The previous examples created a savepoint called SAVE-IT. An update was made to the database that changed the value of the CURR-BAL column of the BALANCES table. You then saved this change as a savepoint. Following this save, you executed a DELETE statement, but you rolled the transaction back to the savepoint immediately thereafter. Then you executed COMMIT TRANSACTION, which committed all commands up to the savepoint. Had you executed a ROLLBACK TRANSACTION after the ROLLBACK TRANSACTION savepoint-name command, the entire transaction would have been rolled back and no changes would have been made.

Summary

A transaction can be defined as an organized unit of work. A transaction usually performs a series of operations that depend on previously executed operations. If one of these operations is not executed properly or if data is changed for some reason, the rest of the work in a transaction should be canceled. Otherwise, if all statements are executed correctly, the transaction's work should be saved. The process of canceling a transaction is called a rollback. The process of saving the work of a correctly executed transaction is called a commit.