Database Reference
In-Depth Information
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 2, 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 http://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:
EODA@ORA12CR1> create table a ( x int );
Table created.
EODA@ORA12CR1> create table b ( x int );
Table created.
Now we have the series of events shown in Table 4-7 .
Table 4-7. SERIALIZABLE Transaction Example
Time
Session 1 Executes
Session 2 Executes
Alter session set
isolation_level=serializable;
T1
--
Alter session set isolation_level=serializable;
T2
--
Insert into a select count(*) from b;
T3
--
Insert into b select count(*) from a;
T4
--
Commit;
T5
--
Commit;
T6
--
 
Search WWH ::




Custom Search