Re: column update order

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Nov 2006 06:31:16 -0800
Message-ID: <1164119475.697683.144510@h54g2000cwb.googlegroups.com>

Charles Hooper wrote:
> Charles Hooper wrote:
> > An individual UPDATE, INSERT, or DELETE will be consistent as of the
> > time the DDL begins, as will a SELECT. Additionally, within a
> > transaction, all SELECTs, UPDATEs, INSERTs, and DELETEs will be
> > consistent as of the start of the transaction.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Should state DML (manipulation) not DDL (definition), in case that
> causes any confusion. DDL causes an implicit COMMIT.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Note: Transaction Isolation Levels can be altered at the session level. The default transaction isolation level does not enforce that SELECTs, UPDATEs, INSERTs, and DELETEs will be consistent as of the start of the transaction. If a column value is updated and committed in another session, the changed values will be visible in the second session. For example:

__Session 1__
CREATE TABLE TEST1 (
  X NUMBER(12,4),
  Y NUMBER(12,4)); CREATE TABLE TEST2 (
  I NUMBER(12,4),
  J NUMBER(12,4)); INSERT INTO TEST1 VALUES (1,1); SELECT
  *
FROM
  TEST1;          X Y

1 1

1 ROW SELECTED Note that I did not commit in session 1.

__Session 2__
SELECT
*
FROM
TEST1; X Y

0 ROWS SELECTED INSERT INTO TEST2 VALUES (5,1); 1 ROW INSERTED SELECT
*
FROM
TEST2; I J

5 1

Note that I did not commit in session 2.

__Session 1__
SELECT
*
FROM
TEST2; I J

0 ROWS SELECTED __Session 2__
COMMIT; TRANSACTION COMMITTED Session 2 commits.

__Session 1__
Session 1 still has an active transaction in progress due to the previous insert without commit.
SELECT
*
FROM
TEST2; I J

5 1

UPDATE
  TEST1
SET
  (X,Y)=(
    SELECT

      I*1.5,
      J*I*1.5
    FROM
      TEST2);

SELECT
  *
FROM
  TEST1;
       X Y

7.5 7.5

COMMIT; ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; UPDATE
  TEST1
SET
  (X,Y)=(
  SELECT
    I*1.5,
    J*J*1.5
  FROM
    TEST2); SELECT
  *
FROM
  TEST1;          X Y

7.5 1.5

Now, the Transaction Isolation Level has been set to keep the transaction consistent. An update was performed without a COMMIT to start the transaction.

__Session 2__
SELECT
*
FROM
TEST1; X Y

7.5 7.5

UPDATE
  TEST2
SET
  I=I*COS(.45),
  J=J*SIN(.45); SELECT
  *
FROM
  TEST2;           I J

4.5022 0.435

COMMIT; TRANSACTION COMMITTED Session 2 still sees the old values that were changed by session 1, as expected. Session 2 commits so that session 1 can see the values that it changed.

__Session 1__
SELECT
*
FROM
TEST2; I J

5 1

Session 1 still can't see the changes made by session 2, even though session 2 committed.

See Chapter 7 of "Expert Oracle Database Architecture" for a full explanation.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 21 2006 - 08:31:16 CST