This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,147 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

set transaction isolation level allow_dirty_reads?

BEDE
BEDE Oracle DeveloperBucharest, RomaniaMember Posts: 2,484 Gold Trophy
edited Nov 16, 2018 9:36AM in Database Ideas - Ideas

Wouldn't it be in some situations good to have the option of allowing dirty reads? It's great not to have any read locks, but this mechanism quite frequently leads to the famous "snapshot too old". Of course dirty read would in many cases not be tolerable, but there may be cases when such an approach would be admitted. Why not offer such an option to the developers?

6 votes

Active · Last Updated

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,317 Gold Crown
    Wouldn't it be in some situations good to have the option of allowing dirty reads?

    No!  It is not great.

    "snapshot too old" usually indicates that you are doing something wrong.  It means "data is no longer available in the Table or UNDO tablespace"

    This usually occurs because your code COMMITS too frequently while doing a SLOW-BY-SLOW operation.

    This style of code "quite frequently leads to the famous 'snapshot too old'" .. not how the database prevents "dirty reads"

    This style of code is "Bad Code" for Oracle databases.

    FIX YOUR CODE!  DO NOT "FIX" THE DATABASE BECAUSE OF BAD CODE!

    Now, there are cases where "code follows proper method" and that error is still thrown.

    At that point, you just need to increase your UNDO space.

    My $0.02

    MK

  • Billy Verreynne
    Billy Verreynne Software Engineer Cape TownMember Posts: 28,976 Red Diamond

    Dirty reads will try to address the symptoms of the problem, while crudely and wilfully ignoring the root cause of the problem.

    This is not a robust, or logical, approach.

  • Billy Verreynne
    Billy Verreynne Software Engineer Cape TownMember Posts: 28,976 Red Diamond
    Wouldn't it be in some situations good to have the option of allowing dirty reads?

    No!  It is not great.

    "snapshot too old" usually indicates that you are doing something wrong.  It means "data is no longer available in the Table or UNDO tablespace"

    This usually occurs because your code COMMITS too frequently while doing a SLOW-BY-SLOW operation.

    This style of code "quite frequently leads to the famous 'snapshot too old'" .. not how the database prevents "dirty reads"

    This style of code is "Bad Code" for Oracle databases.

    FIX YOUR CODE!  DO NOT "FIX" THE DATABASE BECAUSE OF BAD CODE!

    Now, there are cases where "code follows proper method" and that error is still thrown.

    At that point, you just need to increase your UNDO space.

    My $0.02

    MK

    Mike Kutz wrote:Now, there are cases where "code follows proper method" and that error is still thrown.At that point, you just need to increase your UNDO space.

    Mike raised an important issue - UNDO space is a critical resource of the database. Like CPU. Or memory.

    Wanting to undersize the UNDO, undermines the database. Like putting water in your car's fuel tank as petrol is an expensive resource, and water is also liquid, contains hydrogen and oxygen (used in rocket fuel), and is much cheaper than petrol.