Re: Row locking and serializability

Maybe you should try posting an oracle question in the oracle group. The extra cross-posting is useless. 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.