Greg, That's extremely helpful, thank you.
On Mon, 9 Mar 2026 at 13:07, Greg Sabino Mullane <[email protected]> wrote: > On Mon, Mar 9, 2026 at 5:55 AM Shaheed Haque <[email protected]> > wrote: > >> I'm not sure I understand. The two queries are referencing separate, >> single rows in the child table (primary keys payroll_endpoint.id = 1 and >> 2), so where does the multi-row bit come in? Is it because the two parent >> tables are also being locked, in possibly different orders? >> > > This is unrelated to parent tables. What is not shown is the previous > locks. Process A locks id 1. Process B locks id 2, then attempts to lock id > 1. Process A attempts to lock id 2. Hence, a deadlock, even if all four > lock attempts are not shown in the log. > > I'm not seeing "Tom's suggestion". Is there a way to specify that the >> parent tables need not be locked? Perhaps by omitting them from the query? >> > > alter system set log_statement = 'all'; > select pg_reload_conf(); > > ## Run your program and get the deadlock error > > alter system reset log_statement; > select pg_reload_conf(); > > Now check your logs, find the PIDs involved in the deadlock, and trace > what actions they did before the deadlock occurred. > > Here's a real example, showing what the Postgres logs will look like: > > -- Process A: > create table t (id int); > insert into t values (1),(2); > begin; > select * from t where id = 1 for update; > > -- Process B: > begin; > select * from t where id = 2 for update; > select * from t where id = 1 for update; -- Hangs, waiting for process A > to finish > > -- Process A: > select * from t where id = 2 for update; > > DEADLOCK! > > Logs: > > 2026-03-09 09:00:23.885 EDT [242171] LOG: statement: alter system set > log_statement = 'all'; > 2026-03-09 09:00:25.804 EDT [242136] LOG: received SIGHUP, reloading > configuration files > 2026-03-09 09:00:25.805 EDT [242136] LOG: parameter "log_statement" > changed to "all" > 2026-03-09 09:00:31.910 EDT [242171] LOG: statement: drop table if exists > t; > 2026-03-09 09:00:36.649 EDT [242171] LOG: statement: create table t(id > int); > 2026-03-09 09:00:39.522 EDT [242171] LOG: statement: insert into t values > (1),(2); > 2026-03-09 09:00:42.121 EDT [242171] LOG: statement: begin; > 2026-03-09 09:00:50.788 EDT [242171] LOG: statement: select * from t > where id=1 for update; > 2026-03-09 09:00:59.755 EDT [242176] LOG: statement: begin; > 2026-03-09 09:01:05.509 EDT [242176] LOG: statement: select * from t > where id=2 for update; > 2026-03-09 09:01:08.397 EDT [242176] LOG: statement: select * from t > where id=1 for update; > 2026-03-09 09:01:14.278 EDT [242171] LOG: statement: select * from t > where id=2 for update; > 2026-03-09 09:01:15.279 EDT [242171] ERROR: deadlock detected > 2026-03-09 09:01:15.279 EDT [242171] DETAIL: Process 242171 waits for > ShareLock on transaction 15122348; blocked by process 242176. > Process 242176 waits for ShareLock on transaction 15122347; > blocked by process 242171. > Process 242171: select * from t where id=2 for update; > Process 242176: select * from t where id=1 for update; > 2026-03-09 09:01:15.279 EDT [242171] HINT: See server log for query > details. > 2026-03-09 09:01:15.279 EDT [242171] CONTEXT: while locking tuple (0,2) > in relation "t" > 2026-03-09 09:01:15.279 EDT [242171] STATEMENT: select * from t where > id=2 for update; > > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > >
