转载:http://francs3.blog.163.com/blog/static/4057672720109854858308/
Postgresql 8.3.3
今天应用反映数据库很慢,有些SQL执行一天都没执行完,
检查数据库,发现大量会话在更新同一张表 tbl_tmp, 产生大量行锁申请等侍。
--等于等侍状态的SQL (被堵住的SQL)
anpstat=# select datname,procpid,query_start,current_query,waiting,client_addr from pg_stat_activity where waiting='t';
datname | procpid | query_start | current_query | waiting | client_addr
----------+---------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+---------+-------------
sanpstat | 14044 | 2010-10-07 16:46:40.386904+08 | update tbl_tmp set responsed=true , feedback =$1, gmt_modified=now(), modifier=$2 where seq=$3 | t | 127.0.0.1
sanpstat | 27839 | 2010-10-07 16:49:18.022499+08 | update tbl_tmp set responsed=true , feedback =$1, gmt_modified=now(), modifier=$2 where seq=$3 | t | 127.0.0.1
sanpstat | 3539 | 2010-10-07 20:28:13.212075+08 | update tbl_tmp set feedback =$1 where seq=$2 | t | 127.0.0.1
sanpstat | 3894 | 2010-10-07 20:53:43.466517+08 | update tbl_tmp set responsed=true , feedback =$1, gmt_modified=now(), modifier=$2 where seq=$3 | t | 127.0.0.1
sanpstat | 10130 | 2010-10-08 09:37:51.253871+08 | update tbl_tmp set responsed=true , feedback =$1, gmt_modified=now(), modifier=$2