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.
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. Consider the banking application described in the next few paragraphs.
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, shown in Tables 11.1 and 11.2.
Name | Address | City | State | Zip | Customer_ID |
Bill Turner | 725 N. Deal Parkway | Washington | DC | 20085 | 1 |
John Keith | 1220 Via De Luna Dr. | Jacksonville | FL | 33581 | 2 |
Mary Rosenberg | 482 Wannamaker Avenue | Williamsburg | VA | 23478 | 3 |
David Blanken | 405 N. Davis Highway | Greenville | SC | 29652 | 4 |
Rebecca Little | 7753 Woods Lane | Houston | TX | 38764 | 5 |
Average_Bal | Curr_Bal | Account_ID |
1298.53 | 854.22 | 1 |
5427.22 | 6015.96 | 2 |
211.25 | 190.01 | 3 |
73.79 | 25.87 | 4 |
1285.90 | 1473.75 | 5 |
1234.56 | 1543.67 | 6 |
345.25 | 348.03 | 7 |
NAME: Bill Turner ADDRESS: 725 N. Deal Parkway CITY: Washington STATE: DC ZIP: 20085 CUSTOMER_ID: 1While this information is being retrieved, another user with a connection to this database updates Bill Turner's address information:
SQL> UPDATE CUSTOMERS SET Address = "11741 Kingstowne Road" WHERE Name = "Bill Turner";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.
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:
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:
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.
SQL Server's Transact-SQL language implements the BEGIN TRANSACTION command with the following syntax:
begin {transaction | tran} [transaction_name]This implementation is a little different from the Oracle implementation. (Sybase does not allow you to specify the READ ONLY option.) However, Sybase does allow you to give a transaction a name, as long as that transaction is the outermost of a set of nested transactions.
The following group of statements illustrates the use of nested transactions using Sybase's Transact-SQL language:
1> begin transaction new_account 2> insert CUSTOMERS values ("Izetta Parsons", "1285 Pineapple Highway", "Greenville", "AL" 32854, 6) 3> if exists(select * from CUSTOMERS where Name = "Izetta Parsons") 4> begin 5> begin transaction 6> insert BALANCES values(1250.76, 1431.26, 8) 7> end 8> else 9> rollback transaction 10> if exists(select * from BALANCES where Account_ID = 8) 11> begin 12> begin transaction 13> insert ACCOUNTS values(8, 6) 14> end 15> else 16> rollback transaction 17> if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6) 18> commit transaction 19> else 20> rollback transaction 21> goFor 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.
COMMIT [WORK] [ COMMENT 'text' | FORCE 'text' [, integer] ] ;Here is the same command using Sybase 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.
SQL> COMMIT; SQL> SET TRANSACTION READ ONLY; SQL> SELECT * FROM CUSTOMERS WHERE NAME = 'Bill Turner'; ---Do Other Operations--- SQL> COMMIT;An Oracle SQL use of the COMMIT statement would look like this:
SQL> SET TRANSACTION; SQL> INSERT INTO CUSTOMERS VALUES ("John MacDowell", "2000 Lake Lunge Road", "Chicago", "IL", 42854, 7); SQL> COMMIT; SQL> SELECT * FROM CUSTOMERS;
Name | Address | City | State | Zip | Customer_ID |
Bill Turner | 725 N. Deal Parkway | Washington | DC | 20085 | 1 |
John Keith | 1220 Via De Luna Dr. | Jacksonville | FL | 33581 | 2 |
Mary Rosenberg | 482 Wannamaker Avenue | Williamsburg | VA | 23478 | 3 |
David Blanken | 405 N. Davis Highway | Greenville | SC | 29652 | 4 |
Rebecca Little | 7753 Woods Lane | Houston | TX | 38764 | 5 |
Izetta Parsons | 1285 Pineapple Highway | Greenville | AL | 32854 | 6 |
John MacDowell | 2000 Lake Lunge Road | Chicago | IL | 42854 | 7 |
1> begin transaction 2> insert into CUSTOMERS values ("John MacDowell", "2000 Lake Lunge Road", "Chicago", "IL", 42854, 7) 3> commit transaction 4> go 1> select * from CUSTOMERS 2> go
Name | Address | City | State | Zip | Customer_ID |
Bill Turner | 725 N. Deal Parkway | Washington | DC | 20085 | 1 |
John Keith | 1220 Via De Luna Dr. | Jacksonville | FL | 33581 | 2 |
Mary Rosenberg | 482 Wannamaker Avenue | Williamsburg | VA | 23478 | 3 |
David Blanken | 405 N. Davis Highway | Greenville | SC | 29652 | 4 |
Rebecca Little | 7753 Woods Lane | Houston | TX | 38764 | 5 |
Izetta Parsons | 1285 Pineapple Highway | Greenville | AL | 32854 | 6 |
John MacDowell | 2000 Lake Lunge Road | Chicago | IL | 42854 | 7 |
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:
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.
Oracle SQL:
SQL> INSERT INTO BALANCES values (18765.42, 19073.06, 8); SQL> COMMIT WORK;Sybase SQL:
1> insert into BALANCES values (18765.42, 19073.06, 8) 2> commit work
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:
rollback {transaction | tran | work} [transaction_name | savepoint_name]An Oracle SQL sequence of commands might look like this:
SQL> SET TRANSACTION; SQL> INSERT INTO CUSTOMERS VALUES ("Bubba MacDowell", "2222 Blue Lake Way", "Austin", "TX", 39874, 8); SQL> ROLLBACK; SQL> SELECT * FROM CUSTOMERS;
Name | Address | City | State | Zip | Customer_ID |
Bill Turner | 725 N. Deal Parkway | Washington | DC | 20085 | 1 |
John Keith | 1220 Via De Luna Dr. | Jacksonville | FL | 33581 | 2 |
Mary Rosenberg | 482 Wannamaker Avenue | Williamsburg | VA | 23478 | 3 |
David Blanken | 405 N. Davis Highway | Greenville | SC | 29652 | 4 |
Rebecca Little | 7753 Woods Lane | Houston | TX | 38764 | 5 |
Izetta Parsons | 1285 Pineapple Highway | Greenville | AL | 32854 | 6 |
John MacDowell | 2000 Lake Lunge Road | Chicago | IL | 42854 | 7 |
1> begin transaction 2> insert into CUSTOMERS values ("Bubba MacDowell", "2222 Blue Lake Way", "Austin", "TX", 39874, 8) 3> rollback transaction 4> go 1> SELECT * FROM CUSTOMERS 2> go
Name | Address | City | State | Zip | Customer_ID |
Bill Turner | 725 N. Deal Parkway | Washington | DC | 20085 | 1 |
John Keith | 1220 Via De Luna Dr. | Jacksonville | FL | 33581 | 2 |
Mary Rosenberg | 482 Wannamaker Avenue | Williamsburg | VA | 23478 | 3 |
David Blanken | 405 N. Davis Highway | Greenville | SC | 29652 | 4 |
Rebecca Little | 7753 Woods Lane | Houston | TX | 38764 | 5 |
Izetta Parsons | 1285 Pineapple Highway | Greenville | AL | 32854 | 6 |
John MacDowell | 2000 Lake Lunge Road | Chicago | IL | 42854 | 7 |
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.
When the dialog box is loaded, these SQL statements are executed:
NOTE: The following code listing uses Oracle SQL syntax; notice the SQL> prompt and line numbers. The subsequent listing uses Sybase SQL syntax, which lacks the SQL> prompt.
SQL> SET TRANSACTION; SQL> SELECT CUSTOMERS.NAME, BALANCES.CURR_BAL, BALANCES.ACCOUNT_ID 2 FROM CUSTOMERS, BALANCES 3 WHERE CUSTOMERS.NAME = "Rebecca Little" 4 AND CUSTOMERS.CUSTOMER_ID = BALANCES.ACCOUNT_ID;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.
SQL> UPDATE BALANCES SET CURR_BAL = 'new-value' WHERE ACCOUNT_ID = 6; SQL> COMMIT;When the user selects Cancel, the ROLLBACK statement is issued.
SQL> ROLLBACK;When the dialog box is loaded using Sybase SQL, these SQL statements are executed:
1> begin transaction 2> select CUSTOMERS.Name, BALANCES.Curr_Bal, BALANCES.Account_ID 3> from CUSTOMERS, BALANCES 4> where CUSTOMERS.Name = "Rebecca Little" 5> and CUSTOMERS.Customer_ID = BALANCES.Account_ID 6> goThe dialog box allows the user to change the current account balance, so you can store this value back to the database.
Here again, when the OK button is selected, the update will run.
1> update BALANCES set Curr_BAL = 'new-value' WHERE Account_ID = 6 2> commit transaction 3> goWhen the user selects Cancel, the ROLLBACK statement is issued.
1> rollback transaction 2> goThe 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.
SAVEPOINT savepoint_name;Sybase SQL Server's syntax to create a savepoint is the following:
save transaction savepoint_nameThis following example uses Oracle SQL syntax.
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;
Average_Bal | Curr_Bal | Account_ID |
1298.53 | 854.22 | 1 |
5427.22 | 6015.96 | 2 |
211.25 | 190.01 | 3 |
73.79 | 25.87 | 4 |
1285.90 | 25000.00 | 5 |
1234.56 | 1543.67 | 6 |
345.25 | 348.03 | 7 |
1250.76 | 1431.26 | 8 |
1> begin transaction 2> update BALANCES set Curr_Bal = 25000 where Account_ID = 5 3> save transaction save_it 4> delete from BALANCES where Account_ID = 5 5> rollback transaction save_it 6> commit transaction 7> go 1> select * from BALANCES 2> go
Average_Bal | Curr_Bal | Account_ID |
1298.53 | 854.22 | 1 |
5427.22 | 6015.96 | 2 |
211.25 | 190.01 | 3 |
73.79 | 25.87 | 4 |
1285.90 | 25000.00 | 5 |
1234.56 | 1543.67 | 6 |
345.25 | 348.03 | 7 |
1250.76 | 1431.26 | 8 |
This example uses Oracle SQL syntax:
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> ROLLBACK; SQL> SELECT * FROM BALANCES;
Average_Bal | Curr_Bal | Account_ID |
1298.53 | 854.22 | 1 |
5427.22 | 6015.96 | 2 |
211.25 | 190.01 | 3 |
73.79 | 25.87 | 4 |
1285.90 | 1473.75 | 5 |
1234.56 | 1543.67 | 6 |
345.25 | 348.03 | 7 |
1250.76 | 1431.26 | 8 |
1> begin transaction 2> update BALANCES set Curr_Bal = 25000 where Account_ID = 5 3> save transaction save_it 4> delete from BALANCES where Account_ID = 5 5> rollback transaction save_it 6> rollback transaction 7> go 1> select * from BALANCES 2> go
Average_Bal | Curr_Bal | Account_ID |
1298.53 | 854.22 | 1 |
5427.22 | 6015.96 | 2 |
211.25 | 190.01 | 3 |
73.79 | 25.87 | 4 |
1285.90 | 1473.75 | 5 |
1234.56 | 1543.67 | 6 |
345.25 | 348.03 | 7 |
1250.76 | 1431.26 | 8 |
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. SQL syntax supports these two processes through syntax similar to the following two statements:
BEGIN TRANSACTION statement 1 statement 2 statement 3 ROLLBACK TRANSACTIONor
BEGIN TRANSACTION statement 1 statement 2 statement 3 COMMIT TRANSACTION
A No. The entire group must run successfully.
Q After issuing the COMMIT command, I discovered that I made a mistake. How can I correct the error?
A Use the DELETE, INSERT, and UPDATE commands.
Q Must I issue the COMMIT command after every transaction?
A No. But it is safer to do so to ensure that no errors were made and no previous transactions are left hanging.
2. Can savepoints be used to "save off" portions of a transaction? Why or why not?
3. Can a COMMIT command be used by itself or must it be embedded?
4. If you issue the COMMIT command and then discover a mistake, can you still use the ROLLBACK command?
5. Will using a savepoint in the middle of a transaction save all that happened before it automatically?
SQL> START TRANSACTION INSERT INTO CUSTOMERS VALUES ('SMITH', 'JOHN') SQL> COMMIT;
SQL> SET TRANSACTION; UPDATE BALANCES SET CURR_BAL = 25000; SQL> COMMIT;
SQL> SET TRANSACTION; INSERT INTO BALANCES VALUES ('567.34', '230.00', '8'); SQL> ROLLBACK;