BLERe: AW: AW: relation ### modified while in use

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