Thread: AW: Issue NOTICE for attempt to raise lock level?

> A NOTICE might be useful to users since it would complain about
> deadlock-prone user-level coding practices too, such as
> 
>     begin;
>     select * from foo;    -- grabs read lock
>     lock table foo;        -- grabs exclusive lock

Although this is deadlock prone praxis, there is no mention in any standard that 
this is not allowed or depricated. Thus we are imho not allowed to issue a notice.

Of course my opinion is that you should release the lock after select
if isolation is not RR or serializable. Thus not leading to a deadlock situation.
(I do have strong feelings about this issue)

Andreas

> Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> >> I am working on eliminating the "relation NNN modified 
> while in use"
> >> misfeature by instead grabbing a lock on each relation at first use
> >> in a statement, and holding that lock till end of transaction. 
> 
> > As anticipated, I object :-) 
> 
> Your objection is founded on two misunderstandings.  In the 
> first place, we are *always* inside a transaction when executing
> a query. It may be an implicit one-statement transaction, but it's
> still a transaction.
> In the second place, we already grab locks that we do not release till
> end of xact for all user-level queries.  The problem is that we grab
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Even for select?

> them too late, ie, in the executor.  I'm just planning to move up the
> grab till first use.

BTW, what about indices?

Vadim

"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> BTW, what about indices?

CREATE/DROP INDEX grab exclusive lock on the parent table, so there's no
problem with indexes as long as transactions that use the parent table
hold some kind of lock on that table.  I figure it's OK for the executor
to continue to grab and release locks on indexes on a per-scan basis.
It's just the parent table that we need a continuous lock on.
        regards, tom lane

I said:
> CREATE/DROP INDEX grab exclusive lock on the parent table,

Correction: DROP INDEX grabs exclusive lock, CREATE only grabs a
ShareLock on the parent.  But that's OK since addition of an index
doesn't hurt existing readers.
        regards, tom lane