When using Oracle, or other MVCC (Multi Version Concurrency Control) databases like Postgresql or MySql in combination with InnoDb, a transaction with the SERIALIZABLE isolation level doesn't always block (a pessimistic approach) when it conflicts with another transaction. Instead a more optimistic approach is used. This approach however can lead to an abort of the transaction with the feared: "ORA-08177: can't serialize access for this transaction", which in essence is just an optimistic locking failure.

A SERIALIZABLE transaction in classic lock based databases, is implemented by using pessimistic locking. The transaction needs to obtain the exclusive table lock prior to accessing the table. This prevents other SERIALIZABLE transactions and non SERIALIZABLE transactions (these need to obtain the shared table lock) from executing concurrently. The consequence is that a SERIALIZABLE transaction blocks until the exclusive table lock is obtained, or when it is rolled back (eg a timeout, detection of a deadlock, etc).

In Oracle no exclusive table lock is required to implement the SERIALIZABLE isolation level. Instead Oracle is able to take a snapshot of the database and use this snapshot for the duration of the transaction (transaction level read consistency). This is realized by reading the SCN (the System Change Number: a version number that is increased when a transaction commits) when the transaction begins and using this SCN and the data in the rollback segment, to reconstruct previous versions of records. The consequence of this approach is that different transactions could see different snapshots at the same moment (hence Multi Version Concurrency Control). Therefor a SERIALIZABLE transaction is not able to see other updates (so no worries about unrepeatable reads) but also not other inserts/deletes (so no phantom reads).

In short: instead of relying on pessimistic locks to prevent isolation problems, the transaction is able to see the state of the database as it was when the transaction began, even though other transactions could have changed the database in the meanwhile! This is one of the reasons why Oracle is able to perform well under heavy load.

There is one caveat: most transactions do more than just reading (else the READ_ONLY isolation level would have been a better solution). When a transaction modifies (inserts, updates, deletes) records, these records are exclusively locked for the remaining duration of the transaction (these changes are visible inside the transaction of course), independent of the isolation level. But once a transaction ends, these locks are released and without extra protection, it could happen that a SERIALIZABLE transaction updates an older version of the record (a reconstructed one) than the last committed version. This problem is called a lost update.

Luckily Oracle protects us from lost updates with SERIALIZABLE transactions by adding an optimistic lock: when the transaction tries to update a record that has been committed after the transaction began, it knows that a different transaction has modified it and the transaction aborts with the "ORA-08177: can't serialize access for this transaction". Just like optimistic locking failures in normal database communication, the standard policy of dealing with this situation is to retry the transaction and hope that the transaction isn't interfered again.

Filed under Concurrency Control, Oracle | 3 Comments »