The Right TransactionBy Mark Williams
Choose the ODP.NET transaction type that fits your business rules.
Oracle Data Provider for .NET (ODP.NET) offers two distinct transaction types: implicit and explicit. Without the proper understanding of these transaction types, it is possible to create an application that does not function correctly or that leads to data inconsistencies or corruption.
In this column, I explain these two transaction types and discuss the circumstances under which each is appropriate. The sample application that accompanies this column, illustrates the basic use of each of these transaction types.
To best execute the sample application (Microsoft Visual Studio solution) that accompanies this column, you should have the following installed:
If your environment does not include these versions of these components, make adjustments as required to fit your environment.
In the context of this column, a database transaction simply means a logical unit of work performed in the database. This unit of work can consist of a single simple step (such as updating a value in a table) or many complex steps (such as updating values in some tables, deleting rows from other tables, inserting rows into another table, and so on). The number and types of steps are dictated by the business rules, but a guiding practice when it comes to database transactions is not to end the transaction until the entire unit of work is complete.
The implicit transaction is the default when you are using ODP.NET, and, as its name implies, the implicit transaction does not require any special action on your part—it happens implicitly. This is also known as “auto-commit,” because the transaction is automatically committed if no error is raised. When a transaction is committed, it means that any changes made by the transaction become permanent in the database. It is also at commit that the changes become visible to other database users.
Although an implicit transaction is automatically committed if no error is raised, what happens if an error is raised? The implicit transaction is automatically rolled back. This means that any changes made by the transaction are undone and that the database looks as it did before any changes were attempted. Other database users never see the changes that have been undone.
With an implicit transaction, the commit or rollback automatically happens when you invoke the ExecuteNonQuery method of an OracleCommand object. The following invokes the method for an OracleCommand object named “cmd”:
The opposite of the implicit transaction is the explicit transaction. As you have likely guessed, the explicit transaction does require some action on your part. However, this action is minimal and consists of only a few steps. To execute an explicit transaction, you first acquire an OracleTransaction object from an OracleConnection object by invoking the BeginTransaction method. Note that this is the only way to acquire the transaction object—there is no constructor available to create the object. Assuming that you have an OracleConnection object named “con,” you acquire the transaction object as follows:
OracleTransaction txn = con.BeginTransaction();
Acquiring the transaction object from the connection object begins the transaction. To end the transaction, you invoke either the Commit or the Rollback method on the transaction object.
Suppose that after beginning some number of updates, deletes, and inserts, you want to make the changes permanent. You commit the transaction as follows:
Now suppose that after beginning some number of updates, deletes, and inserts, you want to undo the transaction’s changes, perhaps because of an error. You roll back the transaction as follows:
What happens if you fail to commit or roll back an explicit transaction? Oracle Database will roll back the transaction on your behalf.
When to Use Each Transaction
If you take no steps in your code to implement explicit transactions, you will, by default, use implicit transactions. However, as discussed above, business rules ultimately dictate the steps that are involved in a transaction. A classic example in discussions of database transactions (in fact, it is the example used in this column’s sample application) is the transfer of funds from one bank account to another. This bank transfer transaction naturally involves two steps:
1. Decrement the source account by the desired amount.
Both transaction steps must successfully complete for this transaction to be logically complete. If implicit transactions are used in this case, however, and there is a failure after the first step (which is implicitly committed) but before the second step, the data may become logically inconsistent. That is, money “has gone missing” because the balance in the first account has been decremented (and committed) but the balance in the second account has not yet been incremented.
Determining when to use each transaction type is a straightforward process. Use the explicit transaction if either of the following is true:
Use the implicit transaction if both of the following are true:
The Sample Application
This column’s sample application is a very simple implementation of the classic transaction example: transferring funds from one account into another. The comments in the code indicate where an implicit transaction is employed and where an explicit transaction is employed.
The sample application connects with the HR sample user that ships as part of Oracle Database. Of course, you can choose to connect the sample application to any database user in your environment. If you do connect to a different user, adjust the sample application code to connect to that user.
The sample application also uses a new database table. To create this table, you can use your favorite tool, such as Oracle Developer Tools for Visual Studio .NET, Oracle SQL Developer, SQL*Plus, or another tool. Here is the SQL I used to create the table:
create table MayJun2009 ( account_id number(4) primary key, balance number(6,2) );
Next I added two rows of data to the table, using SQL*Plus, as follows:
insert into MayJun2009 values (1, 1000); insert into MayJun2009 values (2, 1000); commit;
To create the sample application, create a new .NET console application, add a reference to the ODP.NET assembly on your system, and replace the generated code with the code from the Program.cs file in the download.
The sample application performs the following basic steps:
1. Updates the balance for Account ID 1, using an implicit transaction
Now execute the sample application in Visual Studio. To see the difference between implicit and explicit transactions, step through the code in debug mode. I also recommend that you use your favorite tool to query the MayJun2009 sample database table during the various steps to clearly see when the data resulting from the transactions becomes visible to other database sessions.
Even though this is a simple application, you can also use it to further experiment with the ODP.NET transactions. Here are a few suggested experiments:
Mark Williams (email@example.com) is an Oracle ACE director, an Oracle Certified Professional DBA, the author of Pro .NET Oracle Programming (Apress, 2004), and a contributor to the Oracle Data Provider for .NET forum on Oracle Technology Network.