public inbox for [email protected]help / color / mirror / Atom feed
Lock timeout in commit 3+ messages / 2 participants [nested] [flat]
* Lock timeout in commit @ 2025-07-10 12:01 Steve Baldwin <[email protected]> 2025-07-10 15:28 ` Re: Lock timeout in commit Tom Lane <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Steve Baldwin @ 2025-07-10 12:01 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Lock timeout in commit 2025-07-10 12:01 Lock timeout in commit Steve Baldwin <[email protected]> @ 2025-07-10 15:28 ` Tom Lane <[email protected]> 2025-07-10 20:32 ` Re: Lock timeout in commit Steve Baldwin <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2025-07-10 15:28 UTC (permalink / raw) To: Steve Baldwin <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> Steve Baldwin <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Lock timeout in commit 2025-07-10 12:01 Lock timeout in commit Steve Baldwin <[email protected]> 2025-07-10 15:28 ` Re: Lock timeout in commit Tom Lane <[email protected]> @ 2025-07-10 20:32 ` Steve Baldwin <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Steve Baldwin @ 2025-07-10 20:32 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> On Fri, 11 Jul 2025 at 01:28, Tom Lane <[email protected]> wrote: > > 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. > > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-07-10 20:32 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-07-10 12:01 Lock timeout in commit Steve Baldwin <[email protected]> 2025-07-10 15:28 ` Tom Lane <[email protected]> 2025-07-10 20:32 ` Steve Baldwin <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox