Re: AccessExclusiveLock on tuple?

On Wed, 2 Dec 2015 09:31:44 -0800
Christophe Pettus <xof@thebuild.com> wrote:
>
> On Dec 2, 2015, at 9:25 AM, Bill Moran <wmoran@potentialtech.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
messageimplies there is such a thing as an AccessExclusiveLock on a tuple, which is new to me.  I wasn't able to
producethis message experimentally doing various combinations of UPDATE statements and SELECT FOR UPDATEs, or even with
explicitLOCK 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