Subject: Lock timeout in commit

From: Steve Baldwin <[email-masked]>
Date: Thu, 10 Jul 2025 22:01:07 +1000

Hi all,

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? I couldn't see any obvious clue in the log. Is there somewhere
else I can look?

In case it matters, the server is running version 17.4.

Thanks,

Steve

---

From: Tom Lane <[email-masked]>
Date: Thu, 10 Jul 2025 11:28:46 -0400

> 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

---

From: Steve Baldwin <[email-masked]>
Date: Fri, 11 Jul 2025 06:32:45 +1000

On Fri, 11 Jul 2025 at 01:28, Tom Lane <[email-masked]> wrote:

> Thanks Tom !!

The application is an API server so we intentionally set the lock timeout
to a very short interval (5 ms). Having locking context would be great.

Other than deferred FK constraints, what other locking actions are deferred
to commit time?

Cheers,

Steve