set transaction isolation level allow_dirty_reads?

BEDE

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?

Mike Kutz

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

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

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.