This is generally considered the most restrictive level of transaction isolation, but it provides the highest degree of isolation. A SERIALIZABLE transaction operates in an environment that makes it appear as if there are no other users modifying data in the database. Any row we read is assured to be the same upon a reread, and any query we execute is guaranteed to return the same results for the life of a transaction. For example, if we execute the following, the answers returned from T would be the same, even though we just slept for 24 hours (or we might get an ORA-01555, snapshot too old error, which is discussed in the next chapter):
SQL> select * from T;
SQL> begin dbms_lock.sleep( 606024 ); end;
SQL> select * from T;
The isolation level SERIALIZABLE assures us these two queries will always return the same results. Side effects (changes) made by other transactions are not visible to the query regardless of how long it has been running.
In Oracle, a SERIALIZABLE transaction is implemented so that the read consistency we normally get at the statement level is extended to the transaction.
Note As noted earlier, there is also an isolation level in Oracle denoted READ ONLY. It has all of the qualities of the SERIALIZABLE isolation level, but it prohibits modifications. It should be noted that the SYS user (or users connected with the SYSDBA privilege) can’t have a READ ONLY or SERIALIZABLE transaction. SYS is special in this regard.
Instead of results being consistent with respect to the start of a statement, they are preordained at the time you begin the transaction. In other words, Oracle uses the undo segments to reconstruct the data as it existed when our transaction began, instead of just when our statement began.
That’s a pretty deep thought there: the database already knows the answer to any question you might ask it, before you ask it.
This degree of isolation comes with a price, and that price is the following possible error:
ERROR at line 1:
ORA-08177: can’t serialize access for this transaction
You will get this message whenever you attempt to update a row that has changed since your transaction began.
Note Oracle attempts to do this purely at the row level, but you may receive an ORA-08177 error even when the row you are interested in modifying has not been modified. The ORA-08177 error may happen due to some other row(s) being modified on the block that contains your row.
Oracle takes an optimistic approach to serialization—it gambles on the fact that the data your transaction wants to update won’t be updated by any other transaction. This is typically the way it happens, and usually the gamble pays off, especially in quick-transaction, OLTP-type systems. If no one else updates your data during your transaction, this isolation level, which will generally decrease concurrency in other systems, will provide the same degree of concurrency as it would without SERIALIZABLE transactions. The downside to this is that you may get the ORA-08177 error if the gamble doesn’t pay off. If you think about it, however, it’s worth the risk. If you’re using SERIALIZABLE transactions, you shouldn’t expect to update the same information as other transactions. If you do, you should use the SELECT … FOR UPDATE as described in Chapter 1, and this will serialize the access. So, using an isolation level of SERIALIZABLE will be achievable and effective if you
•\ Have a high probability of no one else modifying the same data
•\ Need transaction-level read consistency
•\ Will be doing short transactions (to help make the first bullet point a reality)
Oracle finds this method scalable enough to run all of their TPC-Cs (an industry standard OLTP benchmark; see www.tpc.org for details). In many other implementations, you will find this being achieved with shared read locks and their corresponding deadlocks and blocking. In Oracle, we do not get any blocking, but we will get the ORA-08177 error if other sessions change the data we want to change as well. However, we will not get the error as frequently as we will get deadlocks and blocks in the other systems.
But—there is always a “but”—you must take care to understand these different isolation levels and their implications. Remember, with isolation set to SERIALIZABLE, you will not see any changes made in the database after the start of your transaction, until you commit. Applications that attempt to enforce their own data integrity constraints must take extra care in this regard. Using SERIALIZABLE, you will not see the uncommitted changes; furthermore, you will not see the committed changes made after the transaction began.
As a final point, be aware that SERIALIZABLE does not mean that all transactions executed by users will behave as if they were executed one right after another in a serial fashion. It does not imply that there is some serial ordering of the transactions that will result in the same outcome. The phenomena previously described by the SQL standard do not make this happen. This last point is a frequently misunderstood concept, and a small demonstration will clear it up. The following table represents two sessions performing work over time. The database tables A and B start out empty and are created as follows:
SQL> create table a ( x int );
SQL> create table b ( x int );
Now we have the series of events shown in Table 7-7.
Table 7-7. SERIALIZABLE Transaction Example
Now, when this is all said and done, tables A and B will each have a row with the value 0 in it. If there were some serial ordering of the transactions, we could not possibly have both tables containing the value 0 in them. If session 1 executed in its entirety before session 2, then table B would have a row with the value 1 in it. If session 2 executed in its entirety before session 1, then table A would have a row with the value 1 in it. As executed here, however, both tables will have rows with a value of 0. They just executed as if they were the only transaction in the database at that point in time. No matter how many times session 1 queries table B and no matter the committed state of session 2, the count will be the count that was committed in the database at time T1. Likewise, no matter how many times session 2 queries table A, the count will be the same as it was at time T2.
