Transactions are one of the features that set databases apart from file systems. In a file system, if you are in the middle of writing a file and the operating system crashes, that file will probably be corrupted, though there are “journaled” file systems and the like that may be able to recover your file to some point in time. However, if you need to keep two files synchronized, such a system won’t help—if you update one file and the system fails before you finish updating the second, your files won’t be synchronized.
This is the main purpose of transactions—they take the database from one consistent state to the next. That is their function. When you commit work in the database, you are assured that all of your changes, or none of them, have been saved. Furthermore, you are assured that the various rules and checks that protect data integrity were implemented.
Atomicity: Either all of a transaction happens or none of it happens.
Consistency: A transaction takes the database from one consistent state to the next.
Isolation: The effects of a transaction may not be visible to other transactions until the transaction has committed.
Durability: Once the transaction is committed, it is permanent.
In particular, we discussed how Oracle obtains consistency and isolation in the previous chapter. Here, we’ll focus most of our attention on the concept of atomicity and how that is applied in Oracle.
In this chapter, we’ll discuss the implications of atomicity and how it affects statements in Oracle. We’ll cover transaction control statements such as COMMIT, SAVEPOINT, and ROLLBACK, and we’ll discuss how integrity constraints are enforced in a transaction. We’ll also look at why you may have some bad transaction habits if you’ve been developing in other databases. We’ll look at distributed transactions and the two-phase commit (2PC). Lastly, we’ll examine autonomous transactions, what they are, and the role they play.
Transaction Control Statements
You don’t need a “begin transaction” statement in Oracle. A transaction implicitly begins with the first statement that modifies data (the first statement that gets a TX lock). You can explicitly begin a transaction using SET TRANSACTION or the DBMS_TRANSACTION package, but it is not a necessary step, unlike in some other databases. Issuing either a COMMIT or ROLLBACK statement explicitly ends a transaction.
Not all ROLLBACK statements are created equal. It should be noted that a ROLLBACK TO SAVEPOINT command will not end a transaction! Only a full, proper ROLLBACK will.
You should always explicitly terminate your transactions with a COMMIT or ROLLBACK; otherwise, the tool or environment you’re using will pick one or the other for you. If you exit your SQL*Plus session normally, without committing or rolling back, SQL*Plus assumes you wish to commit your work and it does so. If you just exit from a Pro*C program, on the other hand, an implicit rollback takes place. Never rely on implicit behavior, as it could change in the future. Always explicitly COMMIT or ROLLBACK your transactions.
As an example of something changing in the future, SQL*Plus in Oracle 11g Release 2 and above contains a setting, “exitcommit.” This setting controls whether SQL*Plus issues a COMMIT or ROLLBACK upon exit. So when you use 11g Release 2, the default behavior that has been in place since SQL*Plus was invented may well be different!
COMMIT: To use this statement’s simplest form, you just issue COMMIT. You could be more verbose and say COMMIT WORK, but the two are equivalent. A COMMIT ends your transaction and makes any changes permanent (durable). There are extensions to the COMMIT statement used in distributed transactions that allow you to label a COMMIT (label a transaction) with some meaningful comment and force the commit of an in-doubt distributed transaction. There are also extensions that allow you to perform an asynchronous commit—a commit that actually breaks the durability concept. We’ll take a look at this in a bit and see when it might be appropriate to use.
ROLLBACK: To use this statement’s simplest form, you just issue ROLLBACK. Again, you could be more verbose and say ROLLBACK WORK , but the two are equivalent. A rollback ends your transaction and undoes any uncommitted changes. It does this by reading information stored in the rollback/undo segments (going forward I’ll refer to these exclusively as undo segments) and restoring the database blocks to the state they were before your transaction began.
SAVEPOINT: A SAVEPOINT allows you to create a marked point within a transaction. You may have multiple SAVEPOINTs within a single transaction.
ROLLBACK TO <SAVEPOINT>: This statement is used with the SAVEPOINT command. You can roll back your transaction to that marked point without rolling back any of the work that preceded it. So, you could issue two UPDATE statements, followed by a SAVEPOINT and then two DELETE statements. If an error or some sort of exceptional condition occurs during execution of the DELETE statements, and you catch that exception and issue the ROLLBACK TO SAVEPOINT command , the transaction will roll back to the named SAVEPOINT, undoing any work performed by the DELETEs but leaving the work performed by the UPDATE statements intact.
SET TRANSACTION : This statement allows you to set various transaction attributes, such as the transaction’s isolation level and whether it is read-only or read-write. You can also use this statement to instruct the transaction to use a specific undo segment when using manual undo management, but this is not recommended. We’ll discuss manual and automatic undo management in more detail in the next chapter.
That’s it—there are no other transaction control statements. The most frequently used control statements are COMMIT and ROLLBACK. The SAVEPOINT statement has a somewhat special purpose. Internally, Oracle uses it frequently; in fact, Oracle uses it every time you execute any SQL or PL/SQL statement, and you may find some use for it in your applications as well.
Atomicity
Now we’re ready to see what’s meant by statement, procedure , and transaction atomicity.
Statement-Level Atomicity
So, one row was successfully inserted into T, and we duly received the message I fired and updated 1 rows. The next INSERT statement violates the integrity constraint we have on T. The DBMS_OUTPUT message appeared—the trigger on T in fact did fire, and we have evidence of that. The trigger performed its updates of T2 successfully. We might expect T2 to have a value of 2 now, but we see it has a value of 1. Oracle made the original INSERT atomic—the original INSERT INTO T is the statement, and any side effects of that original INSERT INTO T are considered part of that statement.
For programmers used to Sybase or SQL Server, this may be confusing at first. In those databases, exactly the opposite is true. The triggers in those systems execute independently of the firing statement. If they encounter an error, the triggers must explicitly roll back their own work and then raise another error to roll back the triggering statement. Otherwise, the work done by a trigger could persist even if the triggering statement, or some other part of the statement, ultimately fails.
In Oracle, this statement-level atomicity extends as deep as it needs to. In the preceding example, if the INSERT INTO T fires a trigger that updates another table, and that table has a trigger that deletes from another table (and so on and so forth), either all of the work succeeds or none of it does. You don’t need to code anything special to ensure this; it’s just the way it works.
Procedure-Level Atomicity
As you can see, Oracle treated the stored procedure call as an atomic statement. The client submitted a block of code—BEGIN P; END;—and Oracle wrapped a SAVEPOINT around it. Since P failed, Oracle restored the database back to the point right before it was called.
The preceding behavior—statement-level atomicity—relies on the PL/SQL routine not performing any commits or rollbacks. It is my opinion that COMMIT and ROLLBACK should not be used in general in PL/SQL; the invoker of the PL/SQL stored procedure is the only one that knows when a transaction is complete. It is a bad programming practice to issue a COMMIT or ROLLBACK in your developed PL/SQL routines.
Here, we ran a block of code that ignored any and all errors, and the difference in outcome is huge. Whereas the first call to P effected no changes, this time the first INSERT succeeds, and the CNT column in T2 is incremented accordingly.
Oracle considered the “statement” to be the block that the client submitted. This statement succeeded by catching and ignoring the error itself, so the If error then rollback... didn’t come into effect, and Oracle didn’t roll back to the SAVEPOINT after execution. Hence, the partial work performed by P was preserved. The reason this partial work is preserved in the first place is that we have statement-level atomicity within P: each statement in P is atomic. P becomes the client of Oracle when it submits its two INSERT statements. Each INSERT either succeeds or fails entirely. This is evidenced by the fact that we can see that the trigger on T fired twice and updated T2 twice, yet the count in T2 reflects only one UPDATE. The second INSERT executed in P had an implicit SAVEPOINT wrapped around it.
I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or RAISE_APPLICATION_ERROR to re-raise the exception to be a bug. It silently ignores the error, and it changes the transaction semantics. Catching WHEN OTHERS and translating the exception into an old-fashioned return code changes the way the database is supposed to behave.
So, if you include WHEN OTHERS in your code and it is not followed by a RAISE or RAISE_APPLICATION_ERROR, be aware that you are almost certainly looking at a bug in your developed code, a bug placed there by you.
The preceding code represents an exceedingly bad practice. In general, you should neither catch a WHEN OTHERS nor explicitly code what Oracle already provides as far as transaction semantics is concerned.
Here, by mimicking the work Oracle normally does for us with the SAVEPOINT, we are able to restore the original behavior while still catching and “ignoring” the error. I provide this example for illustration only; this is an exceedingly bad coding practice.
Transaction-Level Atomicity
The entire goal of a transaction, a set of SQL statements executed together as a unit of work, is to take the database from one consistent state to another consistent state. To accomplish this goal, transactions are atomic as well—the entire set of successful work performed by a transaction is either entirely committed and made permanent or rolled back and undone. Just like a statement, the transaction is an atomic unit of work. Upon receipt of “success” from the database after committing a transaction, you know that all of the work performed by the transaction has been made persistent.
DDL and Atomicity
- 1.
They begin by committing any outstanding work, ending any transaction you might already have in place.
- 2.
They perform the DDL operation, such as a CREATE TABLE.
- 3.
They commit the DDL operation if it was successful or roll back the DDL operation otherwise.
This means that any time you issue a DDL statement such as CREATE, ALTER, and so on, you must expect your existing transaction to be immediately committed and the subsequent DDL command to be performed and either committed and made durable or rolled back in the event of any error. DDL does not break the ACID concepts in any way, but the fact that it commits is something you definitely need to be aware of.
Durability
You use the WRITE extensions available in the COMMIT statement.
You issue COMMITs in a nondistributed (accesses only a single database, no database links) PL/SQL block of code.
We’ll look at each in turn.
WRITE Extensions to COMMIT
Oracle allows you to add a WRITE clause to your COMMIT statements. The WRITE clause allows the commit to either WAIT for the redo you generated to be written to disk (the default) or NOWAIT—to not wait—for the redo to be written. The NOWAIT option is the capability—a capability that must be used carefully, with forethought, and with understanding of exactly what it means.
Normally, a COMMIT is a synchronous process. Your application invokes COMMIT, and then your application waits for the entire COMMIT processing to be complete. This is the behavior of COMMIT in all the database releases before Oracle 10g Release 2 and is the default behavior in Oracle 10g Release 2 and above.
In current releases of the database, instead of waiting for the commit to complete, which may take measurable time since a commit involves a physical write—a physical I/O—to the redo log files stored on disk, you may have the commit performed in the background, without waiting for it. That comes with the side effect that your commit is no longer assured to be durable. That is, your application may get a response back from the database that the asynchronous commit you submitted was received; other sessions may be able to see your changes, but later find that the transaction you thought was committed was not. This situation will occur only in very rare cases and will always involve a serious failure of the hardware or software. It requires the database to be shut down abnormally in order for an asynchronous commit to not be durable, meaning the database instance or computer the database instance is running on would have to suffer a complete failure.
So, if transactions are meant to be durable, what is the potential use of a feature that might make them possibly not durable? Raw performance. When you issue a COMMIT in your application, you are asking the LGWR process to take the redo you’ve generated and ensure that it is written to the online redo log files. Performing physical I/O, which this process involves, is measurably slow; it takes a long time, relatively speaking, to write data to disk. So, a COMMIT may well take longer than the DML statements in the transaction itself! If you make the COMMIT asynchronous, you remove the need to wait for that physical I/O in the client application, perhaps making the client application appear faster—especially if it does lots of COMMITs.
This might suggest that you’d want to use this COMMIT WRITE NOWAIT all of the time—after all, isn’t performance the most important thing in the world? No, it is not. Most of the time, you need the durability achieved by default with COMMIT. When you COMMIT and report back to an end user “we have committed,” you need to be sure that the change is permanent. It will be recorded in the database even if the database/hardware failed right after the COMMIT. If you report to an end user that “Order 12352 has been placed,” you need to make sure that Order 12352 was truly placed and persistent. So, for most every application, the default COMMIT WRITE WAIT is the only correct option (note that you only need say COMMIT—the default setting is WRITE WAIT).
A custom data load program. It must be custom, since it will have additional logic to deal with the fact that a commit might not persist a system failure.
An application that processes a live data feed of some sort, say a stock quote feed from the stock markets that inserts massive amounts of time-sensitive information into the database. If the database goes offline, the data stream keeps on going, and the data generated during the system failure will never be processed (Nasdaq does not shut down because your database crashed, after all!). That this data is not processed is OK, because the stock data is so time sensitive; after a few seconds, it would be overwritten by new data anyway.
An application that implements its own “queuing” mechanism, for example, one that has data in a table with a PROCESSED_FLAG column. As new data arrives, it is inserted with a value of PROCESSED_FLAG='N' (unprocessed). Another routine is tasked with reading the PROCESSED_FLAG='N' records, performing some small, fast transaction and updating the PROCESSED_FLAG='N' to 'Y'. If it commits but that commit is later undone (by a system failure), it is OK because the application that processes these records will just process the record again—it is “restartable.”
If you look at these application categories, you’ll notice that all three of them are background, noninteractive applications. They do not interact with a human being directly. Any application that does interact with a person—that reports to the person “Commit complete”—should use the synchronous commit. Asynchronous commits are not a tuning device for your online customer-facing applications. Asynchronous commits are applicable only to batch-oriented applications, those that are automatically restartable upon failure. Interactive applications are not restartable automatically upon failure—a human being must redo the transaction. Therefore, you have another flag that tells you whether this capability can be considered—do you have a batch application or an interactive one? Unless it is batch oriented, synchronous commit is the way to go.
So, outside of those three categories of batch applications, this capability—COMMIT WRITE NOWAIT—should probably not be used. If you do use it, you need to ask yourself what would happen if your application is told commit processed, but later the commit is undone. You need to be able to answer that question and come to the conclusion that it will be OK if that happens. If you can’t answer that question, or if a committed change being lost would have serious repercussions, you should not use the asynchronous commit capability.
COMMITS in a Nondistributed PL/SQL Block
Since PL/SQL was first introduced in version 6 of Oracle, it has been transparently using an asynchronous commit. That approach has worked because all PL/SQL is like a batch program in a way—the end user does not know the outcome of the procedure until it is completely finished. That’s also why this asynchronous commit is used only in nondistributed PL/SQL blocks of code; if we involve more than one database, then there are two things—two databases—relying on the commit being durable. When two databases are relying on the commit being durable, we have to utilize synchronous protocols, or a change might be committed in one database but not the other.
Of course, pipelined PL/SQL functions deviate from “normal” PL/SQL functions. In normal PL/SQL functions, the outcome is not known until the end of the stored procedure call. Pipelined functions in general are able to return data to a client long before they complete (they return “chunks” of data to the client, a bit at a time). But since pipelined functions are called from SELECT statements and would not be committing anyway, they do not come into play in this discussion.
Therefore, PL/SQL was developed to utilize an asynchronous commit, allowing the COMMIT statement in PL/SQL to not have to wait for the physical I/O to complete (avoiding the “log file sync” wait). That does not mean that you can’t rely on a PL/SQL routine that commits and returns control to your application to not be durable with respect to its changes—PL/SQL will wait for the redo it generated to be written to disk before returning to the client application—but it will only wait once, right before it returns.
The following example demonstrates a bad practice—one that I call “slow-by-slow processing” or “row-by-row processing,” as row-by-row is synonymous with slow-by-slow in a relational database. It is meant just to illustrate how PL/SQL processes a COMMIT statement.
So, the commits performed in the routine are done with WRITE NOWAIT, and before the PL/SQL block of code returns to the client application, PL/SQL makes sure that the last bit of redo it generated was safely recorded to disk—making the PL/SQL block of code and its changes durable.
Integrity Constraints and Transactions
It is interesting to note exactly when integrity constraints are checked. By default, integrity constraints are checked after the entire SQL statement has been processed. There are also deferrable constraints that permit the validation of integrity constraints to be postponed until either the application requests they be validated by issuing a SET CONSTRAINTS ALL IMMEDIATE command or upon issuing a COMMIT.
IMMEDIATE Constraints
For the first part of this discussion, we’ll assume that constraints are in IMMEDIATE mode , which is the norm. In this case, the integrity constraints are checked immediately after the entire SQL statement has been processed. Note that I used the term “SQL statement,” not just “statement.” If I have many SQL statements in a PL/SQL stored procedure, each SQL statement will have its integrity constraints validated immediately after its individual execution, not after the stored procedure completes.
If Oracle checked the constraint after each row was updated, on any given day we would stand a 50-50 chance of having the UPDATE fail. The rows in T are accessed in some order, and if Oracle updated the X=1 row first, we would momentarily have a duplicate value for X, and it would reject the UPDATE. Since Oracle waits patiently until the end of the statement, the statement succeeds because by the time it is done, there are no duplicates.
DEFERRABLE Constraints and Cascading Updates
In Oracle, we also have the ability to defer constraint checking, which can be quite advantageous for various operations. The one that immediately jumps to mind is the requirement to cascade an UPDATE of a primary key to the child keys. Many people claim you should never need to do this—that primary keys are immutable (I am one of those people), but many others persist in their desire to have a cascading UPDATE. Deferrable constraints make this possible.
It is considered an extremely bad practice to perform update cascades to modify a primary key. It violates the intent of the primary key. If you have to do it once to correct bad information, that’s one thing, but if you find you are constantly doing it as part of your application, you will want to go back and rethink that process—you have chosen the wrong attributes to be the key!
It full scanned the table. It had to full scan the table in order to count the rows. This is due to the fact that in an Oracle B*Tree index, index key entries that are entirely null are not made. That is, the index will not contain an entry for any row in the table T, such that all of the columns in the index are null. Since X is allowed to be null temporarily, the optimizer has to assume that X might be null and therefore would not be in the index on X. Hence, a count returned from the index might be different (wrong) from a count against the table.
So, the bottom line is, only use deferrable constraints where you have an identified need to use them. They introduce subtle side effects that could cause differences in your physical implementation (nonunique vs. unique indexes) or in your query plans—as just demonstrated!
Bad Transaction Habits
Many developers have some bad habits when it comes to transactions. I see this frequently with developers who have worked with a database that “supports” but does not “promote” the use of transactions. For example, in Informix (by default), Sybase, and SQL Server, you must explicitly BEGIN a transaction; otherwise, each individual statement is a transaction all by itself. In a similar manner to the way in which Oracle wraps a SAVEPOINT around discrete statements, these databases wrap a BEGIN WORK/COMMIT or ROLLBACK around each statement. This is because, in these databases, locks are precious resources, and readers block writers and vice versa. In an attempt to increase concurrency, these databases want you to make the transaction as short as possible—sometimes at the expense of data integrity.
Oracle takes the opposite approach. Transactions are always implicit, and there is no way to have an “autocommit” unless an application implements it (see the “Using Autocommit” section later in this chapter for more details). In Oracle, every transaction should be committed when it must and never before. Transactions should be as large as they need to be. Issues such as locks, blocking, and so on should not really be considered the driving forces behind transaction size—data integrity is the driving force behind the size of your transaction. Locks are not a scarce resource, and there are no contention issues between concurrent readers and writers of data. This allows you to have robust transactions in the database. These transactions do not have to be short in duration—they should be exactly as long as they need to be (but no longer). Transactions are not for the convenience of the computer and its software; they are to protect your data.
Committing in a Loop
It is faster and more efficient to frequently commit lots of small transactions than it is to process and commit one big transaction.
We don’t have enough undo space.
Both of these reasons are misguided. Furthermore, committing too frequently leaves you prone to the danger of leaving your database in an “unknown” state should your update fail halfway through. It requires complex logic to write a process that is smoothly restartable in the event of failure. By far the best option is to commit only as frequently as your business processes dictate and to size your undo segments accordingly.
Let’s take a look at these issues in more detail.
Performance Implications
Not only that, but you should notice that the code is getting more and more complex. From the sheer simplicity of a single UPDATE statement , to procedural code, to even more complex procedural code—we are going in the wrong direction! Furthermore (yes, there is more to complain about), the preceding procedural code is not done yet. It doesn’t deal with “what happens when we fail” (not if we but rather when we). What happens if this code gets halfway done and then the system fails? How do you restart the procedural code with a commit? You’d have to add yet more code so you knew where to pick up and continue processing. With the single UPDATE statement, we just reissue the UPDATE. We know that it will entirely succeed or entirely fail; there will not be partial work to worry about. We visit this point more in the section “Restartable Processes Require Complex Logic.”
Now, just to supply a counterpoint to this discussion, recall in the previous chapter when we discussed the concept of write consistency and how an UPDATE statement, for example, could be made to restart. In the event that the preceding UPDATE statement was to be performed against a subset of the rows (it had a WHERE clause, and other users were modifying the columns this UPDATE was using in the WHERE clause), then there would be a case either for using a series of smaller transactions rather than one large transaction or for locking the table prior to performing the mass update. The goal here would be to reduce the opportunity for restarts to occur.
If we were to UPDATE the vast majority of the rows in the table, that would lead us toward using the LOCK TABLE command. In my experience, however, these sorts of large mass updates or mass deletes (the only statement types really that would be subject to the restart) are done in isolation. That large, one-time bulk update or the purge of old data generally is not done during a period of high activity. Indeed, the purge of data should not be affected by this at all, since you would typically use some date field to locate the information to purge, and other applications would not modify this data.
Snapshot Too Old Error
Let’s now look at the second reason developers are tempted to commit updates in a procedural loop, which arises from their (misguided) attempts to use a “limited resource” (undo segments) sparingly. This is a configuration issue; you need to ensure that you have enough undo space to size your transactions correctly. Committing in a loop, apart from generally being slower, is also the most common cause of the dreaded ORA-01555 error. Let’s look at this in more detail.
As you will appreciate after reading the previous chapter, Oracle’s multiversioning model uses undo segment data to reconstruct blocks as they appeared at the beginning of your statement or transaction (depending on the isolation mode). If the necessary undo information no longer exists, you will receive an ORA-01555: snapshot too old error message, and your query will not complete. So, if you are modifying the table that you are reading (as in the previous example), you are generating undo information required for your query. Your UPDATE generates undo information that your query will probably be making use of to get the read-consistent view of the data it needs to update. If you commit, you are allowing the system to reuse the undo segment space you just filled up. If it does reuse the undo, wiping out old undo data that your query subsequently needs, you are in big trouble. Your SELECT will fail and your UPDATE will stop partway through. You have a partly finished logical transaction and probably no good way to restart it (more about this in a moment).
I get the error. I should point out that I added an index hint to the query and a WHERE clause to make sure I was reading the table randomly (together, they caused the cost-based optimizer to read the table “sorted” by the index key). When we process a table via an index, we tend to read a block for a single row, and then the next row we want will be on a different block. Ultimately, we will process all of the rows on block 1, just not all at the same time. Block 1 might hold, say, the data for all rows with OBJECT_NAMEs starting with the letters A, M, N, Q, and Z. So we would hit the block many times, since we are reading the data sorted by OBJECT_NAME, and presumably many OBJECT_NAMEs start with letters between A and M. Since we are committing frequently and reusing undo space, we eventually revisit a block where we can simply no longer roll back to the point our query began, and at that point we get the error.
This was a very artificial example just to show how it happens in a reliable manner. My UPDATE statement was generating undo. I had a very small undo tablespace to play with (10MB). I wrapped around in my undo segments many times, since they are used in a circular fashion. Every time I committed, I allowed Oracle to overwrite the undo data I generated. Eventually, I needed some piece of data I had generated, but it no longer existed and I received the ORA-01555 error.
The ORA-01555 example left my update in a totally unknown state. Some of the work had been done; some had not.
There is absolutely nothing I can do to avoid the ORA-01555 error, given that I committed in the cursor FOR loop.
The ORA-30036 error can be avoided by allocating appropriate resources in the system. This error is avoidable by correct sizing; the first error is not. Further, even if I don’t avoid this error, at least the update is rolled back, and the database is left in a known, consistent state—not halfway through some large update.
The bottom line here is that you can’t “save” on undo space by committing frequently—you need that undo. I was in a single-user system when I received the ORA-01555 error . It takes only one session to cause that error, and many times even in real life it is a single session causing its own ORA-01555 errors. Developers and DBAs need to work together to size these segments adequately for the jobs that need to be done. There can be no shortchanging here. You must discover, through analysis of your system, what your biggest transactions are and size appropriately for them. The dynamic performance view V$UNDOSTAT can be very useful to monitor the amount of undo you are generating and the duration of your longest running queries. Many people consider things like temp, undo, and redo as overhead—things to allocate as little storage to as possible. This is reminiscent of a problem the computer industry had on January 1, 2000, which was all caused by trying to save 2 bytes in a date field. These components of the database are not overhead, but rather are key components of the system. They must be sized appropriately (not too big and not too small).
If you don’t set your undo tablespace back to a normally sized one, you’ll be hitting ORA-30036 errors for the rest of the book!
Restartable Processes Require Complex Logic
Now, we could restart this process if it fails, since we would not process any object name that had already been processed successfully. The problem with this approach, however, is that unless we have some attribute that evenly partitions the data, we will end up having a very wide distribution of rows. The second UPDATE did more work than all of the others combined. Additionally, if other sessions are accessing this table and modifying the data, they might update the OBJECT_NAME field as well. Suppose that some other session updates the object named Z to be A, after we already processed the As. We would miss that record. Furthermore, this is a very inefficient process compared to UPDATE T SET LAST_DDL_TIME = LAST_DDL_TIME+1. We are probably using an index to read every row in the table, or we are full scanning it n times, both of which are undesirable. There are so many bad things to be said about this approach.
The best approach is to do it simply. If it can be done in SQL, do it in SQL. What can’t be done in SQL, do in PL/SQL. Do it using the least amount of code you can. Have sufficient resources allocated. Always think about what happens in the event of an error. So many times, I’ve seen people code update loops that worked great on the test data but then failed halfway through when applied to the real data. Then they are really stuck, as they have no idea where the loop stopped processing. It’s a lot easier to size undo correctly than to write a restartable program. If you have truly large tables that need to be updated, you should be using partitions, which you can update each individually. You can even use parallel DML to perform the update or, in Oracle 11g Release 2 and above, the DBMS_PARALLEL_EXECUTE package.
Using Autocommit
If your program is using ODBC or JDBC when you submit these statements, they (silently) inject a commit after each UPDATE. Consider the impact of this if the system fails after the first UPDATE and before the second. You’ve just lost $1000!
This returns control over the transaction back to you, the developer, which is where it belongs. You can then safely code your account transfer transaction and commit it after both statements have succeeded. Lack of knowledge of your API can be deadly in this case. I’ve seen more than one developer unaware of this autocommit “feature” get into big trouble with their application when an error occurred.
Distributed Transactions
One of the really nice features of Oracle is its ability to transparently handle distributed transactions . I can update data in many different databases in the scope of a single transaction. When I commit, either I commit the updates in all of the instances or I commit none of them (they will all be rolled back). I need no extra code to achieve this; I simply “commit.”
That’s it. Oracle will commit either in both databases or in neither. It uses a two-phase commit protocol (2PC) to do this. 2PC is a distributed protocol that allows for a modification that affects many disparate databases to be committed atomically. It attempts to close the window for distributed failure as much as possible before committing. In a 2PC between many databases, one of the databases—typically the one the client is logged into initially—will be the coordinator for the distributed transaction. This one site will ask the other sites if they are ready to commit. In effect, this site will go to the other sites and ask them to be prepared to commit. Each of the other sites reports back its “prepared state” as YES or NO. If any one of the sites votes NO, the entire transaction is rolled back. If all sites vote YES, the site coordinator broadcasts a message to make the commit permanent on each of the sites.
This limits the window in which a serious error could occur. Prior to the “voting” on the 2PC, any distributed error would result in all of the sites rolling back. There would be no doubt as to the outcome of the transaction. After the order to commit or rollback, there again is no doubt as to the outcome of the distributed transaction. It is only during the very short window when the coordinator is collecting the votes that the outcome might be in doubt, after a failure.
Assume, for example, we have three sites participating in the transaction with Site 1 being the coordinator. Site 1 has asked Site 2 to prepare to commit, and Site 2 has done so. Site 1 then asks Site 3 to prepare to commit, and it does so. At this point, Site 1 is the only site that knows the outcome of the transaction, and it is now responsible for broadcasting the outcome to the other sites. If an error occurs right now—the network fails, Site 1 loses power, whatever—Sites 2 and 3 will be left hanging. They will have what is known as an in-doubt distributed transaction. The 2PC protocol attempts to close the window of error as much as possible, but it can’t close it entirely. Sites 2 and 3 must keep that transaction open, awaiting notification of the outcome from Site 1.
It is the function of the RECO background process to resolve this issue. This is also where COMMIT and ROLLBACK with the FORCE option come into play. If the cause of the problem was a network failure between Sites 1, 2, and 3, then the DBAs at Sites 2 and 3 could actually call the DBA at Site 1, ask them for the outcome, and apply the commit or rollback manually, as appropriate.
You can’t issue a COMMIT over a database link. That is, you can’t issue a COMMIT@remote_site. You may commit only from the site that initiated the transaction.
You can’t do DDL over a database link. This is a direct result of the preceding issue. DDL commits. You can’t commit from any site other than the initiating site; hence, you can’t do DDL over a database link.
You can’t issue a SAVEPOINT over a database link. In short, you can’t issue any transaction control statements over a database link. All transaction control is inherited from the session that opened the database link in the first place; you can’t have different transaction controls in place in the distributed instances in your transaction.
The lack of transaction control over a database link is reasonable, since the initiating site is the only one that has a list of everyone involved in the transaction. If in our three-site configuration, Site 2 attempted to commit, it would have no way of knowing that Site 3 was involved. In Oracle, only Site 1 can issue the commit command. At that point, it is permissible for Site 1 to delegate responsibility for distributed transaction control to another site.
We can influence which site will be the actual commit site by setting the COMMIT_POINT_STRENGTH (a parameter) of the site. A COMMIT_POINT_STRENGTH associates a relative level of importance to a server in a distributed transaction. The more important the server (the more available the data needs to be), the more probable that it will coordinate the distributed transaction. You might want to do this if you need to perform a distributed transaction between your production machine and a test machine. Since the transaction coordinator is never in doubt as to the outcome of a transaction, it’s best if the production machine coordinated the distributed transaction. You don’t care so much if your test machine has some open transactions and locked resources. You certainly do care if your production machine does.
The inability to do DDL over a database link is actually not so bad at all. First, DDL is rare. You do it once at installation or during an upgrade. Production systems don’t do DDL (well, they shouldn’t do DDL). Second, there is a method to do DDL over a database link, in a fashion, using the job queue facility DBMS_SCHEDULER. Instead of trying to do DDL over the link, you use the link to schedule a remote job to be executed as soon as you commit. In that fashion, the job runs on the remote machine, is not a distributed transaction, and can do the DDL. In fact, this is the method by which the Oracle Replication Services perform distributed DDL to do schema replication.
Autonomous Transactions
Top-level anonymous blocks
Local (a procedure in a procedure), stand-alone, or packaged functions and procedures
Methods of object types
Database triggers
Before we take a look at how autonomous transactions work, I’d like to emphasize that this type of transaction is a powerful and therefore dangerous tool when used improperly. The true need for an autonomous transaction is very rare indeed. I would be very suspicious of any code that makes use of them—that code would get extra examination. It is far too easy to accidentally introduce logical data integrity issues into a system using them. In the sections that follow, we’ll discuss when they may safely be used after seeing how they work.
How Autonomous Transactions Work
Next, we’ll create two procedures, each of which simply INSERTs its name into the message table and commits. However, one of these procedures is a normal procedure, and the other is coded as an autonomous transaction. We’ll use these objects to show what work persists (is committed) in the database under various circumstances.
Note the use of the pragma AUTONOMOUS_TRANSACTION. This directive tells the database that this procedure, when executed, is to be executed as a new autonomous transaction, independent from its parent transaction.
A pragma is simply a compiler directive, a method to instruct the compiler to perform some compilation option. Other pragmas are available. Refer to the Oracle Database PL/SQL Language Reference manual; you’ll find a list of them in its index.
Here, only the work done by and committed in the autonomous transaction persists. The INSERT done in the anonymous block was rolled back by the ROLLBACK statement on line 4. The autonomous transaction procedure’s COMMIT has no effect on the parent transaction started in the anonymous block. In a nutshell, this captures the essence of autonomous transactions and what they do.
To summarize, if you COMMIT inside a “normal” procedure, it will make durable not only its own work but also any outstanding work performed in that session. However, a COMMIT performed in a procedure with an autonomous transaction will make durable only that procedure’s work.
When to Use Autonomous Transactions
The Oracle database has supported autonomous transactions internally for quite a while. We see them all of the time in the form of recursive SQL. For example, a recursive transaction may be performed when selecting from a sequence, in order for you to increment the sequence immediately in the SYS.SEQ$ table . The update of the SYS.SEQ$ table in support of your sequence is immediately committed and visible to other transactions, but your transaction is not yet committed. Additionally, if you roll back your transaction, the increment to the sequence remains in place; it is not rolled back with your transaction, as it has already been committed. Space management, auditing, and other internal operations are performed in a similar recursive fashion.
This feature has now been exposed for all to use. However, I have found that the legitimate real-world use of autonomous transactions is very limited. Time after time, I see them used as a workaround to such problems as a mutating table constraint in a trigger. This almost always leads to data integrity issues, however, since the cause of the mutating table is an attempt to read the table upon which the trigger is firing. Well, by using an autonomous transaction, you can query the table, but you are querying the table now without being able to see your changes (which is what the mutating table constraint was trying to do in the first place; the table is in the middle of a modification, so query results would be inconsistent). Any decisions you make based on a query from that trigger would be questionable—you are reading “old” data at that point in time.
A potentially valid use for an autonomous transaction is in custom auditing, but I stress the words “potentially valid.” There are more efficient ways to audit information in the database than via a custom-written trigger. For example, you can use the DBMS_FGA package or just the AUDIT command itself.
A question that application developers often pose to me is, “How can I log errors in my PL/SQL routines in a manner that will persist, even when my PL/SQL routines’ work is rolled back?” Earlier, we described how PL/SQL statements are atomic—they either completely succeed or completely fail. If we logged an error in our PL/SQL routines, by default our logged error information would roll back when Oracle rolled back our statement. Autonomous transactions allow us to change that behavior, to have our error logging information persist even while the rest of the partial work is rolled back.
The “magic” of this routine is on line 5 where we used the pragma autonomous_transaction directive to inform PL/SQL that we want this subroutine to start a new transaction, perform some work in it, and commit it—without affecting any other transaction currently in process. The COMMIT on line 9 can affect only the SQL performed by this LOG_ERROR procedure .
In my experience, that is the only truly valid use of an autonomous transaction—to log errors or informational messages in a manner that can be committed independently of the parent transaction.
Summary
In this chapter, we looked at many aspects of transaction management in Oracle. Transactions are among the major features that set a database apart from a file system. Understanding how they work and how to use them is necessary to implement applications correctly in any database. Understanding that in Oracle all statements are atomic (including their side effects) and that this atomicity is extended to stored procedures is crucial. We saw how the placement of a WHEN OTHERS exception handler in a PL/SQL block could radically affect what changes took place in the database. As database developers, having a good understanding of how transactions work is crucial.
We took a look at the somewhat complex interaction between integrity constraints (unique keys, check constraints, and the like) and transactions in Oracle. We discussed how Oracle typically processes integrity constraints immediately after a statement executes, but that we can defer this constraint validation until the end of the transaction if we wish. This feature is key in implementing complex multitable updates when the tables being modified are all dependent on each other—the cascading update is an example of that.
We moved on to consider some of the bad transaction habits that people tend to pick up from working with databases that “support” rather than “promote” the use of transactions. We looked at the cardinal rule of transactions: they should be as short as they can be but as long as they need to be. Data integrity drives the transaction size—that is a key concept to take away from this chapter. The only things that should drive the size of your transactions are the business rules that govern your system. Not undo space, not locks—business rules.
We covered distributed transactions and how they differ from single database transactions. We explored the limitations imposed upon us in a distributed transaction and discussed why they exist. Before you build a distributed system, you need to understand these limitations. What works in a single instance might not work in a distributed database.
The chapter closed with a look at autonomous transactions and covered what they are and, more important, when they should and should not be used. I would like to emphasize once again that the legitimate real-world use of autonomous transactions is exceedingly rare. If you find them to be a feature you are using constantly, you’ll want to take a long, hard look at why.