Breaking Oracle SERIALIZABLE

The SERIALIZABLE isolation level, is the highest isolation level for transactions. When transactions are executed under this isolation level, they should behave as they were being executed serially (one after another). However under Oracle, and all other databases that use Multi Version Concurrency Control (MVCC) like Postgresql, MySQL + InnoDb, it is possible to to break this behavior. At the moment I’m working on an STM implementation that also uses MVCC, so it suffers from the same problem, I’ll get back to this in another blogpost.

The problem

If we have 2 tables A (with column a) and table B (with column b). And two transactions, with SERIALIZABLE isolationlevel, execute the following statements concurrently:

transaction1 INSERT INTO B (SELECT COUNT(*) FROM A)
transaction2 INSERT INTO A (SELECT COUNT(*) FROM B)

What do you think the result is going to be? The SERIALIZABLE isolation level mandates a serial execution of transactions, so transaction1 is executed first, or transaction 2 is executed first. If Transaction 1 executes first, the result would be A=1, and B=0. If Transaction2 executes first, the result would be A=0 and B=1. With MVCC databases the result can also be A=0 and B=0. How is this possible?

The cause

Transactions with the SERIALIZABLE isolation level, use a snapshot of the world for the duration of the transaction (same goes for the READ_ONLY isolation level), so during the execution of the transaction it will not see changes made by other transactions (it will see its own changes of course). When the transaction wants to commit, the database checks if there are conflicts and if there are, the transaction is aborted and you get the feared ORA-08177: can’t serialize access for this transaction.

Time Transaction 1 Transaction 2
T1 start
T2 start
T3 INSERT INTO B (SELECT COUNT(*) FROM A)
T4 INSERT INTO A (SELECT COUNT(*) FROM B)
T5 commit
T6 commit

At T4, Transaction2 won’t see the record that has been inserted in B by Transaction1, so 0 is inserted in table A. When Transaction1 wants to commit at T5, it can be committed because there were no conflicting writes. When Transaction2 wants to commit at T5, it can commit because it also has no conflicting writes. So this is why the result is A=0 and B=0. In most cases it won’t be an issue, but it is interesting to know that MVCC is not perfect.

If you want to know more about this problem, I advice the excellent books of Thomas Kyte.

Advertisements

8 Responses to Breaking Oracle SERIALIZABLE

  1. […] in practice.. I don’t know.. but it is something to keep in mind. I have placed a similar post about this issue in […]

  2. Seun Osewa says:

    I don’t understand what you mean by “breaking this behaviour”. Do you mean incorrect results or the failure? In my opinion, the failure case (Oracle) doesn’t seem to be a real problem since you can just retry the failed transaction.

    In any case, MySQL+InnoDB in serializable mode doesn’t fail like Oracle or yield false results like PostgreSQL, because it uses strict 2 phase locking for SERIALIZABLE mode. I tested it just now. 2 phase locking is very much under-rated, really.

    • pveentjer says:

      Hi Sean,

      there is nothing to retry because the conflict is not detected. So the transactions will commit and put the database in an invalid state (so you get incorrect results). So retrying (so undoing to committed changes and trying again) is very hard.

      And afaik all MVCC database solutions don’t provide readset conflict detection. And 2 phase locking is not going to help you in this case, because for a read, no locking is required in an MVCC solution. Only when you do a ‘select for update’ you will have the lock you need.

      In Multiverse (a MVCC based STM solution) I added special support for dealing with the writeskew problem:
      http://multiverse.codehaus.org/manual-mapping.html#transactionalmethod.writeSkew

      • Seun Osewa says:

        Hello Peter,

        Wow, you’re right then. PostgreSQL and Oracle are both broken in this respect. It’s funny because they are regarded as the most ‘reliable’ open and closed source databases respectively. Yet they think it’s ok to allow write skew.

        (I was confused because of the “ORA-08177: can’t serialize access for this transaction” reference.)

        However, InnoDB’s ‘serializable’ mode is not broken. According to the manual, it converts every ‘select’ to a ‘select for update’ in ‘serializable’ mode:
        http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_serializable

        I’ve tested it on many write skew examples and in each case it gives the correct results. Which is quite funny, really.

        Regards,
        Seun.

  3. pveentjer says:

    PS: Strict 2 phase locking won’t give you serializable mode using MVCC. The only thing it does is prevent deadlocks.

  4. Nicolas Barbier says:

    PostgreSQL will probably support real serializable transactions starting at version 9.1, see:

  5. Nicolas Barbier says:

    PostgreSQL will probably support real serializable transactions starting at version 9.1, see:

    http://wiki.postgresql.org/wiki/Serializable

  6. fcr says:

    In fact, postgresql 9.1 supports the ISOLATION LEVEL SERIALIZABLE using “serializable snapshot isolation”, it’s explained in an interesting paper: http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: