FAQ
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

Search Discussions

  • Vadim Mikheev at Jul 14, 1998 at 11:15 am
    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
  • Dr. Michael Meskes at Jul 27, 1998 at 6:16 pm

    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!
  • Vadim Mikheev at Jul 27, 1998 at 8:32 pm

    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
  • Dr. Michael Meskes at Jul 28, 1998 at 7:50 pm

    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!
  • Dr. Michael Meskes at Jul 28, 1998 at 7:50 pm

    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!
  • Vadim Mikheev at Jul 30, 1998 at 8:40 am
    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
  • Dr. Michael Meskes at Jul 30, 1998 at 7:41 pm

    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!
  • Andreas Zeugswetter at Jul 30, 1998 at 9:32 am

    Vadim wrote:
    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;
    2 rows updated.
    5. in session T1 run
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE test SET y = 3 WHERE x = 1; blocks
    --
    -- 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 -:)
    UPDATE test SET y = 3 WHERE x = 1
    *
    ERROR at line 1:
    ORA-08177: can't serialize access for this transaction
    SQL> rollback;

    Rollback complete.
    8. now in session T2 run
    UPDATE test SET x = 2;
    3 rows updated.
    9. in session T1 run
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    UPDATE test SET y = 4 WHERE x = 1 or x = 2; blocks
    11. in session T2 run
    COMMIT;
    Commit complete.
    in T1: 3 rows updated.
    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

    So it does.

    Andreas
  • Dr. Michael Meskes at Jul 30, 1998 at 7:40 pm

    On Thu, Jul 30, 1998 at 11:32:44AM +0200, Andreas Zeugswetter wrote:
    Vadim wrote:
    It's nice to see expected results but I still have some
    new questions - please help!
    ...
    It seems you had the results in the wrong order Andreas.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 14, '98 at 11:05a
activeJul 30, '98 at 7:41p
posts10
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase