Re: serializable isolation level behavior question

Bob Jones wrote :

> The DBMS software can only go so far by providing the necessary tools.

Yes, I can accept this argument, Bob. Especially since we are talking detection of mistakes.

> Actually we were discussing about standard compliant. So far it has not been
> proven it is not. Should Oracle generate a error?

Oh ? While I think it is more of a QOI issue, my very uninformed opinion,
for what it is worth, is :

• The Sql 92/99 standard forbids non repeatable/phantom reads for serializable transactions, even when DDL is performed by other transactions
• The standard is of little formal guidance here, given the level of non-conformity involved.
• But Oracle claims that serializable transactions will not experience non repeatable/phantom reads either, never.

The reading is as follows :

A : Is the OP actually in a transaction ?

A.1 In Sql-99, according to my reading :

1. SET TRANSACTION is not initiating a transaction. Its use is actually prohibited inside a transaction.
2. To actually start the transaction, the OP would need to issue a START TRANSACTION (or a single row select)
3. Oracle does not have and does not claim to have a START TRANSACTION statement.

Therefore, the OP cannot achieve the desired effect with Oracle in a Sql-99 conformant way.

A.2 In Oracle, according to my reading of the SQL reference manual :

SET TRANSACTION must be the first statement of a transaction, and actually starts it.

Conclusion : The OP is in transaction, albeit in a non-conformant way.

B. Is a serializable transaction protected against truncate ?

B.1 In Sql-99 :

1. A transaction isolation level is guaranteed against other transactions which execute sql-data or sql-schema operations. Note that sql-schema operations include ALTER or DROP TABLE, which do initiate transactions.

The overall intent is certainly to protect against DDL.

b) but TRUNCATE is not an SQL-99 statement.

B.2 In Oracle :

Oracle claims that "The SERIALIAZABLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard."

Conclusion : The OP cannot expect anything from the letter of the standard, since TRUNCATE is not standard. However, the intent of the standard's isolation modes - which oracle claims to follow - is certainly to protect against DDL.

(Incidentaly, Oracle's claim is specifically for Sql-92 while it usually refers to Sql-99 otherwhere. I couldnt find a relevant difference between 92 and 99, so I assume it is some leftover)

C. What is the level of protection. Should an error be raised ?

C.1 Sql-92 & 99 tell us :

"all read operations are repeatable within an SQL-transaction at isolation
evel SERIALIZABLE, except for (1) the effects of changes to SQL-data or schemas
and its contents made explicitly by the SQL-transaction itself (2) the effects
of differences in parameter values supplied to procedures, and (3) the effects
of references to time-varying system variables such as CURRENT_DATE and

CURRENT_USER. " Note that there is no exception for changes performed by SQL-Schemas/DDL
in other transactions. Reads must be repeatable.

C.2 Oracle tell us

"The SERIALIAZBLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the

start of the serializable transaction, then the DML statement fails." (SET TRANSACTION reference manual)

"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." (concept manual)

Thus, Oracle claims conformance with Sql-92, which guarantees repeatable
read even when DDL is performed by other transactions. Oracle also claims
that serializable transactions do not experience nonrepeatable reads. Never.

On the other hand, oracle claims that an error is raised if a serializable
transaction attempts to update an otherwise modified resource. But nowhere is
it claimed that this is the complete set of failures.

One might object that a DDL statement is not part of a transaction and thus does
not commit. However, Oracle claims : "A transaction in Oracle begins when the first
executable SQL statement is encountered. An executable SQL statement is a SQL
statement that generates calls to an instance, **including DML and DDL statements.**"
(Overview of Transaction Management)

Overall, I think that the Sql standard forbids non repeatable and phantom
reads in serializable transaction, even when DDL has been performed by other
sessions, and that Oracle claims the same...

References :

http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt Oracle9i SQL Reference Release 2 (9.2) (SET TRANSACTION) Oracle9i Database Concepts Release 2 (9.2) (20. Data Concurrency and Consistency)
Oracle9i SQL Reference Release 2 (9.2) (B. Oracle and Standard SQL) Received on Mon Oct 23 2006 - 15:31:10 CDT