Re: Row locking and serializability

This looks correct. ORACLE doesn't really support serializable transactions as they should be. Will you thus declare ORACLE unusable? Finally we'll be rid of them. I think you will find very few people who use serializable transaction mode. Think about locking so much stuff. You might as well have table locks. So, my experience has been that most people live without serializable transactions because they can't afford them. iggy_fernandez@my-deja.com wrote: > > In article <7ivfv9$r8k$1@nnrp1.deja.com>, > iggy_fernandez@my-deja.com wrote: > > It is intuitively obvious that serial (sequential) execution of > > transactions is, in some sense, a "safe mode" of operation that > > guarantees data consistency. It is not at all intuitively obvious that > > concurrent execution of transactions is an equally safe mode of > > operation. The SQL92 standard provides for the "SET TRANSACTION > > ISOLATION LEVEL SERIALIZABLE" command which gives the user the ability > > to specify that the concurrent execution of transactions should > produce > > the same results as "some" serially executed sequence of those same > > transactions. > > > > The following excerpt is from Chapter 3 of the Oracle8 Application > > Developer's Guide. I have the following questions > > > > (1) The author mentions a "greater degree of transaction isolation > than > > defined by SQL92 SERIALIZABLE mode". Has Oracle correctly interpreted > > the SQL92 definition of serializibility? > > > > (2) "Readers" do not block "writers" in an Oracle database. Is it > > possible to guarantee serializability without requiring readers to > > block writers? > > > > (3) Oracle only places locks on rows that satisfy query criteria and > > does not place read locks on pages that were scanned in the process of > > executing a query. Is it possible to guarantee serializability without > > placing read locks on all such pages? > > > > (4) I was able to perform the referential integrity experiments > against > > an Ingres 6.4/06 database without experiencing the anomalies described > > in the Oracle documentation. Is Oracle alone in its interpretation of > > the SQL92 defininition of serializability? > > > > --- begin excerpt --- > > > > Because Oracle does not use read locks, even in SERIALIZABLE > > transactions, data read by one transaction can be overwritten by > > another. Transactions that perform database consistency checks at the > > application level should not assume that the data they read will not > > change during the execution of the transaction (even though such > > changes are not visible to the transaction). Database inconsistencies > > can result unless such application-level consistency checks are coded > > carefully, even when using SERIALIZABLE transactions. > > > > Consider 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. > > > > Time T1: Transaction A: Read parent (it exists) > > Time T2: Transaction B: Read child rows (not found) > > Time T3: Transaction A: Insert child row(s) > > Time T4: Transaction B: Delete parent > > Time T5: Transaction A: Commit work > > Time T6: Transaction B: Commit work > > > > 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. > > > > --- end excerpt --- > > > > Disclaimers: (1) My employer may have opinions very different from > > mine. (2) My opinions may prove to be significantly incorrect. (3) > > Oracle itself is the final authority on the capabilities on the Oracle > > product line. > > > > Sent via Deja.com http://www.deja.com/ > > Share what you know. Learn what you don't. > > > > I was able to perform the referential integrity experiments against an > Ingres 6.4/06 database without experiencing the anomalies described in > the Oracle documentation. Here are the results. > > At Time T1, Transaction A placed a read lock on the page containing the > parent record. > At Time T2, Transaction B placed read locks on all pages scanned in the > process of determining whether child rows existed. > At Time T3, Transaction A was prevented from inserting child rows > because of the read locks acquired by Transaction B at Time T2, and was > forced to wait. > At Time T4, Transaction B was prevented from deleting the parent record > because of the read lock acquired by Transaction A at Time T1 and was > forced to wait. This resulted in a deadlock between Transaction A and > Transaction B. Ingres detected the deadlock by rolling back one of the > transactions. Only one transaction succeeded. > > Oracle's position apparently is that the SQL92 standard defines > transaction isolation levels IN TERMS OF three phenomena that must be > prevented between concurrently executing transactions. The standard > states that the exclusion of these phenomena for transactions executing > at isolation level serializable is A CONSEQUENCE OF the requirement > that such transactions be serializable. > > --- begin excerpts from "Oracle8 Server Concepts" --- > > "The ANSI/ISO SQL standard (SQL92) defines four levels of transaction > isolation with differing degrees of impact on transaction processing > throughput. These isolation levels are defined in terms of three > phenomena that must be prevented between concurrently executing > transactions." > > "Serializable transactions see only those changes that were committed > at the time the transaction began, plus those changes made by the > transaction itself through INSERT, UPDATE, and DELETE statements. > Serializable transactions do not experience nonrepeatable reads or > phantoms." > > "Although Oracle serializable mode is compatible with SQL92 and offers > many benefits compared with read-locking implementations, it does not > provide semantics identical to such systems. Application designers must > take into account the fact that reads in Or