If a transaction (A) attempts to update or delete a row that has been locked by another transaction B (by way of a DML or SELECT FOR UPDATE statement), then A's DML command blocks until B commits or rolls back. Once B commits, transaction A can see changes that B has made to the database.
For most applications, this concurrency model is the appropriate one. In some cases, however, it is advantageous to allow transactions to be serializable. Serializable transactions must execute in such a way that they appear to be executing one at a time (serially), rather than concurrently. In other words, concurrent transactions executing in serialized mode are only permitted to make database changes that they could have made if the transactions were scheduled to run one after the other.
The ANSI/ISO SQL standard SQL92 defines three possible kinds of transaction interaction, and four levels of isolation that provide increasing protection against these interactions. These interactions and isolation levels are summarized in Table 3 - 12.
| READ UNCOMMITTED | Oracle never permits ``dirty reads.'' This is not required for high throughput with Oracle. |
| READ COMMITTED | Oracle meets the READ COMMITTED isolation standard. This is the default mode for all Oracle applications. Note that since an Oracle query only sees data that was committed at the beginning of the query (the snapshot time), Oracle offers more consistency than actually required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation. |
| REPEATABLE READ | Oracle does not support this isolation level, except as provided by SERIALIZABLE. |
| SERIALIZABLE | You can set this isolation level using the SET TRANSACTION command or the ALTER SESSION command, as described ![[*]](jump.gif) . |
Figure 3 - 2. Time Line for Two Transactions
When a serializable transaction fails with an ORA-08177 error (``cannot serialize access''), the application can take any of several actions:
ORA-01453: SET TRANSACTION must be first statement of transaction
Use the ALTER SESSION command to set the transaction isolation level on a session-wide basis. See the Oracle7 Server SQL Reference for the complete syntax of the SET TRANSACTION and ALTER SESSION commands.
Figure 3 - 3 two different transactions that perform application-level checks to maintain the referential integrity parent/child relationship between two tables. One transaction reads the parent table to determine that a row with a specific primary key value exists before inserting corresponding child rows. The other transaction checks to see that no corresponding detail rows exist before proceeding to delete a parent row. In this case, both transactions assume (but do not ensure) that data they read will not change before the transaction completes.
Figure 3 - 3. Referential Integrity Checks
Note that the read issued by transaction A does not prevent transaction B from deleting the parent row. Likewise, transaction B's query for child rows does not prevent the insertion of child rows by transaction A. Therefore the above scenario leaves in the database a child row with no corresponding parent row. This result would occur even if both A and B are SERIALIZABLE transactions, because neither transaction prevents the other from making changes in the data it reads to check consistency.
As this example illustrates, for some transactions, application developers must specifically ensure that the data read by one transaction is not concurrently written by another. This requires a greater degree of transaction isolation than defined by SQL92 SERIALIZABLE mode.
Referential integrity can also be enforced in Oracle7 using database triggers, instead of a separate query as in transaction A above. For example, an INSERT into the child table can fire a PRE-INSERT row-level trigger to check for the corresponding parent row. The trigger queries the parent table using SELECT FOR UPDATE, ensuring that parent row (if it exists) will remain in the database for the duration of the transaction inserting the child row. If the corresponding parent row does not exist, the trigger rejects the insert of the child row.
SQL statements issued by a database trigger execute in the context of the SQL statement that caused the trigger to fire. All SQL statements executed within a trigger see the database in the same state as the triggering statement. Thus, in a READ COMMITTED transaction, the SQL statements in a trigger see the database as of the beginning of the triggering statement's execution, and in a transaction executing in SERIALIZABLE mode, the SQL statements see the database as of the beginning of the transaction. In either case, the use of SELECT FOR UPDATE by the trigger will correctly enforce referential integrity as explained above.
Oracle7 provides transactions executing in READ COMMITTED mode with transaction set consistency on a per-statement basis (since all rows read by a query must have been committed before the query began). Similarly, Oracle7 SERIALIZABLE mode provides transaction set consistency on a per-transaction basis, since all statements in a SERIALIZABLE transaction execute with respect to an image of the database as of the beginning of the transaction.
In other database systems (unlike in Oracle7), a single query run in READ COMMITTED mode provides results that are not transaction set consistent. The query is not transaction set consistent because it may see only a subset of the changes made by another transaction. This means, for example, that a join of a master table with a detail table could see a master record inserted by another transaction, but not the corresponding details inserted by that transaction, or vice versa. Oracle7's READ COMMITTED mode will not experience this effect, and so provides a greater degree of consistency than read-locking systems.
In read-locking systems, at the cost of preventing concurrent updates, SQL92 REPEATABLE READ isolation provides transaction set consistency at the statement level, but not at the transaction level. The absence of phantom protection means two queries issued by the same transaction can see data committed by different sets of other transactions. Only the throughput-limiting and deadlock-susceptible SERIALIZABLE mode in these systems provides transaction set consistency at the transaction level.
For environments with many concurrent users rapidly submitting transactions, designers must assess transaction performance requirements in terms of the expected transaction arrival rate and response time demands, and choose an isolation level that provides the required degree of consistency while satisfying performance expectations. Frequently, for high performance environments, the choice of isolation levels involves making a tradeoff between consistency and concurrency (transaction throughput).
Both Oracle7 isolation modes provide high levels of consistency and concurrency (and performance) through the combination of row-level locking and Oracle's multi-version concurrency control system. Because readers and writers don't block one another in Oracle7, while queries still see consistent data, both READ COMMITTED and SERIALIZABLE isolation provide a high level of concurrency for high performance, without the need for reading uncommitted ("dirty") data.
READ COMMITTED isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results (due to phantoms and non-repeatable reads) for some transactions. The SERIALIZABLE isolation level provides somewhat more consistency by protecting against phantoms and non-repeatable reads, and may be important where a read/write transaction executes a query more than once. However, SERIALIZABLE mode requires applications to check for the "can't serialize access" error, and can significantly reduce throughput in an environment with many concurrent transactions accessing the same data for update. Application logic that checks database consistency must take into account the fact reads don't block writes in either mode.
ORA-08177: Can't serialize access for this transaction.
When you get an ORA-08177 error, the appropriate action is to roll back the current transaction, and re-execute it. After a rollback, the transaction acquires a new transaction snapshot, and the DML operation is likely to succeed.
Since a rollback and repeat of the transaction is required, it is good development practice to put DML statements that might conflict with other concurrent transactions towards the beginning of your transaction, whenever possible.