Thread: Who is locking me?
Hi,
A process hangs forever.
When using this query...
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s where pg_stat_get_backend_activity(s.backendid) not like '<insuff%'
and pg_stat_get_backend_activity(s.backendid) not like '<IDLE>';
I get...
procpid | current_query
---------+--------------------------------------------------------------
---------+-------
26702 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
: pg_stat_get_backend_activity(s.backendid) AS current_query
: FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s
: where pg_stat_get_backend_activity(s.backendid) not like '<insuff%'
: and pg_stat_get_backend_activity(s.backendid) not like '<IDLE>';
21282 | update cmr_lastno set lastisn=lastisn+1 where tablename='DANNY';
25936 | <IDLE> in transaction <== This is the locker
And the question:
- What exactly is the locker doing?
- Can I retrieve the Client PID somewhere (then by process name I will dive into the code).
Thanks a lot
Danny Abraham
BMC Software
CTM&D Business Unit
972-52-4286-513
In response to "Abraham, Danny" <danny_abraham@bmc.com>: > Hi, > > > > A process hangs forever. > > When using this query... > > > > SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, > > pg_stat_get_backend_activity(s.backendid) AS current_query > > FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s where pg_stat_get_backend_activity(s.backendid) not like'<insuff%' > > and pg_stat_get_backend_activity(s.backendid) not like '<IDLE>'; > > > > > > I get... > > > > > > procpid | current_query > > ---------+-------------------------------------------------------------- > > ---------+------- > > 26702 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, > > : pg_stat_get_backend_activity(s.backendid) AS current_query > > : FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s > > : where pg_stat_get_backend_activity(s.backendid) not like '<insuff%' > > : and pg_stat_get_backend_activity(s.backendid) not like '<IDLE>'; > > 21282 | update cmr_lastno set lastisn=lastisn+1 where tablename='DANNY'; > > 25936 | <IDLE> in transaction <== This is the locker > > > > And the question: > > - What exactly is the locker doing? Idle in transaction means they aren't doing anything, but have not yet committed or rolled back the transaction, thus any locks they took out early in the transaction are still held. Keep in mind that they aren't doing anything at that instant. It's possible that the connection is still working, but only issuing queries every so often, because it's processing the resultant data. (It's also possible that they really aren't doing anything and should be swatted for leaving the transaction open). > - Can I retrieve the Client PID somewhere (then by process name I will dive into the code). If you're on a POSIX system, you can use netstat to find out the details of the socket the backend is connected to. If it's a local connection, another look at netstat will give you the pid of the client. If it's a remote connection, then you'll have the client IP and port #. Depending on your setup, the client IP alone might tell you what you need to know. If that's not enough, you should be able to use the client port # on the client's system to track down what process is on the client end (again, using netstat) If you're not on a POSIX system, you can probably still do what I described, I just don't know the details of how it's done. Windows has a netstat equivalent, I think. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Bill Moran <wmoran@potentialtech.com> writes:
> In response to "Abraham, Danny" <danny_abraham@bmc.com>:
>> - Can I retrieve the Client PID somewhere (then by process name I will dive into the code).
> If you're on a POSIX system, you can use netstat to find out the details of
> the socket the backend is connected to.
Looking at all the columns of pg_stat_activity, rather than just some of
them, should provide the client address and port number. I guess you'd
still need netstat or lsof or some other tool to find out what process
owns that port though.
It's quite peculiar that the given query hangs though. Right offhand,
the only thing I can think of that might block it is an exclusive lock
on pg_proc, or one of the other system catalogs that the parser has to
consult to process the query. It's *extremely* bad form for a client
to take such a lock at all, much less sit on it while doing nothing.
regards, tom lane
On Thu, March 18, 2010 12:29, Bill Moran wrote: > In response to "Abraham, Danny" <danny_abraham@bmc.com>: > >> Hi, >> >> >> >> A process hangs forever. >> >> When using this query... >> >> >> >> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, >> >> pg_stat_get_backend_activity(s.backendid) AS current_query >> >> FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s where >> pg_stat_get_backend_activity(s.backendid) not like '<insuff%' >> >> and pg_stat_get_backend_activity(s.backendid) not like '<IDLE>'; >> >> >> >> >> >> I get... >> >> >> >> >> >> procpid | current_query >> >> ---------+-------------------------------------------------------------- >> >> ---------+------- >> >> 26702 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, >> >> : pg_stat_get_backend_activity(s.backendid) AS >> current_query >> >> : FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS >> s >> >> : where pg_stat_get_backend_activity(s.backendid) not like >> '<insuff%' >> >> : and pg_stat_get_backend_activity(s.backendid) not like >> '<IDLE>'; >> >> 21282 | update cmr_lastno set lastisn=lastisn+1 where >> tablename='DANNY'; >> >> 25936 | <IDLE> in transaction <== This is the locker >> >> >> >> And the question: >> >> - What exactly is the locker doing? > > Idle in transaction means they aren't doing anything, but have not yet > committed or rolled back the transaction, thus any locks they took out > early in the transaction are still held. > > Keep in mind that they aren't doing anything at that instant. It's > possible > that the connection is still working, but only issuing queries every so > often, because it's processing the resultant data. (It's also possible > that they really aren't doing anything and should be swatted for leaving > the transaction open). > >> - Can I retrieve the Client PID somewhere (then by process name I will >> dive into the code). > > If you're on a POSIX system, you can use netstat to find out the details > of > the socket the backend is connected to. If it's a local connection, > another > look at netstat will give you the pid of the client. If it's a remote > connection, then you'll have the client IP and port #. Depending on your > setup, the client IP alone might tell you what you need to know. If > that's > not enough, you should be able to use the client port # on the client's > system to track down what process is on the client end (again, using > netstat) > > If you're not on a POSIX system, you can probably still do what I > described, > I just don't know the details of how it's done. Windows has a netstat > equivalent, I think. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- Yes, Windows does have Netstat (C:\Windows\System32 under Windows XP). Use the -b option to determine which program is using the port. Tim -- Timothy J. Bruce Registered Linux User #325725