Thursday, February 26, 2009

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

3 comments:

  1. SydOracleFebruary 27, 2009 at 9:58 PM

    The basic nugget is that every statement in the transaction happens as if it all happened at the same instant (ie the SCN that the tansaction started).
    In the default mode, each SQL runs as of the SCN the statement starts executing.

    Wouldn't expect it on a straight SELECT (maybe a SELECT ...FOR UPDATE).
    The error crops up when you try to get a record in current mode (ie you want to update/delete it) and it has changed since the start of the transaction.
    In default mode, the statement would lock until that other transaction completed, then restart as of a new SCN.
    Since in SERIALIZABLE mode it can't use a new SCN, it just falls over.

    The 'solutions' are (1) make the transactions complete faster so they are less likely to overlap and (2) grab all the locks you need as early as possible.
    Shouldn't be trigger related unless they have autonomous transactions.

    "usage of serializable isolation level is a design error that leads to non-scalable applications"
    Don't agree with the word 'error'. It is a design choice. When you have a transaction of multiple statements, in default mode, the statements do not see a consistent picture of the data - even though each statement is individually consistent.
    There's a balance between consistency and concurrency. SERIALIZABLE moves that balance a bit more towards consistency.

    ReplyDelete
  2. AnonymousMay 1, 2009 at 1:05 AM

    Here is how to get around the error
    http://www.devx.com/dbzone/Article/41591/1954?pf=true

    ReplyDelete
  3. SoappmediaJune 26, 2017 at 3:19 PM

    Great Post. really appreciate this information, very helpful. Thank you for sharing!

    ReplyDelete