Chapter 4

Overview

Transactions are one of the features that set a database apart from a file system. In a file system, if you are in the middle of writing a file and the operating system crashes, this file is likely to be corrupted. It is true there are 'journaled' file systems, and the like, which may be able to recover your file to some point in time. However, if you need to keep two files synchronized, it won't help you there - if you update one file, and the system fails before you finish updating the second then you will have out-of-sync files.

This is the main purpose of transactions in the database - they take the database from one consistent state to the next. That is their job. When you commit work in the database, you are assured that either all of your changes have been saved, or none of them are saved. Furthermore, you are assured that your various rules and checks that implement data integrity are carried out.

Database transactions should exhibit attributes described by the ACID properties. ACID is an acronym for:

  • Atomicity - A transaction either happens completely, 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.

Transactions in Oracle exhibit all of the above characteristics. 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 discuss how integrity constraints and such are enforced in a transaction. We will also look at why you may have some bad transaction habits if you have been developing in other databases. We will look at distributed transactions and the two-phase commit. Lastly, we will look at some real-world issues with regards to transactions, how they are logged, and what role rollback segments might play.

Transaction Control Statements

There is no 'begin transaction' statement in Oracle. A transaction implicitly begins with the first statement that modifies data (the first statement that gets a TX lock). Issuing either a COMMIT or ROLLBACK statement explicitly ends transactions. You should always explicitly terminate your transactions with a COMMIT or ROLLBACK - otherwise the tool/environment you are using will pick one or the other for you. If you exit your SQL*PLUS session normally without committing or rolling back, SQL*PLUS will assume you wish you commit your work, and will do so for you. If you terminate a Pro*C program on the other hand, a rollback will take place.

Transactions are atomic in Oracle - either every statement that comprises the transaction is committed (made permanent), or all of the statements are rolled back. This protection is extended to individual statements as well. A statement either entirely succeeds, or it is entirely rolled back. Note that I said the statement is rolled back. The failure of one statement does not cause previously executed statements to be automatically rolled back. Their work is preserved and must either be committed or rolled back by you. Before we get into the details of exactly what it means for a statement and transaction to be 'atomic', we will take a look at the various transaction control statements available to us. They are:

  • COMMIT - In its simplest form, you would 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. These extensions allow you to label a COMMIT (label a transaction) with some meaningful comment, and to force the commit of an in-doubt distributed transaction.

  • ROLLBACK - In its simplest form, you would 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 you have outstanding. It does this by reading information stored in the rollback segments, and restoring the database blocks to the state they were in prior to your transaction beginning.

  • 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 is used with the SAVEPOINT command above. You may 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 or exceptional condition, occurs during execution of the DELETE statements, the transaction will rollback to the named SAVEPOINT, undoing the DELETEs but not the UPDATE statements.

  • SET TRANSACTION - This statement allows you to set various transaction attributes, such as its isolation level and whether it is read-only or readwrite. You can also use this statement to instruct the transaction to use a specific rollback segment.

That's it - there are no more. The most frequently used control statements are COMMIT and ROLLBACK. The SAVEPOINT statement has a somewhat special purpose. Internally, Oracle uses it frequently, and you may find some use for it in your application as well.

Now that we've had a brief overview of the transaction control statements, we are ready to see what is meant by statement and transaction atomicity. Consider the following statement:

Insert into t values ( 1 ); 

It seems fairly clear that if it fails due to a constraint violation, our row will not be inserted. However, consider the following example, where an insert or delete on table T fires a trigger that adjusts the cnt column in table T2, appropriately:

tkyte@TKYTE816> create table t2 ( cnt int ); Table created.      tkyte@TKYTE816> insert into t2 values ( 0 ); 1 row created.      tkyte@TKYTE816> create table t ( x int check ( x>0 ) ); Table created.      tkyte@TKYTE816> create trigger t_trigger   2  before insert or delete on t for each row   3  begin   4     if ( inserting ) then   5          update t2 set cnt = cnt +1;   6     else   7          update t2 set cnt = cnt -1;   8     end if;   9     dbms_output.put_line( 'I fired and updated '  ||                                         sql%rowcount || ' rows' );  10  end;  11  / Trigger created. 

In this situation it is less clear what should happen. If the error occurs after the trigger has fired, should the effects of the trigger be there or not? That is, if the trigger fired and updated T2, but the row was not inserted into T, what should the outcome be? Clearly the answer is that we would not like the cnt column in T2 to be incremented if a row is not actually inserted into T. Fortunately, in Oracle, the original statement from the client, the INSERT INTO T in this case, either entirely succeeds, or entirely fails. This statement is atomic. We can confirm this, as follows:

tkyte@TKYTE816> set serveroutput on      tkyte@TKYTE816> insert into t values ( 1 ); I fired and updated 1 rows      1 row created.      tkyte@TKYTE816> insert into t values (-1 ); insert into t values (-1 ) * ERROR at line 1: ORA-02290: check constraint (TKYTE.SYS_C001570) violated           tkyte@TKYTE816> exec null  /* this is needed to retrieve the dbms_output */ I fired and updated 1 rows      PL/SQL procedure successfully completed.      tkyte@TKYTE816> select * from t2;             CNT ----------          1 

We successfully inserted one row into T, duly receiving the message, I fired and updated 1 rows. The next INSERT statement violates the integrity constraint we have on T. I needed to exec NULL, to run a Null statement, and get SQL*PLUS to show me the DBMS_OUTPUT information, (since SQL*PLUS will not print out the DBMS_OUTPUT buffer after a SELECT), but this shows that again, the trigger fired and updated one row. We would maybe expect T2 to have a value of 2 now, but we see it has a value of 1. Oracle made the original insert atomic.

It does this by silently wrapping a SAVEPOINT around each of our calls. The above two inserts were really treated like this:

Savepoint statement1;    Insert into t values ( 1 ); If error then rollback to statement1; Savepoint statement2;    Insert into t values ( -1 ); If error then rollback to statement2; 

For programmers used to Sybase or SQLServer, 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. If in the above example, the INSERT INTO T fired a trigger that updates another table, and that table has a trigger that deletes from another table (and so on, and so on) either all of the work succeeds, or none does. You do not need to code anything special to ensure this - it is the way it works.

It is interesting to note that Oracle considers PL/SQL anonymous blocks to be statements as well. Consider the following stored procedure:

tkyte@TKYTE816> create or replace procedure p   2  as   3  begin   4          insert into t values ( 1 );   5          insert into t values (-1 );   6  end;   7  / Procedure created.      tkyte@TKYTE816> select * from t; no rows selected      tkyte@TKYTE816> select * from t2;             CNT ----------          0 

So, we have a procedure we know will fail. The second insert will always fail in this case. Let's see what happens if we just run that stored procedure:

tkyte@TKYTE816> begin   2    p;   3  end;   4  / I fired and updated 1 rows I fired and updated 1 rows begin * ERROR at line 1: ORA-02290: check constraint (TKYTE.SYS_C001570) violated ORA-06512: at "TKYTE.P", line 5 ORA-06512: at line 2           tkyte@TKYTE816> select * from t;      no rows selected      tkyte@TKYTE816> select * from t2;             CNT ----------          0 

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. Now, if we submit a slightly different block, we will get entirely different results:

tkyte@TKYTE816> begin   2          p;   3  exception   4          when others then null;   5  end;   6  / I fired and updated 1 rows I fired and updated 1 rows      PL/SQL procedure successfully completed.      tkyte@TKYTE816> select * from t;               X ----------          1      tkyte@TKYTE816> select * from t2;             CNT ----------          1 

Here, we ran a block of code that ignored any, and all, errors and the difference in outcome here is huge. Whereas the first call to P effected no changes, here 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 did not roll back to the SAVEPOINT after execution. Hence, the partial work performed by P was preserved. The reason that this partial work was 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 entirely succeeds, or fails. This is evidenced by the fact that we can see the trigger on T fired twice and updated T2 twice, yet the count in T2 only reflects one update. The second INSERT executed in P had an implicit SAVEPOINT wrapped around it.

The difference between the two blocks of code is subtle, and something you must consider in your applications. Adding an exception handler to a block of PL/SQL code can radically change its behaviour. A more correct way to code this, one that restores the statement level atomicity to the entire PL/SQL block would be:

tkyte@TKYTE816> begin   2      savepoint sp;   3      p;   4  exception   5      when others then   6          rollback to sp;   7  end;   8  / I fired and updated 1 rows I fired and updated 1 rows      PL/SQL procedure successfully completed.      tkyte@TKYTE816> tkyte@TKYTE816> select * from t;      no rows selected      tkyte@TKYTE816> select * from t2;             CNT ----------          0 

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.

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. Note I said 'SQL statement' and not just 'statement'. If I have many SQL statements in a PL/SQL stored procedure, then each SQL statement will have its integrity constraints validated immediately after their individual execution, not after the stored procedure completes. Integrity constraint checking can be programmatically postponed until the transaction commits, or until you the developer want to validate them.

So, why are constraints validated after the SQL statement executes, why not during? This is because it is very natural for a single statement to make individual rows in a table momentarily 'inconsistent'. Taking a look at the partial work of a statement would result in Oracle rejecting the results, even if the end result would be OK. For example, suppose you have a table like this:

tkyte@TKYTE816> create table t  ( x int unique ); Table created.      tkyte@TKYTE816> insert into t values ( 1 ); 1 row created.      tkyte@TKYTE816> insert into t values ( 2 ); 1 row created. 

And now we want to execute a multiple-row update:

tkyte@TKYTE816> update t set x = x+1; 2 rows updated. 

If Oracle checked the constraint after each row was updated then on any given day you 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, then we would momentarily have a duplicate value for X and it would reject the update. Since Oracle waits patiently to the end of the statement, the statement succeeds because by the time it is done, there are no duplicates.

Starting with Oracle 8.0, we also have the ability to defer constraint checking. This ability 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. There are many that will say you should never need to do this, that primary keys are immutable (I am one of those people), but many people persist in their desire to have a cascading update. Deferrable constraints make this possible.

In prior releases, it was actually possible to do a cascade update, but it involved a tremendous amount of work, and had certain limitations. With deferrable constraints, it becomes almost trivial. It could look like this:

tkyte@TKYTE816> create table p   2  ( pk  int primary key )   3  / Table created.      tkyte@TKYTE816> tkyte@TKYTE816> create table c   2  ( fk  int constraint c_fk   3            references p(pk)   4            deferrable   5            initially immediate   6  )   7  / Table created.      tkyte@TKYTE816> insert into p values ( 1 ); 1 row created.      tkyte@TKYTE816> insert into c values ( 1 ); 1 row created. 

So, I have a parent table P, and a child table C. Table C references table P, and the constraint used to enforce that rule is called C_FK (child foreign key). This constraint was created as DEFERRABLE, but it is set to INITIALLY IMMEDIATE. This means I can defer that constraint until commit or to some other time. By default, however, it will be validated at the statement level. This is the most common use of the deferrable constraints. Most existing applications won't be checking for constraint violations on a COMMIT statement, it is best not to surprise them with that. As defined, our table C behaves in the fashion tables always have, but it gives us the ability to explicitly change its behaviour. Now, lets try some DML on the tables and see what happens:

tkyte@TKYTE816> update p set pk = 2; update p set pk = 2 * ERROR at line 1: ORA-02292: integrity constraint (TKYTE.C_FK) violated - child record found 

Since the constraint is in IMMEDIATE mode, this update fails. We will change the mode and try again:

tkyte@TKYTE816> set constraint c_fk deferred; Constraint set.      tkyte@TKYTE816> update p set pk = 2; 1 row updated. 

Now it succeeds. For illustration purposes, I am going to show how to check a DEFERRED constraint procedurally, to see if the modifications you made are in agreement with the business rules (in other words, that the constraint hasn't been violated). It is a good idea to do this before committing or releasing control to some other part of the program (which may not be expecting the DEFERRED constraints):

tkyte@TKYTE816> set constraint c_fk immediate; set constraint c_fk immediate * ERROR at line 1: ORA-02291: integrity constraint (TKYTE.C_FK) violated - parent key not found 

It fails and returns an error immediately, as expected since we knew that the constraint had been violated. The update to P was not rolled back (that would violate the statement level atomicity). It is still outstanding. You should also note that our transaction is still working with the C_FK constraint DEFERRED since the SET CONSTRAINT command failed. We'll continue on now by cascading the update to C:

tkyte@TKYTE816> update c set fk = 2; 1 row updated.      tkyte@TKYTE816> set constraint c_fk immediate; Constraint set.      tkyte@TKYTE816> commit; Commit complete. 

And that is the way it works.

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 SQLServer 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, they wrap a BEGIN WORK/COMMIT or ROLLBACK around each statement. This is because, in these databases, locks are a precious resource and readers block writers, and writers block readers. In an attempt to increase concurrency, they would like 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 discussion of the JDBC API, at the end of this section). In Oracle, every transaction should be committed when you 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 - 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. Transactions are not for the convenience of the computer and its software, they are to protect your data.

Faced with the task of updating many rows, most programmers will try to figure out some procedural way to do it in a loop, so that they can commit every so many rows. There are two main reasons that I hear for doing it this way:

Both of these conclusions are misguided. It is generally not faster to commit frequently - it is almost always faster to do the work in a single SQL statement. By way of a small example, let's say we have a table T with lots of rows, and we want to update a column value for every row in that table. We could simply do it in a single update like this:

tkyte@TKYTE816> create table t as select * from all_objects; Table created.      tkyte@TKYTE816> set timing on tkyte@TKYTE816> update t set object_name = lower(object_name);      21946 rows updated.      Elapsed: 00:00:01.12 

Many people however, for whatever reason, feel compelled to do it like this:

tkyte@TKYTE816> begin   2     for x in ( select rowid rid, object_name, rownum r   3                  from t )   4     loop   5          update t   6             set object_name = lower(x.object_name)   7           where rowid = x.rid;   8          if ( mod(x.r,100) = 0 ) then   9             commit;  10          end if;  11     end loop;  12     commit;  13  end;  14  / PL/SQL procedure successfully completed.      Elapsed: 00:00:05.99 

In this simple example, it is about five times slower to commit frequently in a loop. If you can do it in a single SQL statement, do it that way, as it is almost certainly faster.

Let's now look at the second reason, which arises from developers using a 'limited resource' (rollback segments) sparingly. This is a configuration issue; you need to ensure that you have enough rollback 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 Locking and Concurrency and Developing Successful Oracle Applications chapters, Oracle's multi-versioning model uses rollback segment data to reconstruct blocks as they appeared at the beginning of your statement or transaction (depending on the isolation mode). If the necessary rollback 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 above procedure), you are generating rollback information required for your query. Your update generates undo information that your query will probably be making use of in order to get the read consistent view of the data it needs to update. If you commit, you are allowing the system to reuse the rollback segment space you just filled up. If it does reuse the rollback, wiping out old rollback data that your query subsequently needs, you are in big trouble. Your SELECT will fail and your update will stop part of the way through. You have a part-finished transaction, and probably no good way to restart it (more about this in a moment). Let's see this in action with a small demonstration. In a small test database I had setup a table:

tkyte@TKYTE816> create table t as select * from all_objects; Table created.      tkyte@TKYTE816> create index t_idx on t(object_name); Index created. 

I then offlined all of my rollback segments and created a small one:

tkyte@TKYTE816> create rollback segment rbs_small storage (initial 64k   2  next 64k minextents 2 maxextents 4 ) tablespace tools;      Rollback segment created. 

Now, with only the small rollback segment online, I ran this block of code to do the update:

tkyte@TKYTE816> begin   2     for x in ( select rowid rid, object_name, rownum r   3                  from t   4                  where object_name > chr(0) )   5     loop   6          update t   7             set object_name = lower(x.object_name)   8           where rowid = x.rid;   9          if ( mod(x.r,100) = 0 ) then  10             commit;  11          end if;  12     end loop;  13     commit;  14  end;  15  / begin * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 10 with name "RBS_SMALL" too small ORA-06512: at line 2 

I get the error. I should point out that I added an index and a WHERE clause. I wanted to make sure that I was reading the table randomly. The WHERE clause will use the index (I used the rule-based optimizer for this). When we process a table via an index, we will tend to read a block for a single row and then the next row we want will be on a different block. We will process all of the rows on block 1, just not concurrently. Block 1 might hold, say, rows A, M, N, Q, and Z. So we would hit the block four times with long time intervals between each hit. Since we are committing frequently and reusing rollback space, we eventually revisit a block we can simply no longer reproduce and get the error.

This was a very artificial example just to show how it happens, in a reliable manner. Our UPDATE statement was generating rollback. We had four 64K extents of rollback to play with, for a total of 256K. We wrapped around in our rollback segment many times, since they are used in a circular fashion. Every time we committed we allowed Oracle to overwrite the rollback data we generated. Eventually, we needed some piece of data that we had generated, but it no longer existed and we received the ORA-01555 error.

You would be right to point out that, in this case, if we had not committed we would get the following error:

begin * ERROR at line 1: ORA-01562: failed to extend rollback segment number 10 ORA-01628: max # extents (4) reached for rollback segment RBS_SMALL ORA-06512: at line 6 

The major differences between the two errors, however, are:

The bottom line here is that you cannot 'save' on rollback space by committing frequently - you need that rollback (I was in a single user system when I received the ORA-01555. It only takes one session to get that). Developers and DBAs need to work together to size these objects adequately for the jobs that need to be done. There can be no short-changing here. You must discover, through analysis of your system, what your biggest transactions are, and size appropriately for them. Given the above example, this might be a one-time update. In that case, I might have created a huge rollback segment somewhere on my system for the sole purpose of doing this update. I would then use the SET TRANSACTION statement to tell my transaction to use this really large rollback segment. I would then drop that rollback segment, and release the space. If this is not a one-time thing but is run frequently, then you need to size that into your system, and have the space readily available. Many people consider things like temp, rollback, 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 - 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, not too small, just right).

Of course, the most serious problem with the 'commit before the transaction is over' approach, is the fact that it frequently leaves your database in an unknown state, if the update fails half way through. Unless you planned for this ahead of time, it is very hard to restart the failed transaction, allowing it to pick up where it left off. For example, say we were not applying the LOWER() function to the column, but rather some other function of the column such as:

last_ddl_time = last_ddl_time + 1; 

If we halted the update loop partway through, how would we restart it? We could not just rerun it, as we would end up adding 2 to some dates, and one to others. If we fail again, we would add 3 to some 2 to others, and 1 to the rest, and so on. We need yet more complex logic - some way to 'partition' the data. For example, we could process all of the object_names that start with A, and then B, and so on:

tkyte@TKYTE816> create table to_do   2  as   3  select distinct substr( object_name, 1,1 ) first_char   4    from T   5  / Table created. tkyte@TKYTE816> begin   2          for x in ( select * from to_do )   3          loop   4              update t set last_ddl_time = last_ddl_time+1   5               where object_name like x.first_char || '%';   6   7              dbms_output.put_line( sql%rowcount || ' rows updated' );   8              delete from to_do where first_char = x.first_char;   9  10              commit;  11          end loop;  12  end;  13  / 11654 rows updated 21759 rows updated 309 rows updated 6 rows updated 270 rows updated 830 rows updated 412 rows updated 7 rows updated 378 rows updated 95 rows updated 203 rows updated 2482 rows updated 13 rows updated 318 rows updated 83 rows updated 14 rows updated 1408 rows updated 86 rows updated 2 rows updated 35 rows updated 2409 rows updated 57 rows updated 306 rows updated 379 rows updated 1 rows updated 1 rows updated      PL/SQL procedure successfully completed. 

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 you have some attribute that evenly partitions the data, you 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 you already processed the A's - you 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 here is the one I advocated in the opening section of Chapter 1: 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. Now they are really stuck, as they have no idea where it stopped processing. It is a lot easier to size rollback correctly than it is to write a restartable transaction. If you have truly large tables that need to be updated, you should be using partitions (more on that in Chapter 14, Partitioning), allowing you to update each partition individually. You can even use parallel DML to perform the update.

My final word on bad transaction habits concerns the one that arises from use of the popular programming APIs ODBC and JDBC. These APIs 'autocommit' by default. Consider the following statements, transferring $1000 from a checking to a savings account:

update accounts set balance = balance - 1000 where account_id = 123; update accounts set balance = balance + 1000 where account_id = 456; 

If your program is using JDBC when you submit these statements, JDBC will (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!

I can understand why ODBC does this. The developers of SQLServer designed ODBC and this database demands that you use very short transactions due to its concurrency model (writes block reads, reads block writes, and locks are a scarce resource). What I cannot understand is how this got carried over into JDBC, an API that is supposed to be in support of the 'Enterprise'. It is my belief that the very next line of code after opening a connection in JDBC should always be:

connection conn81 = DriverManager.getConnection               ("jdbc:oracle:oci8:@ora8idev","scott","tiger");      conn81.setAutoCommit (false); 

This will return 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 for us. I can update data in many different databases in the scope of a single transaction. When I commit, I either 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'.

A key to distributed transactions in Oracle is the database link. A database link is a database object that describes how to log into another instance from your instance. However, the purpose of this section is not to cover the syntax of the database link command (it is fully documented). Once you have the database link set up, accessing remote objects is as easy as:

select * from T@another_database; 

This would select from the table T in the database instance defined by the database link ANOTHER_DATABASE. Typically, you would 'hide' the fact that T is a remote table by creating a view of it, or a synonym. For example, I can:

create synonym T for T@another_database; 

and then access T as if it were a local table. Now that we have this database link set up and can read some tables, we are also able to modify them (given that we have the appropriate privileges, of course). Performing a distributed transaction is now no different from a local transaction. All we would do is:

update local_table set x = 5; update remote_table@another_database set y = 10; commit; 

That is it. Oracle will either commit in both databases or in neither. It uses a two-phase distributed commit (2PC) protocol to do this. The 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 one site will go to the other sites, and ask them to be prepared to commit. Each of the other sites reports back their '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 two-phase commit, 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 in time, 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 C the network fails, Site 1 loses power, whatever C Site 2 and Site 3 will be left 'hanging'. They will have what is known as an in-doubt distributed transaction. The two-phase commit protocol attempts to close the window of error as much as possible, but it cannot close it entirely. Sites 2 and 3 must keep that transaction open, awaiting notification from Site 1 of the outcome. If you recall from the architecture discussion in Chapter 2, it is the function of the RECO process to resolve this issue. This is also where the 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.

There are some limits to what you can do in a distributed transaction. Not many though, and they are reasonable (to me they seem reasonable anyway). The big ones are:

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 above, 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 then 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 (an init.ora parameter) of the site. A commit point strength associates a relative level of importance to a server in a distributed transaction C 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 in the event that 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 would be best if the production machine coordinated the distributed transaction. You do not 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 off, DDL is 'rare'. You do it once at installation, or after an upgrade. Production systems don't do DDL (well, they shouldn't do DDL). Secondly, there is a method to do DDL over a database link, in a fashion, using the job queue facility, DBMS_JOB (covered in the Necessary Supplied Packages appendix). Instead of trying to do DDL over the link, we use the link to schedule a job to be executed as soon as we 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.

Redo and Rollback

I would like to finish up this chapter on transactions with a description of how redo and rollback (undo) are generated, and how they fit into transactions, recovery, and so on. This is a question I am asked frequently. A good conceptual understanding of how redo and undo work, and what takes place, will help you understand the database in general. It is important for developers as well as DBA's to have a good working knowledge of what really happens when an update takes place. You need to understand the ramifications of your actions. What I am presenting below is the pseudo-code for these mechanisms in Oracle. What actually takes place is a little more involved, but having a good understanding of the flow of how it works is valuable.

As an example, we will investigate what might happen with a transaction like this:

insert into t (x,y) values  (1,1); update t set x = x+1 where x = 1; delete from t where x = 2; 

We will follow this transaction down different paths and discover:

The initial INSERT INTO T will generate both redo and undo. The undo generated will be enough information to make the insert 'go away'. The redo generated will be enough information to make the insert 'happen again'. The undo may consist of many pieces of information. There may be indexes on the columns X and Y for example, and their changes must also be undone upon a rollback as well Undo is stored in a rollback segment. A rollback segment is stored in a tablespace, and (this is the important part) is protected by the redo log just like any other segment. In other words, rollback data is treated just like table data or index data - changes to rollback segments generate some redo, which is logged. (why this is so will become clear in a moment when we discuss what happens when the system crashes). Undo data is added to the rollback segment, and is cached in the buffer cache just like any other piece of data would be. Like the undo generated, redo may consist of many pieces of information as well.

So, at this point in time, after the insert has occurred we have:

click to expand

There are some cached, modified rollback blocks, index blocks and table data blocks. Each of these modified blocks is protected by entries in the redo log buffer. All of this information is cached right now.

Hypothetical scenario: the system crashes right now. Everything is OK. The SGA is wiped out but we don't need anything that was in the SGA. It will be as if this transaction never happened when we restart. None of the blocks with changes got flushed, and none of the redo got flushed.

Hypothetical scenario: the buffer cache fills up right now. DBWR must make room and must flush the blocks we just modified. In this case, DBWR will start by asking LGWR to flush the redo blocks that protect the database blocks. Before DBWR can write any of the blocks that are changed to disk, LGWR must flush the redo information related to these blocks. This makes sense, for if we flush the modified blocks for table T, and did not flush the redo for the associated undo blocks, and the system failed, we would have a modified table T block with no undo information associated with it. We need to flush the redo log buffers before writing these blocks out so that we can redo all of these changes necessary to get the SGA back into the state it is in right now, so that a rollback can take place.

This second scenario should show some of the foresight that has gone into all of this. The set of conditions 'if we flushed table T blocks and did not flush the redo for the undo blocks and the system failed' is starting to get complex. It only gets more complex as you add users, and more objects, and concurrent processing, and so on.

So, at this point, we have the situation depicted on the previous page. We have generated some modified table and index blocks. These have created some new rollback segment blocks, and all three types of blocks have generated redo to protect them. If you recall from our earlier discussion on the redo log buffer, it is flushed every three seconds, when a third full, and whenever a commit takes place. It is very likely that at some point during our processing, the redo log buffer will be flushed, and some of our changes will go to disk as well. In that case the picture becomes this:

click to expand

Now, the blocks themselves may end up on disk, but probably not in this case. Next, we do the update. Much the same sort of things take place. This time, the amount of undo will be larger (we now have some 'before' images to save as a result of the update). Now, we have the following picture:

click to expand

We have more new rollback segment blocks in the block buffer cache. In order to undo the update, if necessary, we have modified database table and index blocks in the cache. We have also generated more redo log buffer entries. Some of our generated redo log is on disk, some is in cache.

Hypothetical scenario: the system crashes right now. Upon startup, Oracle would read the redo logs, and find some redo log entries for our transaction. Given the state in which we left the system, with the redo entries for the insert in the redo log files, and the redo for the update still in the buffer, Oracle would 'roll forward' the insert. We would end up with a picture much like the first, with some rollback segment undo blocks (to undo the insert), modified table blocks (right after the insert), and modified index blocks (right after the insert). Now Oracle will discover that our transaction never committed and will roll it back since the system is doing crash recovery and, of course, our session is no longer connected. It will take the undo it just rolled forward in the buffer cache, and apply it to the data and index blocks, making them look as they did before the insert took place. Now everything is back the way it was. The blocks that are on disk may, or may not, reflect the INSERT (it depends on whether or not our blocks got flushed before the crash). If they do, then the insert has been, in effect, undone and when the blocks are flushed from the buffer cache, the data file will reflect that. If they do not reflect the insert - so be it, they will be overwritten later anyway.

Hypothetical scenario: the application rolls back the transaction. At this point, Oracle will find the undo information for this transaction, either in the cached rollback segment blocks (most likely), or on disk if they have been flushed (more likely for very large transactions). It will apply the undo information to the data and index blocks in the buffer cache, or if they are no longer in the cache request, they are read from disk into the cache to have the UNDO applied to them. These blocks will later be flushed to the data files in the original form.

The first scenario covers the rudimentary details of a crash recovery. The system performs this as a two-step process. First it rolls forward, bringing the system right to the point of failure, and then it proceeds to rollback everything that had not yet committed. This action will resynchronize the data files. It replays the work that was in progress, and undoes anything that had not yet completed.

The second scenario is one that is played out much more often. It is useful to note that during the rollback process, the redo logs are never involved. The only time redo logs are read is during recovery and archival. This is a key tuning concept - redo logs are written to. Oracle does not read them during normal processing. As long as you have sufficient devices so that when ARCH is reading a file, LGWR is writing to a different device, then there is no contention for redo logs. Many other databases treat the log files as 'transaction logs'. They do not have this separation of redo and undo - they keep both in the same file. For those systems, the act of rolling back can be disastrous - the rollback process must read the logs their log writer is trying to write to. They introduce contention into the part of the system that can least stand it. Oracle's goal is to make it so that logs are written sequentially, and no one ever reads them while they are being written - ever.

Now, onto the DELETE statement. Again, undo is generated, blocks are modified, and redo is sent over to the redo log buffer. This is not very different from before. In fact, it is so similar to the UPDATE that we are going to go right onto the COMMIT. We've looked at various failure scenarios and different paths, and now we finally made it to the COMMIT. Here, Oracle will flush the redo log buffer to disk and the picture will look like this:

click to expand

The modified blocks are in the buffer cache; maybe some of them have been flushed to disk. All of the redo necessary to replay this transaction is safely on disk and the changes are now permanent. If we were to read the data directly from the data files, we probably would see the blocks as they existed before the transaction took place, as DBWR most likely has not yet written them. That is OK - the redo log files can be used to restore those blocks in the event of a failure. The undo information will hang around until the rollback segment wraps around and reuses those blocks. Oracle will use that undo to provide for consistent reads of the affected objects for any session that needs it.

Summary

In this section we looked at many aspects of transaction management in Oracle. Transactions are one of the major features that set a database apart from a file system. Understanding how they work how to use them is necessary and in order 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 multi-table updates when the tables being modified are all dependent on each other - the cascading update was 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 sort 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 rollback 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 are there. 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.

We closed with a look at redo and undo, the architectural features that serve to enforce the ACID transaction properties discussed at the start of this chapter. We looked at what happens when we modify data in a transaction with regards to redo and undo (rollback data). This last topic will be considered in much more detail in the next chapter.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net