Database Transactions

A database transaction is a group of SQL statements that are a logical unit of work . You can think of a transaction as an inseparable set of SQL statements that should be made permanent in the database (or undone) as a whole. An example of this would be a transfer of money from one bank account to another. One UPDATE statement would subtract from the total amount of money from one account, and another UPDATE would add money to the other account. Both the subtraction and the addition must either be permanently recorded in the database, or they both must be undone ” otherwise money will be lost. This simple example uses only two UPDATE statements, but a more realistic transaction may consist of many INSERT , UPDATE , and DELETE statements.

Committing and Rolling Back a Transaction

To permanently record the results of the SQL statements in a transaction, you perform a commit with the COMMIT statement. To undo the results of the SQL statements, you perform a rollback with the ROLLBACK statement, which resets all the rows back to what they were originally. Any changes you make prior to performing a rollback will be undone, as long as you haven t disconnected from the database beforehand.

The following example adds a row to the customers table and then makes the change permanent by performing a COMMIT :

  INSERT INTO customers   VALUES (6, 'Fred', 'Green', '01-JAN-1970', '800-555-1215');  1 row created.  COMMIT;  Commit complete. 

The following example updates a row in the customers table and then undoes the change by performing a ROLLBACK :

  UPDATE customers   SET first_name = 'Edward'   WHERE customer_id = 1;  1 row updated.  ROLLBACK;  Rollback complete. 

You can verify the changes to the customers table using the following query:

  SELECT *   FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70  6 Fred Green 01-JAN-70 800-555-1215 

Notice that the result of the INSERT statement that added customer #6 is indeed made permanent by the COMMIT and that the result of the UPDATE statement that changed the first name of customer #1 is undone by the ROLLBACK .

Starting and Ending a Transaction

As mentioned, transactions are logical units of work you use to split up your database activities. A transaction has both a beginning and an end; it begins when one of the following events occurs:

• You connect to the database and perform the first DML statement.

• A previous transaction ends and you enter another DML statement.

A transaction ends when one of the following events occurs:

• You perform a COMMIT or a ROLLBACK statement.

• You perform a DDL statement, such as a CREATE TABLE statement, in which case a COMMIT is automatically performed.

• You perform a DCL statement, such as a GRANT statement, in which case a COMMIT is automatically performed. You ll learn about GRANT in the next chapter.

• You disconnect from the database. If you exit SQL*Plus normally by entering the EXIT command, a COMMIT is automatically performed for you. If SQL*Plus terminates abnormally ”for example, if the computer on which SQL*Plus was running were to crash ”a ROLLBACK is automatically performed. This applies to any program that accesses a database. For example, if you wrote a Java program that accessed a database and your program crashed, a ROLLBACK would be automatically performed.

• You perform a DML statement that fails, in which case a ROLLBACK is automatically performed for that individual DML statement.

  Tip  

  It is considered poor practice not to explicitly commit or roll back your transactions once they are complete, so make sure you perform a COMMIT or ROLLBACK at the end of your transactions.

Savepoints

You can also set a savepoint at any point within a transaction. These allow you to roll back changes to that point. This might be useful if you have a very long transaction because if you make a mistake after you ve set a savepoint, you don t have to roll back the transaction all the way to the start. You should use savepoints sparingly; you might be better off restructuring your transaction into smaller transactions instead. I ll show you an example of using a savepoint, but before we begin, let s check the details for product #1 and product #2:

  SELECT product_id, price   FROM products   WHERE product_id IN (1, 2);  PRODUCT_ID PRICE ---------- ----------  1 19.95  2 30 

So far, so good: the price for product #1 is $19.95, and the price for product #2 is $30. Next, let s increase the price of product #1 by 20 percent:

 UPDATE products SET price = price * 1.20 WHERE product_id = 1; 

Set a savepoint here named save1 . This will allow you to roll back any further DML statements and preserve the previous UPDATE :

 SAVEPOINT save1; 

Next, let s increase the price of product #2 by 30 percent:

 UPDATE products SET price = price * 1.30 WHERE product_id = 2; 

Let s check the prices of the two products, just to make sure everything s set as we expect:

  SELECT product_id, price   FROM products   WHERE product_id IN (1, 2);  PRODUCT_ID PRICE ---------- ----------  1 23.94  2 39 

Everything looks good: product #1 s price is 20 percent greater, and product #2 s price is 30 percent greater. Okay, let s roll back the transaction to the savepoint established earlier:

 ROLLBACK TO SAVEPOINT save1; 

This should preserve the new price set for product #1, but it will roll back the price for product #2 to its original price before we began , because the savepoint was set before the change to product #2 s price was made:

  SELECT product_id, price   FROM products   WHERE product_id IN (1, 2);  PRODUCT_ID PRICE ---------- ----------  1 23.94  2 30 

These are the expected results: product #1 s new price is $23.94 and product #2 s price is back to the original. Finally, roll back the entire transaction:

 ROLLBACK; 

This rolls back the changes all the way to the start of the transaction and undoes the change made to the price of product #1.

ACID Transaction Properties

Earlier, I defined a transaction as being a logical unit of work , that is, a grouping of related SQL statements that are either committed or rolled back as one unit. One example of this is a transfer of money from one bank account to another using two UPDATE statements, one that takes money out of one account, and another that puts that money into a different account. Both UPDATE statements may be considered to be a single transaction because both statements must be either committed or rolled back together; otherwise, money might be lost.

Database theory has a more rigorous definition of a transaction and states that a transaction has four fundamental properties, known as ACID properties:

• Atomicity    Transactions are committed or rolled back as a group, and are atomic, meaning that all SQL statements contained in a transaction are considered to be a single indivisible unit.

• Consistency    Transactions ensure that the database state remains consistent, meaning that the database starts at one consistent state and ends in another consistent state when the transaction finishes.

• Isolation    Separate transactions should appear to run without interfering with each other.

• Durability    Once a transaction has been committed, the database changes are preserved, even if the machine on which the database software runs later crashes.

The Oracle database software ensures that each transaction possesses these ACID properties and has extensive recovery facilities for restoring databases that may have crashed for one reason or another.

Concurrent Transactions

The Oracle database supports many users interacting with the database at the same time, with each user running their own transactions at the same time. These transactions are known as concurrent transactions.

If users are running transactions that affect the same table, the effects of those transactions are separated from each other until a COMMIT is performed. The following sequence of events, based on two transactions named T1 and T2 accessing the customers table, illustrates the separation of transactions:

1. T1 and T2 perform a SELECT that retrieves all the rows from the customers table.

2. T1 performs an INSERT to add a row in the customers table, but T1 doesn t perform a COMMIT .

3. T2 performs another SELECT and retrieves the same rows as those in step 1. T2 doesn t see the new row added by T1 in step 2.

4. T1 finally performs a COMMIT to permanently record the new row added in step 2.

5. T2 performs another SELECT and finally sees the new row added by T1.

To summarize: T2 doesn t see the changes made by T1 until T1 commits its changes. This is the default level of isolation between transactions. As you ll learn later in the section Transaction Isolation Levels, you can change this level of isolation.

Table 8-1 shows example SQL statements that further illustrate concurrent transactions. The table shows the interleaved order in which statements are to be performed by two transactions named T1 and T2. T1 retrieves rows, adds a row, and updates a row in the customers table. T2 retrieves rows from the customers table. T2 doesn t see the changes made by T1 until T1 commits its changes.

Table 8-1: Concurrent Transactions

Transaction 1 T1	Transaction 2 T2
SELECT * FROM customers;	SELECT * FROM customers;
INSERT INTO customers (    customer_id, first_name, last_name ) VALUES (    7, ˜Jason', ˜Price' );
UPDATE customers SET last_name = ˜Orange' WHERE customer_id = 2;
SELECT * FROM customers; The returned result set contains the new row and the update.	SELECT * FROM customers; The returned result set doesn't contain the new row or the update made by T1. Instead, the result set contains the original rows.
COMMIT;
	SELECT * FROM customers; The returned result set contains the new row and the update made by T1.

You can enter the statements shown in Table 8-1 and see their results by starting two separate SQL*Plus sessions and connect as the store user for both sessions. Enter the statements in the interleaved order shown in the table into the SQL*Plus sessions.

Transaction Locking

To support concurrent transactions, an Oracle database must ensure that the data in the tables remains valid. It does this through the use of locks . Consider the following example in which two transactions named T1 and T2 attempt to modify customer #1 in the customers table:

1. T1 performs an UPDATE to modify customer #1, but T1 doesn t perform a COMMIT . T1 is said to have locked the row.

2. T2 also attempts to perform an UPDATE to modify customer #1, but since this row is already locked by T1, T2 is prevented from getting a lock on the row. T2 s UPDATE statement has to wait until T1 ends and frees the lock on the row.

3. T1 ends by performing a COMMIT , thus freeing the lock on the row.

4. T2 gets the lock on the row and the UPDATE is performed. T2 holds the lock on the row until T2 ends.

To summarize: A transaction cannot get a lock on a row while another transaction already holds the lock on that row.

Note  

The easiest way to understand default locking is: readers don t block readers, writers don t block readers, and writers only block writers when they attempt to modify the same row.

Transaction Isolation Levels

The transaction isolation level is the degree to which the changes made by one transaction are separated from other transactions running concurrently. Before you see the details of the various transaction isolation levels, you need to understand the types of problems that may occur when current transactions attempt to access the same rows in a table.

In the following bullets, you ll see examples of two concurrent transactions that are accessing the same rows to illustrate the three types of potential transaction processing problems:

• Phantom reads    T1 reads a set of rows returned by a specified WHERE clause. T2 then inserts a new row, which also happens to satisfy the WHERE clause of the query previously used by T1. T1 then reads the rows again using the same query, but now sees the additional row just inserted by T2. This new row is known as a phantom because to T1 this row seems to have magically appeared.

• Nonrepeatable reads    T1 reads a row, and T2 updates the same row just read by T1. T1 then reads the same row again and discovers that the row it read earlier is now different. This is known as a nonrepeatable read, because the row originally read by T1 has been changed.

• Dirty reads    T1 updates a row, but doesn t commit the update. T2 reads the updated row. T1 then performs a rollback, undoing the previous update. Now the row just read by T2 is no longer valid (it s dirty) because the update made by T1 wasn t committed when the row was read by T2.

To deal with these potential problems, databases implement various levels of transaction isolation to prevent concurrent transactions from interfering with each other. The SQL standard defines the following transaction isolation levels, shown in order of increasing isolation:

• READ UNCOMMITTED    Phantom reads, nonrepeatable reads, and dirty reads are permitted.

• READ COMMITTED    Phantom reads and nonrepeatable reads are permitted, but dirty reads are not.

• REPEATABLE READ    Phantom reads are permitted, but nonrepeatable and dirty reads are not.

• SERIALIZABLE    Phantom reads, nonrepeatable reads, and dirty reads are not permitted.

The Oracle database supports the READ COMMITTED and SERIALIZABLE transaction isolation levels. It doesn t support READ UNCOMMITTED or REPEATABLE READ levels.

The default transaction isolation level defined by the SQL standard is SERIALIZABLE , but the default used by the Oracle database is READ COMMITTED , which is usually acceptable for nearly all applications.

Caution  

Although you can use SERIALIZABLE with the Oracle database, it may increase the time your SQL statements take to complete, so you should only use SERILIZABLE if you absolutely have to.

You set the transaction isolation level using the SET TRANSACTION statement. For example, the following statement sets the transaction isolation level to SERIALIZABLE :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

You ll see an example of a transaction that uses the isolation level of SERIALIZABLE next.

A SERIALIZABLE Transaction Example

In this section, you ll see an example that shows the effect of setting the transaction isolation level to SERIALIZABLE .

The example uses two transactions named T1 and T2. T1 has the default isolation level of READ COMMITTED; T2 has a transaction isolation level of SERIALIZABLE . T1 and T2 will read the rows in the customers table, and then T1 will insert a new row and update an existing row in the customers table. Because T2 is SERIALIZABLE , it doesn t see the inserted row or the update made to the existing row by T1, even after T1 commits its changes. That s because reading the inserted row would be a phantom read, and reading the update would be a nonrepeatable read, which is not permitted by SERIALIZABLE transactions.

Table 8-2 shows the SQL statements that make up T1 and T2 in the interleaved order in which the statements are to be performed.

Table 8-2: Serializable Transactions

Transaction 1 T1 (READ COMMITTED)	Transaction 2 T2 (SERIALIZABLE)
	SET TRANSACTION ISOLATION  LEVEL SERIALIZABLE;
SELECT * FROM customers;	SELECT * FROM customers;
INSERT INTO customers (    customer_id, first_name, last_name ) VALUES (    8, ˜Steve', ˜Button' );
UPDATE customers SET last_name = ˜Yellow' WHERE customer_id = 3;
COMMIT;
SELECT * FROM customers; The returned result set contains the new row and the update.	SELECT * FROM customers; The returned result set still doesn't contain the new row or the update made by T1. That's because T2 is SERIALIZABLE.