Anthony Presley <anthony@resolution.com> writes:
> Ok, I've written a script to find some of this information when an
> <IDLE> in transaction has been hanging out too long, and then run some
> SQL commands. Since there's a lot there, I've added it to PasteBin:
> http://pastebin.com/TpfKd9Ya
It would help if you'd shown the actual queries you're using, because
these outputs seem to be just a subset of what's going on --- in
particular, your pg_locks output doesn't include *any* ungranted locks,
so it's clearly not telling us what we need to know.
The only thing I can see here that looks suspicious is that process
30637 (the idle one) has a RowShareLock on the employee table, which
implies that it has done a SELECT FOR UPDATE or SELECT FOR SHARE on that
table. What is most likely happening is that it has got a row-level
lock as a result of that on some row that the other process's UPDATE is
trying to change. Row-level blocking shows up only rather indirectly in
the pg_locks table --- typically as a block on a transaction ID --- so
I think the reason we can't see anything there is that you're omitting the
entries that would tell us about it.
> forecast_timeblock has a foreign key to the employee table, ie:
> Foreign-key constraints:
> "fk80bcf09c965efae7" FOREIGN KEY (employee_id) REFERENCES employee(id)
> Any idea what gives? I don't understand locks well enough to see what's
> going on, but why would inserting into forecast_timeblock cause it to
> block an insert into the employee table (which has no relation back to
> forecast_timeblock).
Well, an insert into forecast_timeblock would result in taking a SELECT
FOR SHARE lock on the referenced employee row. (This is needed to make
sure nobody else deletes the referenced row before the insert commits.
Without it, there'd be a race condition that would allow the FK
constraint integrity to be violated.)
Is it likely that 8982 is trying to update the same employee row that
30637 previously inserted a reference to? If so, that's the cause of
the blockage.
The real bottom line here, of course, is that sitting around with an
uncommitted transaction is bad news for concurrency. You need to fix
the application-side logic to not do that.
regards, tom lane