Thread: AccessExclusiveLock on tuple?
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.045ms
What conditions produce an "AccessExclusiveLock on tuple"? Attempting to lock a tuple when another process has done an
explicitLOCK ACCESS EXCLUSIVE?
--
-- Christophe Pettus
xof@thebuild.com
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.045ms > > What conditions produce an "AccessExclusiveLock on tuple"? Attempting to lock a tuple when another process has done anexplicit 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
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. -- -- Christophe Pettus xof@thebuild.com
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
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,"process8465 still waiting for ShareLock on transaction 1000055 after 1001.203 ms","Process holdingthe 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
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
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
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"?