READ COMMITTED isolevel is implemented ...

Hello All,

-----Original Message-----
From: owner-pgsql-hackers@postgreSQL.org
[mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Vadim Mikheev
Sent: Saturday, January 30, 1999 2:55 AM
To: hackers@postgreSQL.org
Subject: [HACKERS] READ COMMITTED isolevel is implemented ...

and this is now the DEFAULT isolevel.

It's different from current(v6.4.2).
The way will be provided to upgrade user's current code ?

I run some tests to ensure how it works, but not so much.
Unfortunately, currently it's not possible to add
such tests to regression suit because of they require
concurrent transactions. We could write simple script to
run a few psql-s simultaneously and than just put queries
to them (through pipes) in required order. I have no time
for this now...

Processing updates in READ COMMITTED isolevel is much
complex than in SERIALIZABLE one, because of if transaction T1
notices that tuple to be updated/deleted/selected_for_update
is changed by concurrent transaction T2 then T1 has to check
does new version of tuple satisfy T1 plan qual or not.

How about UPDATE t set x = x + 1 where .... ?

The values of x used for x = x + 1 are at the time when statement
started ?
It seems that this case also requires re-execution.

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp

Hiroshi Inoue wrote:

Subject: [HACKERS] READ COMMITTED isolevel is implemented ...

and this is now the DEFAULT isolevel.

It's different from current(v6.4.2).

First, I think that DEFAULT isolevel must be configure-able.

The way will be provided to upgrade user's current code ?

Even SERIALIZABLE isolevel in MVCC is different from
one in locking systems. There is only one way to don't
change anything in applications - use table level locking.
Should we provide ability to turn MVCC off?

Processing updates in READ COMMITTED isolevel is much
complex than in SERIALIZABLE one, because of if transaction T1
notices that tuple to be updated/deleted/selected_for_update
is changed by concurrent transaction T2 then T1 has to check
does new version of tuple satisfy T1 plan qual or not.

How about UPDATE t set x = x + 1 where .... ?

The values of x used for x = x + 1 are at the time when statement
started ?
It seems that this case also requires re-execution.

x + 1 is in target list of execution plan. And so when child plan
is executed, new value of x is used to evaluate target list
expressions. Executor uses tuple from child plan as new version
of tuple.

Vadim

Handling of SELECT FOR UPDATE OF > 1 relations is ever more
complex. While processing tuples (more than 1 tuple may be
returned by join) from child plan P1 created for tuple of table

I don't think Informix allows FOR UPDATE in a multi-table select.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-----Original Message-----
From: owner-pgsql-hackers@postgreSQL.org
[mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Vadim Mikheev
Sent: Saturday, January 30, 1999 1:41 PM
To: Hiroshi Inoue
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] READ COMMITTED isolevel is implemented ...

Hiroshi Inoue wrote:

Subject: [HACKERS] READ COMMITTED isolevel is implemented ...

and this is now the DEFAULT isolevel.

It's different from current(v6.4.2).

First, I think that DEFAULT isolevel must be configure-able.

The way will be provided to upgrade user's current code ?

Even SERIALIZABLE isolevel in MVCC is different from
one in locking systems. There is only one way to don't
change anything in applications - use table level locking.
Should we provide ability to turn MVCC off?

I think in most cases SEIALIZABLE is sufficient for upgrading.
So it is preferable that we can change default isolation level
easily.
I believe that SET TRANSCTION ISOLATION LEVEL is per
transaction command(i.e it is necessary for every transaction
which is different from default).
Another command to set per connection default is necessary
as Thomas Lockhart wrote about "autocommit".

We can have the default be "set autocommit on" (probably
with an equals sign like our other "set" variables) and we can
have it be a run-time option like DATESTYLE and other settable
parameters. So you can configure your server or your client
environment to always behave the way you prefer.

Processing updates in READ COMMITTED isolevel is much
complex than in SERIALIZABLE one, because of if transaction T1
notices that tuple to be updated/deleted/selected_for_update
is changed by concurrent transaction T2 then T1 has to check
does new version of tuple satisfy T1 plan qual or not.

How about UPDATE t set x = x + 1 where .... ?

The values of x used for x = x + 1 are at the time when statement
started ?
It seems that this case also requires re-execution.

x + 1 is in target list of execution plan. And so when child plan
is executed, new value of x is used to evaluate target list
expressions. Executor uses tuple from child plan as new version
of tuple.

Oracle(Version7) seems to work as you mentioned.
Sorry.

Hiroshi Inoue
Inoue@tpf.co.jp

Hiroshi Inoue wrote:

Subject: [HACKERS] READ COMMITTED isolevel is implemented ...

and this is now the DEFAULT isolevel.

It's different from current(v6.4.2).

First, I think that DEFAULT isolevel must be configure-able.

The way will be provided to upgrade user's current code ?

Even SERIALIZABLE isolevel in MVCC is different from
one in locking systems. There is only one way to don't
change anything in applications - use table level locking.
Should we provide ability to turn MVCC off?

I think in most cases SEIALIZABLE is sufficient for upgrading.
So it is preferable that we can change default isolation level
easily.

Agreed, but I never worked with configure stuff...

I believe that SET TRANSCTION ISOLATION LEVEL is per
transaction command(i.e it is necessary for every transaction
which is different from default).
Another command to set per connection default is necessary
as Thomas Lockhart wrote about "autocommit".

Oracle uses ALTER SESSION command for this.

Processing updates in READ COMMITTED isolevel is much
complex than in SERIALIZABLE one, because of if transaction T1
notices that tuple to be updated/deleted/selected_for_update
is changed by concurrent transaction T2 then T1 has to check
does new version of tuple satisfy T1 plan qual or not.

How about UPDATE t set x = x + 1 where .... ?

The values of x used for x = x + 1 are at the time when statement
started ?
It seems that this case also requires re-execution.

x + 1 is in target list of execution plan. And so when child plan
is executed, new value of x is used to evaluate target list
expressions. Executor uses tuple from child plan as new version
of tuple.

Oracle(Version7) seems to work as you mentioned.
Sorry.

Isn't this the same you told in first message?
And if so - what "sorry" means? -:)

Ok. T1 executes UPDATE t SET x = x + 1 WHERE y = 2 and sees
that row (x = 1, y = 2) is updated by T2 to be (x = 3, y = 2).
What is the result of T1 update? In postgres the result
will be (x = 4, y = 2), not (x = 2, y = 2). Is it ok?

Vadim

Bruce Momjian wrote:

Handling of SELECT FOR UPDATE OF > 1 relations is ever more
complex. While processing tuples (more than 1 tuple may be
returned by join) from child plan P1 created for tuple of table

I don't think Informix allows FOR UPDATE in a multi-table select.

Oracle does. I don't know about SyBase, DB2 etc.
In any case - this is implemented already -:)

Vadim

Vadim Mikheev wrote:

Bruce Momjian wrote:

Handling of SELECT FOR UPDATE OF > 1 relations is ever more
complex. While processing tuples (more than 1 tuple may be
returned by join) from child plan P1 created for tuple of table

I don't think Informix allows FOR UPDATE in a multi-table select.

Oracle does. I don't know about SyBase, DB2 etc.
In any case - this is implemented already -:)

When MS Access came out they made a big fuss about this ability,
claiming that they were the first ones to implement this.

I'm not sure in what category they claimed they were first ;)

-------------------
Hannu

and this is now the DEFAULT isolevel.

But it seems that the standard says SERIALIZABLE is the default
isolation level (or at least the highest isolation level implemented
in the product), doesn't it?

I have looked into Japanese transalated version of:

"A guide to the SQL standard 4th edition" by C.J.Date
"Understanding the new SQL: A complete guide" by J.Melton and A.R.Simon

Anyone can confirm this?
--
Tatsuo Ishii

Tatsuo Ishii wrote:

and this is now the DEFAULT isolevel.

But it seems that the standard says SERIALIZABLE is the default
isolation level (or at least the highest isolation level implemented
in the product), doesn't it?

Yes, it does.

But Oracle, Informix, Sybase all use READ COMMITTED as default.
Please decide youself - it doesn't matter much to me -:)
I would like to see it 1. configure-able; 2. in pg_options;
3. in command line args. I'll do this after beta started,
if no one else before.

Vadim

Hiroshi Inoue wrote:

Recently I thought about UPDATE operations in READ COMMITED
mode a little and found it's very difficult.
I read Oracle's manual but couldn't find the algorithm.

After I read your posting [READ COMMITTED isolevel is implemented ...],
I tested the following [Case-1] in my Oracle database environment(Version
7).
I believed that changes of the values of columns used in a QUERY caused
re-execution.

After I posted my mail,I tested another case(the following [Case -2]) and
found that changes of the values of columns used in a QUERY don't
necessarily cause re-execution.
To tell the truth,I can't understand the result of this case.
IMHO this case requires re-execution after re-setting the time of execution
and the result should be same as [Case-1] .
But Oracle doesn' work as I believe.

create table t (id int4,dt int4,name text);
insert into t values (10,5,'a0');
insert into t values (20,10,'b0');
insert into t values (30,15,'c0');

id |dt |name
----------------------------
10 |5 |a0
20 |10 |b0
30 |15 |c0

session-1 session-2 session-3

[Case-1]
update t set dt=dt+1,
^^^^^^^^
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where dt >7;
^^^^^^^^^^^^^
(blocked)
update t set dt=dt+3,
^^^^^^^^^
id=id+1
where id=10;
UPDATE 1
commit;
COMMIT
commit;
COMMIT
UPDATE 3
^^^^^^^^^^^^

Ops. I'm quite suprized that T2 sees changes made by T3 after
T2' statement started! What would be results if T3 wouldn't
make UPDATE but made INSERT INTO t VALUES (11, 8, 'a1') ?

[result] id |dt |name
---------------------------
11 |10 |a0
20 |12 |b0
30 |18 |c1

If dt=dt+1 ==> dt=dt
^^^^^^^^
then UPDATE 3 ==> UPDATE 2
^^^^^^^^^^^^

[result] id |dt |name
---------------------------
11 |8 |a0
20 |12 |b0
30 |17 |c1

Why T2 doesn't change id=11 row now???
Does Oracle re-execute _entire_ query after being blocked
by concurrent transaction T only if T made changes in columns
used in QUAL?!

Yes! Case-2 confirmes this!

[Case-2]

update t set dt=dt+1,
^^^^^^^^
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where id > 10;
^^^^^^^^^
(blocked)
update t set dt=dt+3,
id=id+1
^^^^^^^^
where id=10;
UPDATE 1
commit;
COMMIT
commit;
COMMIT
UPDATE 2
^^^^^^^^^^^^

[result] id |dt |name
---------------------------
11 |8 |a0
20 |12 |b0
30 |18 |c1

id is not changed by T1 and so T2 doesn't re-execute query
after T1 committed and doesn't see changes made by T3!
T2 just re-evaluates target list.

Ok. Postgres always re-executes query after being blocked,
but makes this for single row only and so all other changes
made after query started are not visible. I would say that
we are more consistent than Oracle -:))

Oracle says:
---
Oracle always enforces statement-level read consistency.
This guarantees that the data returned by a single query
is consistent with respect to the time that the query began.
Therefore, a query never sees dirty data nor any of the changes
made by transactions that commit during query execution. As query
execution proceeds, only data committed before the query began is
visible to the query. The query does not see changes committed after
statement execution begins.
---

Your tests show that Oracle breaks this statement if re-evaluation
of query' qual is required!

Just wondering what would be the results of these test in
Informix or Sybase?
Could someone run them?

Vadim

Hello all,

-----Original Message-----
From: root@sunpine.krs.ru [mailto:root@sunpine.krs.ru]On Behalf Of Vadim
Mikheev
Sent: Monday, February 01, 1999 1:54 PM
To: Hiroshi Inoue
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] READ COMMITTED isolevel is implemented ...

Hiroshi Inoue wrote:

[snip]

create table t (id int4,dt int4,name text);
insert into t values (10,5,'a0');
insert into t values (20,10,'b0');
insert into t values (30,15,'c0');

id |dt |name
----------------------------
10 |5 |a0
20 |10 |b0
30 |15 |c0

session-1 session-2 session-3

[Case-1]
update t set dt=dt+1,
^^^^^^^^
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where dt >7;
^^^^^^^^^^^^^
(blocked)
update t set dt=dt+3,
^^^^^^^^^
id=id+1
where id=10;
UPDATE 1
commit;
COMMIT
commit;
COMMIT
UPDATE 3
^^^^^^^^^^^^

Ops. I'm quite suprized that T2 sees changes made by T3 after
T2' statement started! What would be results if T3 wouldn't
make UPDATE but made INSERT INTO t VALUES (11, 8, 'a1') ?

The result was

UPDATE 3

id |dt |name
----------------------------
10 |5 |a0
20 |12 |b0
30 |18 |c1
11 |10 |a1

[result] id |dt |name
---------------------------
11 |10 |a0
20 |12 |b0
30 |18 |c1

If dt=dt+1 ==> dt=dt
^^^^^^^^
then UPDATE 3 ==> UPDATE 2
^^^^^^^^^^^^

[result] id |dt |name
---------------------------
11 |8 |a0
20 |12 |b0
30 |17 |c1

Why T2 doesn't change id=11 row now???
Does Oracle re-execute _entire_ query after being blocked
by concurrent transaction T only if T made changes in columns
used in QUAL?!

Yes! Case-2 confirmes this!

[Case-2]

update t set dt=dt+1,
^^^^^^^^
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where id > 10;
^^^^^^^^^
(blocked)
update t set dt=dt+3,
id=id+1
^^^^^^^^
where id=10;
UPDATE 1
commit;
COMMIT
commit;
COMMIT
UPDATE 2
^^^^^^^^^^^^

[result] id |dt |name
---------------------------
11 |8 |a0
20 |12 |b0
30 |18 |c1

id is not changed by T1 and so T2 doesn't re-execute query
after T1 committed and doesn't see changes made by T3!
T2 just re-evaluates target list.

Ok. Postgres always re-executes query after being blocked,
but makes this for single row only and so all other changes
made after query started are not visible. I would say that
we are more consistent than Oracle -:))

Oracle says:
---
Oracle always enforces statement-level read consistency.
This guarantees that the data returned by a single query
is consistent with respect to the time that the query began.
Therefore, a query never sees dirty data nor any of the changes
made by transactions that commit during query execution. As query
execution proceeds, only data committed before the query began is
visible to the query. The query does not see changes committed after
statement execution begins.
---

Your tests show that Oracle breaks this statement if re-evaluation
of query' qual is required!

I don't think so.
It seems that Oracle changes the time that the query began and
re-executes the query (or processes something like re-execution
more effective than re-execution).

So [ Case-1 ] is reasonable for me.

In case of dt=dt+1,the time that the query of T2 began was changed
to the time when T1 was committed and in case of dt=dt,it was not
changed from the time when the query command was issued.
I think that both cases hold column-level read consistency.

But [ Case-2 ] is a question for me.

When did the query of T2 begin ?
It seems that only the values of old target dt ( ... set dt=dt+2 ...) are
at the time when T1 was committed. ^^^

Just wondering what would be the results of these test in
Informix or Sybase?
Could someone run them?

Anyway the version of Oracle used for my test is very old.
The result may be different in newer version.
Cound someone run them in Oracle8 ?

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp

Hiroshi Inoue wrote:

Oracle says:
---
Oracle always enforces statement-level read consistency.
This guarantees that the data returned by a single query
is consistent with respect to the time that the query began.
Therefore, a query never sees dirty data nor any of the changes
made by transactions that commit during query execution. As query
execution proceeds, only data committed before the query began is
visible to the query. The query does not see changes committed after
statement execution begins.
---

Your tests show that Oracle breaks this statement if re-evaluation
of query' qual is required!

I don't think so.
It seems that Oracle changes the time that the query began and

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
What's the reason to do this?

re-executes the query (or processes something like re-execution
more effective than re-execution).

So [ Case-1 ] is reasonable for me.

Why?
If T2 wouldn't be blocked by T1 then T2 wouldn't see T3 changes
even if T3 would be committed "just after" T2 started - before
T2 read id=10 row.

In case of dt=dt+1,the time that the query of T2 began was changed
to the time when T1 was committed and in case of dt=dt,it was not
changed from the time when the query command was issued.
I think that both cases hold column-level read consistency.
But [ Case-2 ] is a question for me.

When did the query of T2 begin ?
It seems that only the values of old target dt ( ... set dt=dt+2 ...) are
at the time when T1 was committed. ^^^

And this shows that Oracle doesn't re-execute query at all when qual
columns were not changed - just because of this is not required
from any point of view. Oracle just gets new version of row
and re-evaluates target list - to performe update over new version,
not old one.

Ok. Please try to run this modified test:

create table t (id int4,dt int4,name text);
insert into t values (10,5,'a0');
insert into t values (20,10,'b0');
insert into t values (30,15,'c0');

create table updatable (id int4);
insert into updatable select id from t;

[Case-2]

update t set dt=dt+1,
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where id > 10 and
id in (select * from updatable);
(blocked)

delete from updatable
where id = 30;
DELETE 1
COMMIT;
END
COMMIT;
END
UPDATE 2
(actually, I got UPDATE 1 - due to bug in subqueries:
subplan' snapshot wasn't initialized, fixed, patch attached).
select * from t;

id|dt|name
--+--+----
10| 5|a0
20|12|b0
30|18|c1
^^^^^^^^
updated... What's the result in Oracle?

Vadim

Tatsuo Ishii wrote:

and this is now the DEFAULT isolevel.

But it seems that the standard says SERIALIZABLE is the default
isolation level (or at least the highest isolation level implemented
in the product), doesn't it?

Yes, it does.

Then we should go for the standard way, I think.

But Oracle, Informix, Sybase all use READ COMMITTED as default.
Please decide youself - it doesn't matter much to me -:)
I would like to see it 1. configure-able; 2. in pg_options;
3. in command line args. I'll do this after beta started,
if no one else before.

BTW, what is the advantage of READ COMMMITTED in PostgreSQL? I thought
the SERIALIZABLE should give us enough concurrency since we are using
MVCC. Could you give me some examples?
--
Tatsuo Ishii

Hello all,

Oracle says:
---
Oracle always enforces statement-level read consistency.
This guarantees that the data returned by a single query
is consistent with respect to the time that the query began.
Therefore, a query never sees dirty data nor any of the changes
made by transactions that commit during query execution. As query
execution proceeds, only data committed before the query began is
visible to the query. The query does not see changes committed after
statement execution begins.
---

Your tests show that Oracle breaks this statement if re-evaluation
of query' qual is required!

I don't think so.
It seems that Oracle changes the time that the query began and

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
What's the reason to do this?

In case of SELECT only,read consistency is obvious.
But it's ambiguous and difficult for me in case of update or select ..
for update.
There will be many ways of thinking.
Mine is only one of them.

My way of thinking is

The values of columns of tuples to be updated/deleted/sele
cted_for_update which appears in the query should be
latest ones and so they may be different from the values
which read consistency provides.
The time that the query began should be changed to
the time that no such differences can be seen.

re-executes the query (or processes something like re-execution
more effective than re-execution).

So [ Case-1 ] is reasonable for me.

Why?
If T2 wouldn't be blocked by T1 then T2 wouldn't see T3 changes
even if T3 would be committed "just after" T2 started - before
T2 read id=10 row.

[My thinkging]
If T2 wouldn't be blocked by T1,T2 woundn't detect the changes
of dt,so we don't have to change the time that the query began.
But if T2 detects the change of dt which is used in the query(
and the tuple is to be updated),the time that the query began
should be changed.

In case of dt=dt+1,the time that the query of T2 began was changed
to the time when T1 was committed and in case of dt=dt,it was not
changed from the time when the query command was issued.
I think that both cases hold column-level read consistency.
But [ Case-2 ] is a question for me.

When did the query of T2 begin ?
It seems that only the values of old target dt ( ... set

dt=dt+2 ...) are

at the time when T1 was committed. ^^^

And this shows that Oracle doesn't re-execute query at all when qual
columns were not changed - just because of this is not required
from any point of view. Oracle just gets new version of row
and re-evaluates target list - to performe update over new version,
not old one.

My way of thinking can't explain this case.
Oracle seems to ignore columns in the targetlist as you say.

But how about the following case ?
After chainging the query of T2 in [ Case-2 ] to

select dt from t
where id > 10 for update;

the result was
dt
----
8
10
16
(3 rows)

This result is reasonable for me.
Where is the difference ?

Ok. Please try to run this modified test:

create table t (id int4,dt int4,name text);
insert into t values (10,5,'a0');
insert into t values (20,10,'b0');
insert into t values (30,15,'c0');

create table updatable (id int4);
insert into updatable select id from t;

[Case-2]

update t set dt=dt+1,
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where id > 10 and
id in (select * from updatable);
(blocked)

delete from updatable
where id = 30;
DELETE 1
COMMIT;
END
COMMIT;
END
UPDATE 2
(actually, I got UPDATE 1 - due to bug in subqueries:
subplan' snapshot wasn't initialized, fixed, patch attached).
select * from t;

id|dt|name
--+--+----
10| 5|a0
20|12|b0
30|18|c1
^^^^^^^^
updated... What's the result in Oracle?

The result is same as yours.

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp

Tatsuo Ishii wrote:

BTW, what is the advantage of READ COMMMITTED in PostgreSQL? I thought
the SERIALIZABLE should give us enough concurrency since we are using
MVCC. Could you give me some examples?

Yes, but UPDATE/DELETE in SERIALIZABLE mode will cause
elog(ERROR, "Can't serialize access due to concurrent update");
in the case of the-same row update.
Oracle sets implicit savepoint before executing a statement.
In Postgres - entire transaction will be aborted...

I have some ideas about savepoints... may be in 6.6 or 6.7...

Vadim