From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Baldwin <steve.baldwin@gmail.com>
cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Lock timeout in commit
In-reply-to: <CAKE1AiY17RgcKCFba1N6Sz6SjHqSqvq+cfKWBfyKFEjT-L+qkg@mail.gmail.com>
References: <CAKE1AiY17RgcKCFba1N6Sz6SjHqSqvq+cfKWBfyKFEjT-L+qkg@mail.gmail.com>
Comments: In-reply-to Steve Baldwin <steve.baldwin@gmail.com>
	message dated "Thu, 10 Jul 2025 22:01:07 +1000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1529238.1752161326.1@sss.pgh.pa.us>
Date: Thu, 10 Jul 2025 11:28:46 -0400
Message-ID: <1529239.1752161326@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1529239.1752161326%40sss.pgh.pa.us>
Precedence: bulk

Steve Baldwin <steve.baldwin@gmail.com> writes:
> I'm occasionally seeing a lock timeout in a commit statement. For example:

> 2025-07-10 08:56:07.225 UTC,"b2bc_api","b2bcreditonline",23592,"
> 10.124.230.241:60648",686f8022.5c28,55,"COMMIT",2025-07-10 08:56:02
> UTC,3984/10729,676737574,ERROR,55P03,"canceling statement due to lock
> timeout",,,,,,"COMMIT",,,"098c62f7-bb76-4607-88da-120c6edee423","client
> backend",,-2835399305386018931

> Am I right in thinking this is likely due to a deferred foreign key
> constraint?

Plausible guess perhaps, although FKs don't normally need strong
locks.

> I couldn't see any obvious clue in the log. Is there somewhere
> else I can look?

I think all you could do is monitor the pg_locks view and hope to
catch the process in "waiting" state before it fails.

It occurs to me to wonder though if we couldn't provide more
context in the error about what lock is being waited for.

			regards, tom lane