Обсуждение: Q about read committed in Oracle...

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

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!

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

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!

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!

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

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!