Re: Row locking and serializability

Related paper on ANSI SQL transaction isolation: http://www.acm.org/pubs/citations/proceedings/mod/223784/p1-berenson/ It mentiones "Oracle Read Consistency" as well.

iggy_fernandez_at_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.
Received on Thu Jun 03 1999 - 12:05:19 CDT