Re: AccessExclusiveLock on tuple?

On Wed, 2 Dec 2015 09:31:44 -0800
Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>
> On Dec 2, 2015, at 9:25 AM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
>
> > No. See the section on row level locks here:
> > http://www.postgresql.org/docs/9.4/static/explicit-locking.html
>
> That wasn't quite my question. I'm familiar with the row-level locking and the locking messages in general, but this message implies there is such a thing as an AccessExclusiveLock on a tuple, which is new to me. I wasn't able to produce this message experimentally doing various combinations of UPDATE statements and SELECT FOR UPDATEs, or even with explicit LOCK ACCESS EXCLUSIVE MODE, thus the question.

First off, that documentation page _does_ answer your question.

Secondly, there is a config setting: log_lock_waits, which is
disabled by default. The message won't appear if that is off, so
if you're testing on a different install than where the incident
happened, that could be part of the problem.

Finally, the following sequence triggers the message:

create table test1 (data int);
insert into test1 values (1);

Connection 1:
begin;
select * from test1 where data = 1 for update;

Connection 2:
select * from test1 where data = 1 for update;

Then wait for a little while and the message will be logged.

--
Bill Moran