Re: Wrong FOR UPDATE lock type

Jan Wieck <janwieck(at)Yahoo(dot)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