Обсуждение: AW: AW: relation ### modified while in use

> > > What I'm proposing is that once an xact has touched a
> > > table, other xacts should not be able to apply schema updates to that
> > > table until the first xact commits.
> >
> > No, this would mean too many locks, and would leave the dba with hardly a
> > chance to alter a table.
> >
> 
> Are there many applications which have many SELECT statements(without
> FOR UPDATE) in one tx ?

Why not ?

> As for locks,weak locks doesn't pass intensive locks.  Dba 
> seems to be able to alter a table at any time.

Sorry, I don't understand this sentence. Tom suggested placing a shared lock on 
any table that is accessed until end of tx. Noone can alter table until all users have
closed their txns and not accessed tables again. Remember that this would include
creating an index ...

Andreas

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> As for locks,weak locks doesn't pass intensive locks.  Dba 
>> seems to be able to alter a table at any time.

> Sorry, I don't understand this sentence. Tom suggested placing a
> shared lock on any table that is accessed until end of tx. Noone can
> alter table until all users have closed their txns and not accessed
> tables again.

Until existing xacts using that table have closed, yes.  But I believe
the lock manager has some precedence rules that will allow the pending
request for AccessExclusiveLock to take precedence over new requests
for lesser locks.  So you're only held off for a long time if you have
long-running xacts that use the target table.

I consider that behavior *far* safer than allowing schema changes to
be seen mid-transaction.  Consider the following example:
Session 1            Session 2
begin;
INSERT INTO foo ...;
                ALTER foo ADD constraint;
INSERT INTO foo ...;
end;

Which, if any, of session 1's insertions will be subject to the
constraint?  What are the odds that the dba will like the result?

With my proposal, session 2's ALTER would wait for session 1 to commit,
and then the ALTER's own scan to verify the constraint will check all
the rows added by session 1.

Under your proposal, I think the rows inserted at the beginning of
session 1's xact would be committed without having been checked.
        regards, tom lane

Philip Warner <pjw@rhyme.com.au> writes:
> In this case, wouldn't the answer depend on the isolation level of session
> 1? For serializable TX, then constraint would not apply; 'read committed'
> would mean the constraint was visible on the second insert and at the commit.

The important issue here is that all schema changes have to be read
on a read-committed basis, even if your transaction is otherwise
serializable.  Consider for example the possibility that the schema
change you're ignoring consists of a DROP INDEX or some such --- you'll
fail if you proceed as though the index is still there.  This is the
point Vadim was making a few days ago (but I didn't understand at the
time).

I believe we can work out a consistent set of behavior such that user
data accesses (SELECT/UPDATE/etc) follow MVCC rules but system accesses
to schema data always follow read-committed semantics.  One of the
components of this has to be an agreement on how to handle locking.
AFAICS, we have to adopt hold-some-kind-of-lock-till-end-of-xact,
or we will have consistency problems between the user and system
views of the world.
        regards, tom lane

> > As for locks,weak locks doesn't pass intensive locks.  Dba
> > seems to be able to alter a table at any time.
>
> Sorry, I don't understand this sentence. Tom suggested placing a shared
lock on
> any table that is accessed until end of tx. Noone can alter table until
all users have
> closed their txns and not accessed tables again.

More of that - while one xaction will wait to alter a table no new xaction
will be
allowed to access this table too.

> Remember that this would include creating an index ...

I don't think so. Index creation requires
1. share lock on schema
2. share lock on data

Vadim

At 10:10 23/10/00 -0400, Tom Lane wrote:
>
>I consider that behavior *far* safer than allowing schema changes to
>be seen mid-transaction.  Consider the following example:
>
>    Session 1            Session 2
>
>    begin;
>
>    INSERT INTO foo ...;
>
>                    ALTER foo ADD constraint;
>
>    INSERT INTO foo ...;
>
>    end;
>
>Which, if any, of session 1's insertions will be subject to the
>constraint?  What are the odds that the dba will like the result?
>

In this case, wouldn't the answer depend on the isolation level of session
1? For serializable TX, then constraint would not apply; 'read committed'
would mean the constraint was visible on the second insert and at the commit.

I would err on the side of insisting all metadata changes occur in
serializable transactions to make life a little easier.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Zeugswetter Andreas SB wrote:

> > > > What I'm proposing is that once an xact has touched a
> > > > table, other xacts should not be able to apply schema updates to that
> > > > table until the first xact commits.
> > >
> > > No, this would mean too many locks, and would leave the dba with hardly a
> > > chance to alter a table.
> > >
> >
> > Are there many applications which have many SELECT statements(without
> > FOR UPDATE) in one tx ?
>
> Why not ?
>

It seems to me that multiple SELECT statements in a tx has little
meaning unless the tx is executed in SERIALIZABLE isolation level.

>
> > As for locks,weak locks doesn't pass intensive locks.  Dba
> > seems to be able to alter a table at any time.
>
> Sorry, I don't understand this sentence. Tom suggested placing a shared lock on
> any table that is accessed until end of tx. Noone can alter table until all users have
> closed their txns and not accessed tables again. Remember that this would include
> creating an index ...
>

What I meant is the following though I may be misunderstanding your point.

Session-1.# begin;# declare myc cursor for select * from t1;

Session-2. # begin; # lock table t1; [blocked]

Session-3. # begin; # select * from t1; [blocked]

Session-1. # abort;

Then
Session-2. LOCK TABLE #

but
Session-3. [still blocked]

Regards.
Hiroshi Inoue