Thread: Fundamental change of locking behavior in 7.1

Since I see, that Tom has implemented the "keep a AccessShareLock lock until 
transaction end" philisophy I would like to state a protest.

This is a fundamental change in behavior and I would like to see 
a vote on this.

The one example we already know is:

session1                session2
begin work;                begin work;
select * from tenk1 limit 1;                select * from tenk1 limit 1;
lock table tenk1; --now waits (why should it ?)                lock table tenk1; -- NOTICE:  Deadlock detected -->
ABORT

I think this is not acceptable in committed read isolation. The AccessShareLock
needs to be released after each statement finishes.

Thank you
Andreas

> -----Original Message-----
> From: Zeugswetter Andreas SB
> 
> Since I see, that Tom has implemented the "keep a AccessShareLock 
> lock until 
> transaction end" philisophy I would like to state a protest.
> 
> This is a fundamental change in behavior and I would like to see 
> a vote on this.
> 
> The one example we already know is:
> 
> session1                session2
> begin work;                begin work;
> select * from tenk1 limit 1;
>                     select * from tenk1 limit 1;
> lock table tenk1; --now waits (why should it ?)
>                     lock table tenk1; -- 
> NOTICE:  Deadlock detected --> ABORT
>

In PostgreSQL,'lock table' acquires a AccessExclusiveLock by default.
IMHO ExclusiveLock is sufficient for ordinary purpose. It doesn't conflict
with AccessShareLock. Oracle doesn't have AccessExclusive(Share)Lock
and I've been suspicious why users could acquire the lock explicitly.

Comments ?

Regards.
Hiroshi Inoue

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> In PostgreSQL,'lock table' acquires a AccessExclusiveLock by default.
> IMHO ExclusiveLock is sufficient for ordinary purpose.

People who want that kind of lock can get it with LOCK TABLE IN
EXCLUSIVE MODE.  I do not think it's a good idea to change the
default kind of lock acquired by a plain LOCK TABLE, if that's
what you're suggesting ...
        regards, tom lane