DBA 101: What Does SERIALIZABLE Really Mean?

Follow @Oratweets

Also see: Day 12: The Twelve Days of SQL: Readers do not block writers; writers do not block readers

While browsing in the bookstore at Oracle OpenWorld 2010 on Sunday, I chanced upon the recently released second edition of Expert Oracle Database Architecture by Tom Kyte. On page 253 in Concurrency and Multi Versioning, Tom says:

“SERIALIZABLE does not mean that all transactions executed by users will behave as if they were executed one right after another in a serial fashion. It does not imply that there is some serial ordering of the transactions that will result in the same outcome. The phenomena previously described by the SQL standard do not make this happen.”

This is contradicted by the language in the chapter on Data Concurrency and Consistency in Oracle Database Concepts 11g Release 1. Identical language can even be found in earlier editions going all the way back to Oracle7 Server Concepts Release 7.3 (PDF).

“To describe consistent transaction behavior when transactions run at the same time, database researchers have defined a transaction isolation model called serializability. The serializable mode of transaction behavior tries to ensure that transactions run in such a way that they appear to be executed one at a time, or serially, rather than concurrently.”

The above language can be traced to a 1995 Oracle document titled Concurrency Control, Transaction Isolation and Serializability in SQL92 and Oracle7 (Part number A33745) by Ken Jacobs (Dr. DBA) and others. You can ask Oracle Support for a copy if you can’t find it online.

“To describe fully consistent transaction behavior when transactions execute concurrently, database researchers have defined a transaction isolation model called “serializability”. The intention of the serializable mode of transaction execution is to ensure that transactions execute in such a way that they appear to be executed one at a time (“serially”), rather than concurrently. … In other words, concurrent transactions executing in serializable mode are only permitted to make database changes they could have made if the transactions had been scheduled to execute one after another, in some specific order, rather than concurrently. This mode ensures that transactions move the database from one consistent state to another consistent state, because potentially harmful interactions between concurrently executing transactions are prevented.”

Almost identical language occurs in Oracle’s patent on multi versioning awarded to Ken Jacobs (Dr. DBA) and others in 1999.

“To describe fully consistent transaction behavior when transactions execute concurrently, database researchers have defined a transaction isolation level called “serializability.” In the serializable isolation level, transactions must execute in such a way that they appear to be executed one at a time (“serially”), rather than concurrently. … In other words, concurrent transactions executing in serializable mode are only permitted to make database changes that they could have made if the transactions had been scheduled to execute one after another, in some specific order, rather than concurrently. This mode ensures that transactions move the database from one consistent state to another consistent state by preventing potentially harmful interactions between concurrently executing transactions.”

So where’s the potential for a misunderstanding? In A Critique of ANSI SQL Isolation Levels by Microsoft and Sybase researchers, we find the following comment.

“ANSI SQL defines four levels of isolation by the matrix of Table 1. Each isolation level is characterized by the phenomena that a transaction is forbidden to experience (loose or strict interpretations). However, the ANSI SQL specifications do not define the SERIALIZABLE isolation level solely in terms of these phenomena. Subclause 4.28, “SQL-transactions”, in [ANSI] notes that the SERIALIZABLE isolation level must provide what is “commonly known as fully serializable execution.” The prominence of the table compared to this extra proviso leads to a common misconception that disallowing the three phenomena implies serializability. [emphasis added]”

In fact, we find the following definitions in the SQL-92 standard. The language has not changed over the years; identical language can be found in the Foundation document of the SQL-2008 standard (12 MB ZIP file).

“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.”

We also find the following explanation in the SQL-92 standard. Identical language can also be found in the SQL-2008 standard.

“The isolation levels are different with respect to phenomena P1, P2, and P3. Table 9, “SQL-transaction isolation levels and the three phenomena” specifies the phenomena that are possible and not possible for a given isolation level.

Table 9—SQL-transaction isolation levels and the three phenomena

Level	P1	P2	P3
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

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.”

Note the use of the word “consequence” in the above text from the SQL standard. In other words, the exclusion of the three undesirable phenomena is a consequence of the serializable requirement; it does not guarantee serializable execution. Oracle Database Concepts 11g Release 1 alludes to the problem in the following text taken from the chapter on Data Concurrency and Consistency.

“Although Oracle Database 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 consider the fact that reads in Oracle Database do not block writes as they do in other systems. Transactions that check for database consistency at the application level can 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 Database from other environments.”

In another place in the same chapter, the caution is repeated using different words.

“Because Oracle Database does not use read locks in either read-consistent or serializable transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level cannot assume that the data they read will remain unchanged 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 with this in mind, even when using serializable transactions.”

The above two excerpts from the Oracle Database concepts guide can also be traced to the 1995 Oracle paper by Ken Jacobs referenced earlier. Identical language can also be found in earlier editions of the concepts guide going all the way back to Oracle7 Server Concepts Release 7.3. The conclusion is that SERIALIZABLE does mean that that all transactions executed by users will behave as if they were executed one right after another in a serial fashion but SERIALIZABLE in Oracle Database does not mean exactly that. The language in the chapter on Data Concurrency and Consistency in Oracle Database Concepts 11g Release 2 is an accurate reflection of the Oracle Database approach to serializability:

“To describe consistent transaction behavior when transactions run concurrently, database researchers have defined a transaction isolation model called serializability. A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database. [emphasis added]”

As an example, create two tables called PARENT and CHILD as follows and insert one row into the PARENT table.

create table parent (
  parent_name varchar(8)
);

create table child (
  child_name varchar(8),
  parent_name varchar(8)
);

insert into parent values('Warbucks');

The following timeline of two concurrent transactions A and B shows that it is possible to create an orphan record.

18:25:07 TRANSACTION A> alter session set isolation_level=serializable;

Session altered.

18:25:07 TRANSACTION A> select * from parent where parent_name='Warbucks';

PARENT_N
--------
Warbucks

1 row selected.

          18:25:16 TRANSACTION B> alter session set isolation_level=serializable;

          Session altered.

          18:25:16 TRANSACTION B> select * from child where parent_name='Warbucks';

          no rows selected

18:25:19 TRANSACTION A> insert into child values ('Annie','Warbucks');

1 row created.

          18:25:21 TRANSACTION B> delete from parent where parent_name='Warbucks';

          1 row deleted.

18:25:23 TRANSACTION A> commit;

Commit complete.

          18:25:25 TRANSACTION B> commit;

          Commit complete.

Here are the contents of the two tables after the experiment. Annie is now an orphan. 😦

18:25:28 SQL> select * from parent;

no rows selected

18:25:28 SQL> select * from child;

CHILD_NA PARENT_N
-------- --------
Annie    Warbucks

1 row selected.

The solution for the above example is to use SELECT FOR UPDATE or to define a referential integrity constraint (since it will use SELECT FOR UPDATE internally). In the above example, the delete transaction will then fail with the error message “ORA-08177: can’t serialize access for this transaction.” If not using the serializable isolation level, the delete transaction will fail with the error message “ORA-02292: integrity constraint (%s.%s) violated – child record found.”

The Data Consistency and Concurrency Challenge

Concurrency—There’s No Free Lunch