Thread: READ COMMITTED isolevel is implemented ...

READ COMMITTED isolevel is implemented ...

From
Vadim Mikheev
Date:
and this is now the DEFAULT isolevel.

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.
For simple cases like UPDATE t ... WHERE x = 0 or x = 1
it would be possible to just ExecQual for new tuple, but
for joins & subqueries it's required to re-execute entire
plan having this tuple stuck in Index/Seq Scan over result
relation (i.e. - scan over result relation will return
only this new tuple, but all other scans will work as usual). 
To archieve this, copy of plan is created and executed. If
tuple is returned by this child plan then T1 tries to update
new version of tuple and if it's already updated (in the time
of child plan execution) by transaction T3 then T1 will re-execute
child plan for T3' version of tuple, etc.

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
A and trying to mark a tuple of table B, updated by T3, T1
will have to suspend P1 execution and create new child plan
P2 with two tuples stuck in scans of A & B. Execution of P1
will be continued after execution of P2 (P3, P4 ... -:)).
Fortunately, max # of possible child plans is equal to
the number of relations in FOR UPDATE clause: if while
processing first tuple from Pn T1 sees that tuple stuck in
Pm, m < n, was changed, then T1 stops execution of
Pn, ..., Pm-1 and re-start Pm execution for new version
of tuple. Note that n - m may be more than 1 because of
tuples are always marked in the order specified in FOR UPDATE
clause and only after transaction ensured that new tuple
version satisfies plan qual.

Trigger manager is also able to use child plans for
before row update/delete triggers (tuple must be 
marked for update - i.e. locked - before trigger
execution), but this is not tested at all, yet.

Executor never frees child plans explicitely but re-uses
them if needed and there are unused ones. 

Well, MVCC todo list:

-- big items

1. vacuum
2. btree  2.1 still use page locking  2.2 ROOT page may be changed by concurrent insertion but      btinsert doesn't
checkthis
 

-- small ones

3. refint - selects don't block concurrent transactions:            FOR UPDATE must be used in some cases
4. user_lock contrib code: lmgr structures changed

Vadim


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

From
"Hiroshi Inoue"
Date:
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 




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

From
Vadim Mikheev
Date:
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


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

From
"Hiroshi Inoue"
Date:

> -----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
equalssign 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



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

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026
 


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

From
Vadim Mikheev
Date:
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


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

From
Vadim Mikheev
Date:
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


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

From
Hannu Krosing
Date:
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


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

From
Tatsuo Ishii
Date:
> 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


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

From
Vadim Mikheev
Date:
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


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

From
Tatsuo Ishii
Date:
>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


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

From
Vadim Mikheev
Date:
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