ORA-08177 can't serialize access for this transaction We've been getting this error recently and we are in the process of researching the problem.

I figured I might as well share some of my reference material in case someone else may find it useful.

First, what is it? From the docs:

ORA-08177: can't serialize access for this transaction
Cause: Encountered data changed by an operation that occurred after the start of this serializable transaction.

Action: In read/write transactions, retry the intended operation or transaction.

There are 4 isolation levels when it comes to transaction processing, all are based on the ANSI/ISO SQL standard SQL92:

• READ UNCOMMITTED
• READ COMMITTED
• SERIALIZABLE
• REPEATABLE READ

Oracle handles these the following ways:

READ UNCOMMITTED:
Oracle Database never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle Database.

READ COMMITTED
Oracle Database meets the READ COMMITTED isolation standard. This is the default mode for all Oracle Database applications. Because an Oracle Database query only sees data that was committed at the beginning of the query (the snapshot time), Oracle Database actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation.

SERIALIZABLE
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.

REPEATABLE READ
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.

I've learned more about transaction isolation levels in the last few days than I ever cared to know.

READ COMMITTED is the default for Oracle. There's a possibility that our calling applications are using SERIALIZABLE via the driver. I've never worked with SERIALIZABLE.

According to the trace files it is happening on both INSERTs and SELECTs and we've seen multiple occurrences on the same 2 statements.

I suspect, but can't prove, it's (evil) trigger related. Any pointers as to how to prove that would be most helpful. Or any pointers in general in dealing with this issue.

Update
I did find this post where the author says:

Once more - in the vaste majority of cases usage of serializable isolation level is a design error that leads to non-scalable applications. Most databases enforce it with very restictive locks that kill concurrency and in case of Oracle and other databases that rely on multiversioning (PosgreSQL, Interbase) you have to be ready to failed ("Can not serialize") transactions. Latter is, usually, tolerable as soon as application is prepared for them (normally, it is enough just to restart transaction). For Oracle quite good discussion of this topic (as almost any other Oracle-related topic) may be found on asktom.oracle.com (http://asktom.oracle.com).

Unfortunately it was just a statement without the proof to back it up. I'm trying to get away from just making blanket statements without providing the evidence.

Labels: error, ORA-08177, oracle

  ¶ 2/26/2009 09:09:00 PM