CORAL "read-only" modes for sessions and transactions

This page is meant to explain the current and foreseen future semantics of the different types of CORAL sessions and transactions available to users. For both sessions and transactions, we review first the various options available in Oracle (the main deployment technology), then their mapping in CORAL and extra functionalities offered by CORAL. See also the discussion about this topic in task #25923.

Session types in Oracle and CORAL

Session types in Oracle

In our understanding, Oracle does not support the concept of a 'read-only' session. For instance, there is no way to pass a readonly flag to OCISessionBegin. Not even sqlplus supports the concept of a readonly session; some external tools like Benthic do support read-only sessions, but this is something implemented by Benthic, not an Oracle intrinsic feature.

What Oracle does provide is a variety of other handles, including

• read-only databases
• read-only tables (new in 11g)
• accounts with limited privileges, that we can call 'read-only accounts'
• read only transactions (as discussed below in another section)

In the CERN environment, the third option is very widely used. Creating 'reader' accounts with limited privileges is a well established policy. Users are advised to connect using their read only accounts (and actually most users ONLY have access to these accounts).

Session types in CORAL

CORAL presently supports two types of sessions, Update and ReadOnly.

In the C++ code this is achieved via the AccessMode enum. This is a parameter that should be passed to the connect methods of the IConnectionService to get an ISessionProxy.

Within CORAL, this AccessMode for sessions serves essentially two purposes:

1. Before connecting, the access mode is used by the CORAL dblookup plugin to provide a valid replica for a given DB alias (e.g. Frontier can be selected if AccessMode is readonly, but otherwise Oracle must be chosen for update access). [Note that the selection of one or another account for authenticating, via the authentication plugin, is instead determined by the optional 'role' specified by the user - the session AccessMode has no relevance here].
2. Once connected, if in ReadOnly mode CORAL performs some checks to impose extra constraints, i.e. forbid DDL and DML and only allow SELECTs. For instance, an INSERT will throw a CORAL InvalidOperationInReadOnlyModeException.

In summary, the concept of CORAL ReadOnly session implements an additional functionality that Oracle does not offer. The second point is especially important: it is CORAL (like Benthic, mentioned above) that takes care of checking and deciding which operations should be allowed within a ReadOnly session.

The ReadOnly session concept and its functionality were implemented in CORAL from the very first release CORAL_1_0_0. The InvalidOperationInReadOnlyModeException class was also added long ago in CORAL_1_3_0.

Transaction types in Oracle and CORAL

Transaction isolation levels in the ANSI standard

A good overview of transaction types (or, to be more precise, of transaction isolation levels) in the ANSI standard and how these are implemented in Oracle can be found in the Oracle Concepts manual.

The ANSI standard defines four levels of transaction isolation. These isolation levels are defined in terms of phenomena that must be prevented between concurrently executing transactions. The preventable phenomena are:

• Dirty reads. A transaction reads data that has been written by another transaction that has not been committed yet.
• Nonrepeatable (fuzzy) reads. A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data. For example, a user queries a row and then later queries the same row, only to discover that the data has changed.
• Phantom reads. A transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition. For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire. More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.

The SQL standard defines four levels of isolation in terms of the phenomena that a transaction running at a particular isolation level is permitted to experience: Read uncommitted, Read committed, Repeatable read, Serializable. The table below shows these levels.

	Dirty read	Nonrepeatable read	Phantom read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

See also an interesting description of these isolation levels and locks on wikipedia.

Transaction isolation levels in Oracle SQL

According to the Oracle Concepts manual, Oracle offers the read committed (default) and serializable ANSI isolation levels, as well as an additional (non-ANSI and Oracle-specific) read-only mode. In summary, Oracle provides three transaction isolation levels:

1. Read Committed isolation level (default). Every query executed by a transaction sees only data committed before the query - not the transaction - began.
2. Serializable isolation level. A transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself. A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database. [Note that this isolation level may cause updates to fail with a ORA-08177 error, when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began.]
3. Read-Only isolation level. The read-only isolation level is similar to the serializable isolation level, but read-only transactions do not permit data to be modified in the transaction (unless the user is SYS). In our understanding, Read-Only transactions are serializable transactions which in addition do not allow data modifications.

Another interesting concept in this context is Read consistency (see again the Oracle Concepts manual). Oracle provides two levels of Read consistency:

• Statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time, is ensured by all three isolation levels above. This is because, as mentioned above, Oracle never permits dirty reads, which occur when a transaction reads uncommitted data in another transaction. [Note that Flasback queries, described in the Oracle Advanced Application Developers manual, allow to specify this point in time (in the past) explicitly.]
• Transaction-level read consistency, i.e. consistency to all queries in a transaction, is only provided by the Serializable and Read-Only isolation levels. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began; queries made by a serializable transaction see changes made by the transaction itself.

It is interesting to consider the three isolation levels above in terms of two properties:

• Read consistency. All three isolation levels above provide at least statement-level read consistency. The Serializable and Read-Only isolation levels also provide transaction-level read consistency.
• The possibility to modify data during the transaction. The Read-Committed and Serializable isolation levels allow data modification, while the Read-Only isolation level forbids it.

In summary, Oracle provides the following three out of the four possible combinations:

	Statement-level read consistency ("non serializable")	Transaction-level read-consistency ("serializable")
Updates allowed ("read-write")	Read Committed isolation level	Serializable isolation level
Updates forbidden ("read-only")	-	Read-Only isolation level

In other words, there is no concept in Oracle defining a Read Committed transaction where updates are forbidden (what Andrea often described as "non serializable read-only transaction" in several savannah posts on COOL and CORAL). In our understanding, this in line with what is mentioned above for "read-only" sessions in Oracle, which are not implemented because Oracle provides other mechanisms for implementing "read-only" access, such as read-only databases, tables, accounts and (serializable, only!) transactions. [This may be different on other platforms such as IBM DB2.]

Transaction control in Oracle

The following observations are relevant to describe how users can control when transactions begin and end as well as their isolation levels via Oracle SQL.

• According to the Oracle SQL Language Reference manual, the syntax of the SET TRANSACTION statement (through which the above isolation levels can be implemented) is the following:
  
  In other words, neglecting the option involving rollback segments, there are four possible ways to issue a SET TRANSACTION statement, to implement the above three isolation levels. In our understanding, this is because two SET TRANSACTION statements (READ WRITE and ISOLATION LEVEL READ COMMITTED) are equivalent and provide the default Read Committed isolation level.
• In addition, note that a transaction does not need to be started explicitly: as mentioned in the Oracle Concepts manual, a transaction begins when the first executable SQL statement is encountered (where an executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement). In our understanding, if no explicit SET TRANSACTION is issued, the default Read Committed isolation level is again used.
• Finally, as also mentioned in the Oracle Concepts manual, note that DDL statements commit any existing transaction: the database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

To summarize, this is in our understanding how five common scenarios map to the three isolation levels:

SQL statement	Isolation level
No explicit SET TRANSACTION (transaction started by DML or DDL)	Read committed (default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED	Read committed (default)
SET TRANSACTION READ WRITE	Read committed (default)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE	Serializable
SET TRANSACTION READ ONLY	Read only

If we understand correctly, note that there is a small difference (the only one?) between explicitly starting a READ WRITE or ISOLATION LEVEL READ COMMITTED transaction before an INSERT and instead issuing the INSERT directly. By using an explicit SET TRANSACTION statement, you ensure that there cannot be another, possibly different, SET TRANSACTION statement issued before the INSERT.

In terms of the two properties mentioned above, read consistency and the possibility to modify data, in our understanding this is where those five scenarios fit:

	Statement-level read consistency ("non serializable")	Transaction-level read-consistency ("serializable")
Updates allowed ("read-write")	No explicit SET TRANSACTION SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET TRANSACTION READ WRITE (Read Committed isolation level)	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (Serializable isolation level)
Updates forbidden ("read-only")	-	SET TRANSACTION READ ONLY (Read-Only isolation level)

[Note: Metalink note 252545.1 "How To Query The Isolation Level Status For A Session/Transaction" could be useful if you want to test the effects of the various transaction control scenarios in Oracle. The following query (to be executed only if there is an uncommitted DML) is especially interesting: select decode(bitand(flag,268435456),268435456,'serializable','non-serializable') from v$transaction,v$session where taddr=addr and sid=(select sid from v$mystat where rownum <2);.]

Transaction control in Oracle OCI

Since the CORAL OracleAccess plugin is implemented using OCI, it is also important to describe how transaction control in Oracle is achieved via OCI. This is described in the Oracle OCI manual.

As in the case of SQL, also in OCI transactions can be started explicitly or not, via an OCITransStart call:

• Many applications work with only simple local transactions: OCITransStart is not called explicitly and an implicit transaction is created when the application makes database changes. The only transaction-specific calls needed by such applications are OCITransCommit to commit or OCITransRollback to roll back the transaction. As soon as one transaction has been committed or rolled back, the next modification to the database creates a new implicit transaction for the application. Only one implicit transaction can be active at any time on a service context. Attributes of the implicit transaction are opaque to the user.
• Applications requiring serializable or read-only transactions require an additional OCITransStart call to start the transaction. The OCITransStart call must specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read/write transaction. Specifying the read-only option in the OCITransStart call saves the application from performing a server round-trip to execute a SET TRANSACTION READ ONLY statement.
• It also seems possible to control transactions in OCI by simply issuing the corresponding SQL statement. As mentioned in the Oracle OCI manual, OCI applications treat transaction control, session control, and system control statements as if they were DML statements.

To summarize, this is in our understanding how the three isolation levels may be achieved via Oracle OCI:

SQL statement	Isolation level
No explicit OCITransStart call	Read committed (default)
OCITransStart with no flag	Read committed (default)
OCITransStart with OCI_TRANS_READWRITE flag	Read committed (default)
OCITransStart with OCI_TRANS_SERIALIZABLE flag	Serializable
OCITransStart with OCI_TRANS_READONLY flag	Read only

In terms of the two properties mentioned above, read consistency and the possibility to modify data, this is the effect of the five OCI scenarios above:

	Statement-level read consistency ("non serializable")	Transaction-level read-consistency ("serializable")
Updates allowed ("read-write")	No explicit OCITransStart call OCITransStart with no flag OCITransStart with OCI_TRANS_READWRITE flag (Read Committed isolation level)	OCITransStart with OCI_TRANS_SERIALIZABLE flag (Serializable isolation level)
Updates forbidden ("read-only")	-	OCITransStart with OCI_TRANS_READONLY flag (Read-Only isolation level)

Transaction control in CORAL (present situation)

CORAL currently offers two types of transactions, of "readOnly" and "update" types. In the C++ code this is controlled by the boolean "readOnly" flag in the ITransaction::start() method.

There are three features of these CORAL transaction that are particularly relevant to this discussion:

1. CORAL expects users to always start a transaction explicitly. If any SELECT, DML or DDL is attempted while no (CORAL) transaction is active, CORAL will throw a TransactionNotActiveException. This is the case since the very first release CORAL_1_0_0.
2. CORAL "readOnly" transactions result on Oracle in a SET TRANSACTION READ ONLY statement, while "update" transactions result in a SET TRANSACTION READ WRITE (i.e. a READ COMMITTED) transaction.
3. Beyond calling SET TRANSACTION READ ONLY, CORAL "readOnly" transactions provide an additional functionality: CORAL internally allows only SELECT statements for these transactions, while it explictly forbids DML and DDL statements by throwing an InvalidOperationInReadOnlyTransactionException. This is a recent feature, implemented in CORAL_2_3_8 to fix what was considered a bug (bug #17873). Note in particular that, while DML would be blocked anyway by Oracle 'SET TRANSACTION READ ONLY', this is not the case for DDL, where CORAL truly provides an additional constraint (as mentioned above, any DDL commits before and after its execution, so any existing READ ONLY transaction would be committed and bypassed too). [Note instead that no attempt is made to handle DDL autocommits during CORAL "update" transactions. No warning is printed if users try to roll back some DML followed by DDL, for instance. After the DDL, Oracle also goes back in the default READ COMMITTED or READ WRITE isolation level, which is what the CORAL "update" transaction initially created - this would be different if CORAL was using a SERIALIZABLE update transaction.]

In terms of the two properties mentioned above, read consistency and the possibility to modify data, this is where the two current CORAL transactions fall:

	Statement-level read consistency ("non serializable")	Transaction-level read-consistency ("serializable")
Updates allowed ("read-write")	CORAL "update" transaction	-
Updates forbidden ("read-only")	-	CORAL "readOnly" transaction

Updates are forbidden partly by Oracle (DML) and partly by CORAL (DDL) in the bottom row of this table.

Transaction control in CORAL (proposed changes)

The present situation of transaction control in CORAL is not fully satisfactory.

The main issue, in our opinion, is the mapping of CORAL "readOnly" transactions to serializable READ ONLY transactions in Oracle. This has several drawbacks:

• Missing support for looser, statement-level, read consistency for read-only operations as in READ COMMITTED isolation levels is an issue. The CORAL server in the ATLAS HLT, for instance, needs the READ COMMITTED isolation level to read new trigger configuration data added after the start of the CORAL "readOnly" transaction. [In this case, a special mode enabled by setting the CORAL_ORA_SKIP_TRANS_READONLY environment variable ensures that no transaction is started explicitly via OCI. The net effect (selecting data via a READ COMMITTED isolation level) is actually almost the same as starting a CORAL "update" transaction (i.e. issuing SET TRANSACTION READ WRITE), modulo the fact that CORAL internally prevent DML and DDL in this special "non-serializable readOnly" transaction.]
• The transaction-level read consistency of Oracle READ ONLY transactions may not necessarily be needed, but it has a cost. Because of their use of UNDO segments (see the Oracle Concepts manual again), these transactions may result in various errors, such as ORA-01555 (as seen by ATLAS at Gridka in 2008) or ORA-01446 (as described in bug #87935).
• CORAL "readOnly" transactions are not mapped in the same way on all plugins. In particular, we understand that in Frontier, which is stateless and read-only, "there is no concept of a transaction and each query is independent" (as discussed by Dave in a private email thread following bug #57639); most likely the Frontier JDBC does not set any explicit transaction, hence uses the default READ COMMITTED mode. In other words, currently CORAL is internally inconsistent because the same code (start transaction; select) on Oracle will use a READ ONLY tx and on Frontier will use a READ COMMITTED tx, with different read consistency policies.
• CORAL provides some functionalities for transparently reconnecting after a "network glitch". To be consistent, if a READ ONLY tx is interrupted by a network glitch, CORAL should throw an exception and not try to reconnect and restart the transaction, because it could only start the tx at a later time, where the results of queries may be different from those executed in the original interrupted tx, started at an earlier time. Again, this is probably a subtlety that users are not interested in and a cost they are not prepared to pay, as they do not necessarily need a READ ONLY tx.

Our proposal would therefore be the following:

1. Extend the CORAL ITransaction API with a new method (set, begin...) accepting an enum instead of a boolean, adding READ COMMITTED "readOnly" transactions (and maybe SERIALIZABLE "update" transactions, though this is probably not needed).
2. Keep the old start method accepting a boolean, mapping it initially to the same behaviour presently observed (READ ONLY in Oracle, READ COMMITTED "readOnly" in Frontier). _Eventually the default behaviour for "readOnly" transactions could be switched from READ ONLY to READ COMMITTED if users are convinced this should be done.

In terms of the two properties mentioned above, read consistency and the possibility to modify data, this is where the three or four new CORAL transactions would fall:

	Statement-level read consistency ("non serializable")	Transaction-level read-consistency ("serializable")
Updates allowed ("read-write")	coral::ReadCommittedUpdate	(coral::SerializableUpdate)
Updates forbidden ("read-only")	coral::ReadCommittedReadOnly	coral::SerializableReadOnly

In the bottom row of this table, updates would be forbidden partly by Oracle (DML in READ ONLY) and partly by CORAL (DDL in READ ONLY, DDL and DML in READ COMMITTED).

In other words, CORAL would provide two additional features that are not implemented in Oracle:

• It would continue to force users to always start a transaction explicitly. This was in CORAL from the beginning and eventually could also be removed, if not really necessary.
• As was already the case for sessions, and only partially for transactions, the concept of CORAL ReadCommittedReadOnly and SerializableReadOnly would implement a protection against DML and DDL updates within a transaction.

-- AndreaValassi - 31-Jan-2012