>     I'm  about  99.666667% sure that the lock type choosen in the
>     FOR UPDATE case (line  511  of  parse_relation.c)  should  be
>     RowExclusiveLock  instead  of  RowShareLock.   Actually I get
>     "Deadlock risk" debug messages when selecting FOR UPDATE  and
>     then really UPDATE.

http://www.postgresql.org/users-lounge/docs/6.5/user/x3116.htm

RowShareLock
Acquired by SELECT FOR UPDATE and LOCK TABLE for IN ROW SHARE MODE
statements. 

Conflicts with ExclusiveLock and AccessExclusiveLock modes. 

Vadim

Mon, 4 Dec 2000 09:51:33 -0800 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Wrong FOR UPDATE lock type
Mikheev, Vadim wrote:
> >     I'm  about  99.666667% sure that the lock type choosen in the
> >     FOR UPDATE case (line  511  of  parse_relation.c)  should  be
> >     RowExclusiveLock  instead  of  RowShareLock.   Actually I get
> >     "Deadlock risk" debug messages when selecting FOR UPDATE  and
> >     then really UPDATE.
>
> http://www.postgresql.org/users-lounge/docs/6.5/user/x3116.htm
>
> RowShareLock
> Acquired by SELECT FOR UPDATE and LOCK TABLE for IN ROW SHARE MODE
> statements.
>
> Conflicts with ExclusiveLock and AccessExclusiveLock modes.

    Tom,

    IIRC  the  "Deadlock risk" debug message is from you. I think
    it must get a little smarter. IMHO an application that want's
    to  UPDATE  something  in  a  transaction but must SELECT the
    row(s) first to do it's own calculation on them,  should  use
    SELECT FOR UPDATE. Is that debug output really appropriate in
    this case (it raises from  RowShareLock  to  RowExclusiveLock
    because  of  the  UPDATE  of the previous FOR UPDATE selected
    row)?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Mon, 4 Dec 2000 14:18:25 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Wrong FOR UPDATE lock type
Jan Wieck <janwieck@Yahoo.com> writes:
>     Tom,
>     IIRC  the  "Deadlock risk" debug message is from you. I think
>     it must get a little smarter. IMHO an application that want's
>     to  UPDATE  something  in  a  transaction but must SELECT the
>     row(s) first to do it's own calculation on them,  should  use
>     SELECT FOR UPDATE. Is that debug output really appropriate in
>     this case (it raises from  RowShareLock  to  RowExclusiveLock
>     because  of  the  UPDATE  of the previous FOR UPDATE selected
>     row)?

Well, there is a theoretical chance of deadlock --- not against other
transactions doing the same thing, since RowShareLock and
RowExclusiveLock don't conflict, but you could construct deadlock
scenarios involving other transactions that grab ShareLock or
ShareRowExclusiveLock.  So I don't think it's appropriate for the
"deadlock risk" check to ignore RowShareLock->RowExclusiveLock
upgrades.

But I'm not sure the check should be enabled in production releases
anyway.  I just put it in as a quick and dirty debug check.  Perhaps
it should be under an #ifdef that's not enabled by default.

			regards, tom lane

Mon, 04 Dec 2000 16:51:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Wrong FOR UPDATE lock type 