Обсуждение: ...

Vadim, bright as always wrote:
> ....
>9. ObjExclusiveLock - acquired by DROP/ALTER.
>
>: SELECT doesn't aquire any type of lock except of
>  AccessSharedLock but this is short-term lock and would we
>  like disallow DROP TABLE that was read by some running
>  transaction ?

I would say allow the drop table, of course only if no update or intent update (select for update)
lock is on it. This is how Informix behaves. Otherwise it will become very hard to drop tables altogether.

Andreas

Andreas Zeugswetter wrote:
>
> I would say allow the drop table, of course only if no update or
> intent update (select for update) lock is on it.
> This is how Informix behaves. Otherwise it will become very
> hard to drop tables altogether.

Ok, currently, table can't be dropped if SELECTed by another
running transaction.

Would we like to change this ?!

Comments?

---

Also, I have yet another test for Oracle locking/table
dropping - help me please:

CREATE TABLE test (x integer, y integer);
INSERT INTO test VALUES (1, 1);

1. In session T1 run

   LOCK TABLE test IN EXCLUSIVE MODE;

2. In session T2 run

   UPDATE test SET y = 0 WHERE x = 0;

   -- shouldn't be blocked by T1 if ROW EXCLUSIVE
   -- lock is acquired by T2 only when row found

3. Now again in session T1

   DROP TABLE test;

   -- will be this blocked ?

TIA,
    Vadim

Vadim Mikheev wrote:
>
> Andreas Zeugswetter wrote:
> >
> > I would say allow the drop table, of course only if no update or
> > intent update (select for update) lock is on it.
> > This is how Informix behaves. Otherwise it will become very
> > hard to drop tables altogether.
>
> Ok, currently, table can't be dropped if SELECTed by another
> running transaction.
>
> Would we like to change this ?!

Due to the lack of shared catalog cache I would like to
preserve current behaviour:

System catalogs are scanned by transaction once on first open
of relation and so there is no way for trasnaction to know if
relation was dropped by another backend. If relation file
will not be closed by Virtual file descriptor code (fd.c)
then subsequent operations over relation will be ok
else -> elog(ERROR, "cannot open _relation_") from smgr.c
or even worse if relation will be re-created in the meantime...

We wouldn't like these unpredictable results, yes ?

Vadim

On Sat, Aug 01, 1998 at 12:05:52AM +0800, Vadim Mikheev wrote:
> 1. In session T1 run
>
>    LOCK TABLE test IN EXCLUSIVE MODE;
>
> 2. In session T2 run
>
>    UPDATE test SET y = 0 WHERE x = 0;
>
>    -- shouldn't be blocked by T1 if ROW EXCLUSIVE
>    -- lock is acquired by T2 only when row found

But it indeed is blocked.

> 3. Now again in session T1
>
>    DROP TABLE test;
>
>    -- will be this blocked ?

DROP TABLE test
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

However, after this, the update call is executed. After a commit in T2, test
can be dropped.

Michael
--
Dr. Michael Meskes        meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire!    Use Debian GNU/Linux!

Thanks again, Michael!

> > 1. In session T1 run
> >
> >    LOCK TABLE test IN EXCLUSIVE MODE;
> >
> > 2. In session T2 run
> >
> >    UPDATE test SET y = 0 WHERE x = 0;
> >
> >    -- shouldn't be blocked by T1 if ROW EXCLUSIVE
> >    -- lock is acquired by T2 only when row found
>
> But it indeed is blocked.

Ok, I misunderstood Oracle documentation..
Blocking means that T2 acquires ROW EXCLUSIVE table lock
_before_ statement execution.

>
> > 3. Now again in session T1
> >
> >    DROP TABLE test;
> >
> >    -- will be this blocked ?
>
> DROP TABLE test
>            *
> ERROR at line 1:
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> However, after this, the update call is executed. After a commit in T2, test
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
DROP TABLE commits transaction and releases EXCLUSIVE table lock...

> can be dropped.

No matter was table _really_ modified or not, T2 holds ROW EXCLUSIVE
table lock untill COMMIT/ABORT.

Well...

Vadim