Sorry for re-posting - this message has right charset...

This is said in Oracle7 Server Concepts Manual, Data Concurrency,              
Additional Considerations for Serializable Isolation:                          
                                                                               
---                                                                            
Both read committed and serializable transactions use row-level locking, and   
both will wait if they try to change a row updated by an uncommitted           
concurrent transaction. The second transaction that tries to update a given    
row waits for the other transaction to commit or rollback and release its      
lock. If that other transaction rolls back, the waiting transaction            
(regardless of its isolation mode) can proceed to change the previously        
locked row, as if the other transaction had not existed.                       
                                                                               
However, read committed and serializable transactions behave differently if    
the other (blocking) transaction commits. When the other transaction commits   
and releases its locks, a read committed transaction will proceed with its     
intended update...                                                             
^^^^^^^^                                                                       
---                                                                            
                                                                               
What does this mean? Will Oracle update this row (just updated by other        
Xaction)? In any case or only if qualification is ok for this row now 
(qual was ok for unchanged version of row but could be changed by
concurrent Xaction)?

Could someone run in Oracle test below?

1.  CREATE TABLE test (x integer, y integer)
2.  INSERT INTO test VALUES (1, 1);
    INSERT INTO test VALUES (1, 2);
    INSERT INTO test VALUES (3, 2);
3.  run two session T1 and T2 (in read committed mode)
4.  in session T2 run
    UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
5.  in session T1 run
    UPDATE test SET y = 3 WHERE x = 1;
6.  in session T2 run
    COMMIT;
7.  in session T1 run
    SELECT * FROM test;         -- results?
8.  in session T1 run
    COMMIT;
9.  now in session T2 run
    UPDATE test SET x = 2;
10. in session T1 run
    UPDATE test SET y = 4 WHERE x = 1;
11. in session T2 run
    COMMIT;
12. in session T1 run
    SELECT * FROM test;         -- results?

TIA,
        Vadim

Tue, 14 Jul 1998 19:14:10 +0800
Vadim Mikheev <vadim@krs.ru>
Q about read committed in Oracle...
On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
> Could someone run in Oracle test below?

I could, but how do I make Oracle use read committed mode?

> 
> 1.  CREATE TABLE test (x integer, y integer)
> 2.  INSERT INTO test VALUES (1, 1);
>     INSERT INTO test VALUES (1, 2);
>     INSERT INTO test VALUES (3, 2);
> 3.  run two session T1 and T2 (in read committed mode)
> 4.  in session T2 run
>     UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
> 5.  in session T1 run
>     UPDATE test SET y = 3 WHERE x = 1;
> 6.  in session T2 run
>     COMMIT;
> 7.  in session T1 run
>     SELECT * FROM test;         -- results?
> 8.  in session T1 run
>     COMMIT;
> 9.  now in session T2 run
>     UPDATE test SET x = 2;
> 10. in session T1 run
>     UPDATE test SET y = 4 WHERE x = 1;
> 11. in session T2 run
>     COMMIT;
> 12. in session T1 run
>     SELECT * FROM test;         -- results?
> 
> TIA,
>         Vadim

Michael
-- 
Dr. Michael Meskes		meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire!	Use Debian GNU/Linux! 

Mon, 27 Jul 1998 16:45:33 +0200
"Dr. Michael Meskes" <meskes@online-club.de>
Re: [HACKERS] Q about read committed in Oracle...
Dr. Michael Meskes wrote:
> 
> On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
> > Could someone run in Oracle test below?
> 
> I could, but how do I make Oracle use read committed mode?

"...You can set the isolation level of a transaction by using 
one of these commands at the beginning of a transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION ISOLATION LEVEL READ ONLY;
..."

TIA,
	Vadim

Tue, 28 Jul 1998 04:31:38 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Q about read committed in Oracle...
On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
> Could someone run in Oracle test below?
> 1.  CREATE TABLE test (x integer, y integer)
> 2.  INSERT INTO test VALUES (1, 1);
>     INSERT INTO test VALUES (1, 2);
>     INSERT INTO test VALUES (3, 2);
> 3.  run two session T1 and T2 (in read committed mode)
> 4.  in session T2 run
>     UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
> 5.  in session T1 run
>     UPDATE test SET y = 3 WHERE x = 1;

Blocked until 6 is executed.

> 6.  in session T2 run
>     COMMIT;
> 7.  in session T1 run
>     SELECT * FROM test;         -- results?

         X          Y
---------- ----------
         1          3
         1          3
         1          2

> 8.  in session T1 run
>     COMMIT;
> 9.  now in session T2 run
>     UPDATE test SET x = 2;
> 10. in session T1 run
>     UPDATE test SET y = 4 WHERE x = 1;

Blocked again until after 11. Nothing is updated.

> 11. in session T2 run
>     COMMIT;
> 12. in session T1 run
>     SELECT * FROM test;         -- results?

         X          Y
---------- ----------
         2          3
         2          3
         2          2

Michael
-- 
Dr. Michael Meskes		meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire!	Use Debian GNU/Linux! 

Tue, 28 Jul 1998 21:25:57 +0200
"Dr. Michael Meskes" <meskes@online-club.de>
Re: [HACKERS] Q about read committed in Oracle...
On Tue, Jul 28, 1998 at 04:31:38AM +0800, Vadim Mikheev wrote:
> Dr. Michael Meskes wrote:
> > 
> > On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
> > > Could someone run in Oracle test below?
> > 
> > I could, but how do I make Oracle use read committed mode?
> 
> "...You can set the isolation level of a transaction by using 
> one of these commands at the beginning of a transaction:

Hmm, do I have to re-set it after a commit? I didn't do that though.
Shall I re-run?

Michael
-- 
Dr. Michael Meskes		meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire!	Use Debian GNU/Linux! 

Tue, 28 Jul 1998 21:27:50 +0200
"Dr. Michael Meskes" <meskes@online-club.de>
Re: [HACKERS] Q about read committed in Oracle...
First, thanks Michael!

It's nice to see expected results but I still have some
new questions - please help!

1.  CREATE TABLE test (x integer, y integer)
2.  INSERT INTO test VALUES (1, 1);
    INSERT INTO test VALUES (1, 2);
    INSERT INTO test VALUES (3, 2);
3.  run two session T1 and T2 
4.  in session T2 run
    UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
5.  in session T1 run
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE test SET y = 3 WHERE x = 1;
    --
    -- 1st record will be changed by T2, qual for new record
    -- version will be OK, but T1 should be aborted (???)
    --
6.  in session T2 run
    COMMIT;
7.  in session T1 run
    ROLLBACK;               -- just to be sure -:)
8.  now in session T2 run
    UPDATE test SET x = 2;
9.  in session T1 run
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    UPDATE test SET y = 4 WHERE x = 1 or x = 2;
11. in session T2 run
    COMMIT;
12. in session T1 run
    SELECT * FROM test;         -- results?
    ^^^^^^^^^^^^^^^^^^
I would like to be sure that T1 will update table...

TIA,
        Vadim

Thu, 30 Jul 1998 16:40:13 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Q about read committed in Oracle...
On Thu, Jul 30, 1998 at 04:40:13PM +0800, Vadim Mikheev wrote:
> 1.  CREATE TABLE test (x integer, y integer)
> 2.  INSERT INTO test VALUES (1, 1);
>     INSERT INTO test VALUES (1, 2);
>     INSERT INTO test VALUES (3, 2);
> 3.  run two session T1 and T2 
> 4.  in session T2 run
>     UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
> 5.  in session T1 run
>     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>     UPDATE test SET y = 3 WHERE x = 1;

UPDATE test SET y = 3 WHERE x = 1
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

>     --
>     -- 1st record will be changed by T2, qual for new record
>     -- version will be OK, but T1 should be aborted (???)
>     --
> 6.  in session T2 run
>     COMMIT;
> 7.  in session T1 run
>     ROLLBACK;               -- just to be sure -:)
> 8.  now in session T2 run
>     UPDATE test SET x = 2;
> 9.  in session T1 run
>     SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
>     UPDATE test SET y = 4 WHERE x = 1 or x = 2;

blocked

> 11. in session T2 run
>     COMMIT;
> 12. in session T1 run
>     SELECT * FROM test;         -- results?
>     ^^^^^^^^^^^^^^^^^^
> I would like to be sure that T1 will update table...

         X          Y
---------- ----------
         2          4
         2          4
         2          4

Michael
-- 
Dr. Michael Meskes		meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire!	Use Debian GNU/Linux! 

Thu, 30 Jul 1998 21:41:30 +0200
"Dr. Michael Meskes" <meskes@online-club.de>
Re: [HACKERS] Q about read committed in Oracle...