Re: odd locking behaviour

On Sun, Jul 21, 2013 at 9:15 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> hello
>
> It can be artefact of RI implementation.
>
> see http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-for-key-share-and-for-no-key-update/
>
> Try to 9.3, please, where RI uses more gently locks

It still behaves this way in 9.4dev.

>>
>> On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube(at)gmail(dot)com> wrote:
>>>
>>>
>>> Thank you for the responses. Is it a bug?

I don't think so. While PostgreSQL of course strives for maximum
concurrency, it makes no guarantee that it uses the weakest
theoretically possible locking in all possible cases. But it is kind
of unfortunate that updating the same row twice causes a lock
escalation when it is not obvious it should do so, because as you
found that makes avoiding deadlocks quite difficult.

I'm rather surprised it doesn't block at the first update of the 2nd
session, rather than waiting for the 2nd update of that session.

Anyway, when the 2nd session re-updates the same row in the same
transaction, it uses a 'multixact' to record this. Doing that
apparently defeats some locking optimization that takes place under
simpler cases.

Sorry, that probably isn't the definitive answer you were hoping for.

Cheers,

Jeff