AccessExclusiveLock on tuple?

On Wed, 2 Dec 2015 09:01:37 -0800
Christophe Pettus <xof@thebuild.com> wrote:

On 9.4, I've encountered a locking message I've not seen before:

process 5293 still waiting for AccessExclusiveLock on tuple (88636,15) of relation 18238 of database 16415 after 5000.045 ms

What conditions produce an "AccessExclusiveLock on tuple"? Attempting to lock a tuple when another process has done an explicit LOCK ACCESS EXCLUSIVE?

No. See the section on row level locks here:
http://www.postgresql.org/docs/9.4/static/explicit-locking.html

Essentially, any data modification could take an exclusive lock on the row(s)
that it's going to modify. Generally, this will be an UPDATE statement,
although the same thing happens when you do SELECT ... FOR UPDATE.

The message you're seeing simply means that one process has been waiting for
a long time for the lock to release (5 seconds in this case). Deadlocks are
automatically handled, so this is not a deadlock. Although if the process
holding the lock does not commit the transaction, the waiting process will
wait indefinitely.

If this is happening infrequently, it's probably of no concern. If it's
happening frequently, you'll want to investigate what process is holding
the locks for so long and see what can be done about it.

--
Bill Moran

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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.

--
-- Christophe Pettus
xof@thebuild.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Dec 2, 2015, at 9:50 AM, Bill Moran <wmoran@potentialtech.com> wrote:

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

Well, yes and no :) :

2015-12-02 10:07:40.281 PST,"xof","xof",8465,"[local]",565f3365.2111,4,"UPDATE waiting",2015-12-02 10:07:33 PST,3/12,1000056,LOG,00000,"process 8465 still waiting for ShareLock on transaction 1000055 after 1001.203 ms","Process holding the lock: 8428. Wait queue: 8465.",,,,"while updating tuple (0,1) in relation ""x""","update x set a='a';",,,"psql"

Note that it's waiting for a ShareLock, not an AccessExclusiveLock, thus my question.

Just to clarify, my very specific question is about "AccessExclusiveLock".
--
-- Christophe Pettus
xof@thebuild.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Christophe Pettus <xof@thebuild.com> writes:

Note that it's waiting for a ShareLock, not an AccessExclusiveLock, thus my question.
Just to clarify, my very specific question is about "AccessExclusiveLock".

I believe it'll depend on which PG version you're testing. Alvaro whacked
that stuff around in connection with FOR SHARE LOCK and related features.

The short answer is that heavyweight tuple locks can be taken internally
by UPDATE, DELETE, and other row-level operations, and the specifics of
which type of lock any given action takes are implementation details that
can change from time to time.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Dec 2, 2015, at 10:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The short answer is that heavyweight tuple locks can be taken internally
by UPDATE, DELETE, and other row-level operations, and the specifics of
which type of lock any given action takes are implementation details that
can change from time to time.

Great, thank you!

--
-- Christophe Pettus
xof@thebuild.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, 2015-12-02 at 10:47 -0800, Christophe Pettus wrote:

On Dec 2, 2015, at 10:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The short answer is that heavyweight tuple locks can be taken
internally
by UPDATE, DELETE, and other row-level operations, and the
specifics of
which type of lock any given action takes are implementation
details that
can change from time to time.

Great, thank you!

--
-- Christophe Pettus
   xof@thebuild.com

Don't know if this helps at all. On the hot standby doco.

http://www.postgresql.org/docs/9.4/static/hot-standby.html

About two-thirds of the way down it refers to the pg_locks table.

Are you using "hot standby"?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general