Re: Row locking and serializability

In article <7ivdpi$qko$1_at_nnrp1.deja.com>,   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.
>

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 Oracle do not block writes as they do in other systems. Transactions that check for database consistency at the application level may require coding techniques such as the use of SELECT FOR UPDATE. This issue should be considered when applications using serializable mode are ported to Oracle from other environments."

• end excerpts ---
• begin excerpt from SQL92 standard ---

The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL- transaction begins.

The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL-transactions. The following phenomena are possible:

1. P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL- transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.
2. P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
3. P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL- statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

Level P1 P2 P3

READ UNCOMMITTED  Possible      Possible      Possible
READ COMMITTED    Not Possible  Possible      Possible
REPEATABLE READ   Not Possible  Not Possible  Possible
SERIALIABLE       Not Possible  Not Possible  Not Possible

Note: The exclusion of these phenomena for SQL-transactions executing at isolation level SERIALIZABLE is a consequence of the requirement that such transactions be serializable.

• end excerpt ---

The full text of the standard is available at "http://sunsite.doc.ic.ac.uk/pub/computing/programming/languages/perl/db /refinfo/sql2/sql1992.txt".

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 of the Oracle product line.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon May 31 1999 - 23:20:23 CDT