lock problem - Mailing list pgsql-admin
| From | Rural Hunter |
|---|---|
| Subject | lock problem |
| Date | |
| Msg-id | 4EF190E6.3030509@gmail.com Whole thread Raw |
| List | pgsql-admin |
I'm seeing connection hang issue these days. many concurrent connections are hanging on db. They basically do the same thing:update different rows in same table. The sql itself should run very fast as it's updating just one row based on anunique key. I though it might be lock problem. The I list the locks ordered by query start time(see list below). I'mconfused why the oldest connection are still waiting for the lock? anything else can hold that lock?<br /> <font color="#ff0000"> | 1580056836 | ShareLock | f | update article set tm_update=$ |2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020</font><br /><br /> select <br /> pg_class.relname,pg_locks.transactionid,pg_locks.mode, pg_locks.granted, <br /> substr(pg_stat_activity.current_query,1,30),pg_stat_activity.query_start, <br /> age(now(),pg_stat_activity.query_start)as "age", pg_stat_activity.procpid <br /> from pg_stat_activity,pg_locks left <br/> outer join pg_class on (pg_locks.relation = pg_class.oid) <br /> where pg_locks.pid=pg_stat_activity.procpidorder by query_start limit 50;<br /> relname | transactionid | mode | granted | substr | query_start | age | procpid<br /> ----------------------------+---------------+------------------+---------+--------------------------------+-------------------------------+-----------------+---------<br /> article_title_hash_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> | 1580056836 | ShareLock | f | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> | 1579897513 | ExclusiveLock | t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> article_fid_author_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article_stage_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> article_fid_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article_cid_time_style_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> | | ExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article_tm_spider_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> article_tm_update_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article_guid_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> article_url_hash | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article_rfid_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> article_url_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:28:56.643105+08 | 00:24:51.599424 | 21020<br /> article_pkey | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 | 21020<br/> article | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> article_cid_time_style_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_fid_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> article_rfid_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_pkey | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> | 1579921995 | ExclusiveLock | t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_url_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> article_title_hash_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_guid_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> article_tm_update_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> | 1580056836 | ShareLock | f | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> article_fid_author_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_tm_spider_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> article_stage_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_url_hash | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:01.947787+08 | 00:23:46.294742 | 706<br /> | | ExclusiveLock | t | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 | 706<br/> article_title_hash_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> | 1580056836 | ShareLock | f | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_stage_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> article_fid_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_pkey | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> article_url_hash | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_cid_time_style_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> article | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_fid_author_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> article_tm_update_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_rfid_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> article_url_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> | | ExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> article_guid_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_tm_spider_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:11.735228+08 | 00:23:36.507301 | 22892<br /> | 1579925267 | ExclusiveLock | t | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 | 22892<br/> article_title_hash_idx | | RowExclusiveLock | t | update article set tm_update=$ | 2011-12-2113:30:26.843451+08 | 00:23:21.399078 | 32700<br /> article_fid_author_idx | | RowExclusiveLock| t | update article set tm_update=$ | 2011-12-21 13:30:26.843451+08 | 00:23:21.399078 | 32700<br/> (50 rows)
pgsql-admin by date: