Re: Dirty Read

A copy of this was sent to goldenge_at_my-deja.com (if that email address didn't require changing) On Wed, 22 Mar 2000 09:25:59 GMT, you wrote:

>Hi All,
> In Oracle database server , How can I read the uncommitted
>information? Suggest, there are two transaction will update the same
>record at the same time. This will deadlock.

no it is not a deadlock. This is called blocking. A deadlock is when session 1 updates row A, session 2 updates row B, session 1 then attempts to update row B (and blocks -- session 2 has that locked) and then session 2 attempts to update row A -- thats a deadlock and those are automatically detected for you. The database will fail one of the statements that are deadlocked in either session 1 or session 2.

>In our bussiness transaction, the table have a HOLD flag column, when
>user update the record, first he check the HOLD flag , if this is null,
>then he can update this record, and then he update this flag,(this
>update information is uncommitted information, other user can not read
>this from anthor transaction ). If the HOLD flag is not null, then the
>user can get the information that this record is hold by another, so he
>can not update this record. The key problem is another user connect to
>the datatabase from another transaction, he can not read first user
>update the HOLD flag, this flag will be committed with the whole
>transaction .
>
>
>pls give some help!!!!!

there are NO dirty reads in Oracle. None.

Besides -- the above would not necessarily work, you need a TEST and SET (atomic operation) if you want to mimick a semaphore (which is what you are trying to do). You would have to code in the application a compensating UPDATE as well before you commit to null out the flag (else the row would appear to be locked when it is not).

In Oracle -- what you want to do is this:

    select * from T where <condition to select the row> FOR UPDATE NOWAIT;     ...
    update T set ..... where <some conditation as above>;     ...

The select for update nowait will get and lock the row *if it is not already locked* (if it is -- ORA-54 will be returned). You can now update it without worry of blocking and any other session that does the select for update nowait will get an error back immediately telling them the row is already locked by another session.

>
>Best Regards
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

-- 
http://osi.oracle.com/~tkyte/
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation