... May be it looks complicated,...
for a theorized 1-2% problem, yes, it sure does. I didn't really understand it either. I don't know what "pass" means.
... And even, may be, it can be nice alternative for serializable transaction..?
...
why? since serializable requires no special coding on part of the developer and this requires the developer actually understands the database better than 99.9999% of developers do today?
.... So we can prevent new readers appearance ...
which would get you back to being almost just like select for update - we queue the reads again.
As for ora-8177, here is what I wrote in Expert Oracle Database Architecture, ora-8177 is sort of like an ORA-4091 Mutating table constraint. It is an an implementation restriction - but is an "error" none the less. It is also better than the alternative (deadlock) that occurs in other systems (at a much higher rate, since READS would deadlock with WRITES in the other systems - here only the write of the same data conflicts)
<quote>
SERIALIZABLEThis 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
Select * from T;
Begin dbms_lock.sleep( 60*60*24 ); end;
Select * from T;
the answers returned from T would be the same, even though we just slept for 24 hours (or we might get an ORA-1555, snapshot too old error, which is discussed in Chapter 8). The isolation level 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 as SYSDBA) cannot 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 rollback 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.
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 transaction, you shouldn’t expect to update the same information as other transactions. If you do, you should use the SELECT ... FOR UPDATE as described previously 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
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. Here 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, such as the resource scheduler described in Chapter 1, must take extra care in this regard. If you recall, the problem in Chapter 1 was that we could not enforce our integrity constraint in a multiuser system since we could not see changes made by other uncommitted sessions. Using SERIALIZABLE, we would still not see the uncommitted changes, but we would also not see the committed changes made after our transaction began!
</quote>