Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Unanswered: A high percentage of my transactions rollback

    My oracle 9i DB is regularly rolling back user transactions.

    Several applications are running on the DB. Most of the time over 70% of the transactions rolled back.

    I have no idea what the applications are doing , but the high percentage of rollbacks caused lots of concern from management.

    Could it be caused by improper configuration ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Could it be caused by improper configuration ?
    ROLLBACK does not happen automagically.
    I suspect improperly coded application.
    Is application written in Java?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anacedent
    >Could it be caused by improper configuration ?
    ROLLBACK does not happen automagically.
    I suspect improperly coded application.
    Is application written in Java?
    Why is the programming language important?

  4. #4
    Join Date
    Jan 2009
    Posts
    4
    Two in PowerBuilder, one is a report writer but not sure in what language.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I encountered 1 Java application which did
    SELECT SYSDATE INTO TIME_TEMP FROM DUAL;
    ROLLBACK;
    before issuing every valid SQL "just to be sure connection & Oracle were working".
    Why anyone thought a ROLLBACK of a SELECT was ever appropriate, boggled my mind.

    ROLLBACK is/are application induced activity.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anacedent
    I encountered 1 Java application which did
    SELECT SYSDATE INTO TIME_TEMP FROM DUAL;
    ROLLBACK;
    That behaviour has nothing to do with Java though.
    You can do wrong stuff in any language. Actually it looks more like a C program because of the SELECT ... INTO statement which is not possible with standard JDBC.

    Why anyone thought a ROLLBACK of a SELECT was ever appropriate, boggled my mind.
    When using a connection pool it is quite common to configure a statement that is issued before the pool returns a cached connection to the application to make sure it is still active (it could have been "killed" by network problem, or because the server node in the cluster is no longer there, or Oracle has been restarte, ...)

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tortoro
    I have no idea what the applications are doing , but the high percentage of rollbacks caused lots of concern from management.
    Why not ask the developer/manufacturer?

    Or even better: the users.
    Maybe they are initiating the rollback from within the application and everything is perfectly fine.

    The real question is: is the application doing everything correctly? If yes, then why should the rollbacks be a problem? (apart from a very strange application design maybe)

    Are there any performance problems with this?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also if the application is not auto-committing and they don't issue a commit, it will be rolled back when they leave.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh
    Also if the application is not auto-committing and they don't issue a commit, it will be rolled back when they leave.
    Who with a sane mind would ever use auto-commit? That's calling for trouble
    It's the same as turning off transactions completely (if that would be possible)

  10. #10
    Join Date
    May 2009
    Location
    India
    Posts
    66
    To take it forward, the next step should be to view the appropriate system generated logs. If needed, you will have to reconfigure the database.

    This will likely tell you the "culprit" SQL statements and you can address them separately.

    I heard from one of my friends (an Oracle expert) that a way to force a commit without changing the application is to shorten the size of the rollback segment files since it will changeover to a new file automatically and the changeover automatically triggers a commit. This will of course interfere with the application in subtle ways but if it is mostly a single app (so serialised), then no noticable problems.

    Another point,
    Two in PowerBuilder, one is a report writer but not sure in what language.
    If the reports are summaries and aggregates or looking up master tables, they are likely locking the database. You can ask the user to quit the menu and login again when needed.

    End

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by AnanthaP
    I heard from one of my friends (an Oracle expert) that a way to force a commit without changing the application is to shorten the size of the rollback segment files since it will changeover to a new file automatically and the changeover automatically triggers a commit.
    This sounds like a very questionable statement. Oracle will never automatically commit a transaction.
    If the rollback segment (undo tablespace since Oracle 9) is too small Oracle will thrown an error.
    I would be seriously concerned if Oracle did commit my transactions on its own behalf (except when running in autocommit mode)

    Another point, If the reports are summaries and aggregates or looking up master tables, they are likely locking the database. You can ask the user to quit the menu and login again when needed.
    SELECT statements (and that's what a report normally does), never locks anything in Oracle
    (unless you specify FOR UPDATE which would be very uncommon for a report, and then it would still not lock other SELECTs)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •