Anthony Presley <anthony@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
